Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamically Filter 2 Columns ( both containing lists) and categorize into a slicer

Hi Everyone, 

I've spent quite some time on this issue and searched the forums/internet and have been unable to find a solution.

I'm trying to create a slicer that looks at 2 columns (containing lists) and categorizes them into groups based on whether or not they contain contain certain values. Does BI have this capability? 

 

The 2 lists below represent the current data. 

 

Ideally the slicer would look something like this (please excuse the crude ms paint drawing) 

 

Thanks in advance. 

 

R. San 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , if they are not overlapping then you can have new column like

     

    New column = Switch( True() ,

    (search("a", [ListA],,0) > 0 && search("y", [ListB],,0) > 0 )|| (search("a", [ListB],,0) > 0 && search("y", [ListA],,0) > 0 ),"Group A" ,
    (search("c", [ListA],,0) > 0 && search("y", [ListB],,0) > 0 )|| (search("y", [ListB],,0) > 0 && search("y", [ListA],,0) > 0 ),"Group A" ,
    // add others
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I've added the new column to the the slicer it only shows 2 visible groups, unfortunately this does not seem to be a solution. 

      Is it possible to concatenate the two columns and search it for "a" and "y"? If so, how would that code look (it seems fairly complex)? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is a more accurate depiction of the fields. When i ran the code I was only seeing 2 groups in the slicer versus the desired 5. Any troubleshooting assistance would be greatly appreciated. Thanks in advance