Filtering table -- cells can have multiple values

I have a dataset table with filters. Some cells have multiple values. Say I have a column called "Device." Row 1 can have "Desktop, mobile" and Row 2 can have "Desktop." I want to have a filter called "Desktop" that shows all rows containing any instance of "Desktop."

When I do the beast mode formula:

case
when `Device` like '%Desktop%' then 'Desktop'
when `Device` like '%Mobile%' then 'Mobile'
end

this doesn't work, since with this method each row is grouped into only one of the new filters. So for example, if I filter by "Mobile" it will display Row 1, but if I filter by "Desktop" it will only display Row 2, since Row 1 is only associated with "Mobile." It should show both rows. Is there a solution to this?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    This requires restructuring your data, so that there is a row for each type. So, if an entry had "desktop, mobile" it would become two rows with this ETL. It would look something like this:

    You can use a formula in each of your filter to retrieve the rows that contain mobile and another one for desktop. Then create a column called DeviceType and set a constant value to it. Finally, append them together. Your filter card can now use that DeviceType column to get those distinct values. Your table card would need to use this dataset as well, but would give you the rows you are looking.

    Hope that makes sense.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    You might consider pivoting the data in Magic ETL to create columns for each of your types.

    You could also use multiple filters in Magic ETL and then append them back together. This would keep your data from getting wider. You would set up a filter tile for each device type you are looking for and then use the append tile after those to stack your data.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks for the response Mark. This filter would need to be a card used for salespeople to filter the table and create customized tables. Say a client wanted to know which packages (rows) support mobile devices. The salesperson would use the checkbox selector card to select "Mobile" and the table would display only relevant rows, which then could be exported as an Excel. Could you elaborate on how Magic ETL could be used to create a more robust filtering interface for users on the dashboard?

  • jrtomici
    jrtomici ⚪️
    edited April 20

    That makes a lot of sense, thank you so much. Only issue is now, if multiple filters are selected, there can be repeat rows in the table. So if the user wants a table that displays only rows that support desktop or mobile, if both those filters are selected, any row that has both desktop and mobile will now appear twice.

    Edit: I can hide these duplicate rows by sorting by device and aggregating by count, correct? Thank you so much for the help again.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    You might be able to get around that duplication by not showing the newly created column in the main table and including an aggregate column in there. This way, it will just show distinct values. You can even hide the aggregate column in the chart properties, but it is still used to reduce the number of rows.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @MarkSnodgrass follow up question: I have two cards now, each checkbox filters that filter by the new columns made in Magic ETL since both these columns can have multiple values. However, when one of these filters is used, all the checkbox options in the other filter card disappear. How can I allow both filter cards to be used in conjunction and keep the card populated with values when the other is in use?