How to filter for Tags (many-one relationship - multiple tags per field)

NathanDorsch
NathanDorsch Member
edited September 2021 in Charting

I have a dataset containing tags in which a row might have multiple tags applied. For example:

Keyword -- Tag(s)

Car -- blue

truck -- green, red

bike -- yellow, red, purple

How can I set up a filter for a dashboard/card which will allow me to filter down to just trucks and bikes when I filter by "red". Or just trucks when I filter by "green". Or cars and bikes when I filter by "blue" and "purple"?

I've tried case statements in Beast Mode (i.e. Case when Tag(s) like '%green%' then 'green' when Tag(s) like '%blue%' then... but it appears the Beast mode will select the first one that's applicable for each... so if I filter by "red" with this, it won't include either the trucks or bikes....

Thanks for any help!

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @User_32265

    You could use a contains filter instead of selecting a value from the list. Alternatively you could have your dataset list each record with the different tag for each record making sure to handle any possible duplicate records in your card processing.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    Thanks Grant. Are there any particular techniques you can advise on for handling duplicate records in my card processing?

    If my example was the complete dataset, I'd want just 3 fields being pulled (car, truck, bike) if no filters were applied. Or if the user selects "yellow" and "purple", just one field (bike) would pop up (not 2: bike-yellow and bike-purple).

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @User_32265

    @GrantSmith is giving you solid advice.

    Typically there are two ways to handle 'mult-valued columns'.

    1) you can increase your row count (one row per value) OR

    2) you can add columns for isGreen isBlue isRed.


    to handle avoid showing duplicate records, you could UNION a version of the row (car, truck bike) with the color "All Colors" this way when you filter to "All Colors" you know you don't have row duplication.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • michael_west_cs
    michael_west_cs Member

    Hey @jaeW_at_Onyx

    I'm solving a similar problem to the one here.

    Reading through this, I somewhat confused on your last comment. Specifically this:

     "… you could UNION a version of the row (car, truck bike) with the color "All Colors" this way when you filter to "All Colors" you know you don't have row duplication. "

    I'm not sure what you mean here. Can you give an example? Would this be done in a dataflow? Or the original query for the datasource? And how does this exactly remove row duplication. I would assume the table would look something like this:

    Keyword | Tag(s)
    ---------------------
    Car | blue
    Truck | green
    Truck | red
    Bike | yellow
    Bike | red
    Bike | purple

Categories