Forum Discussion

047's avatar
047
Frequent Visitor
4 years ago
Solved

Slicer with overlapping values

Hi

A newbie here. I've tried searching in the forum without luck. I'm looking to apply filter on a set of values where some of the rows may belong to mulitple categories. For example:

 

 

ItemNameCategory
AppleFruit
PotatoVegetable
Tomato

Both

 

I want to add a filter(slicer) on Category, but it should only show Fruit & Vegetable as the filter catogires. When selecting fruit, both tomato & apple should be returned, and similarly both tomato & potato when filtered on vegetable. Trying to create a new measure as the filter without luck. Can you pls help? Thanks.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi 047 ,

    If your sample cannot make relationship, you can try this method.

    1.Create a table with all categories, donn't make relationship between the two tables.

    2. Create a measure.

    Check =
IF (
    NOT ( ISFILTERED ( 'Table (2)'[Category] ) ),
    1,
    IF (
        ISFILTERED ( 'Table (2)'[Category] )
            && (
                MAX ( 'Table'[Category] ) = SELECTEDVALUE ( 'Table (2)'[Category] )
                    || MAX ( 'Table'[Category] ) = "Both"
            ),
        1
    )
)

    3.Put the new category table in the slicer, and put the measure in the ItemName visual filter, select the value is 1.

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    047 , Create a new table like this join with the above tbale many to many bi-direction and use cat name for filter

     

    Category Cat Name
    Fruit Fruit
    Vegetable Vegetable
    Both Fruit
    Both Vegetable
    • 047's avatar
      047
      Frequent Visitor
      4 years ago

      Hi amitchandak 

      Thanks for your reply. It seems correct intutively, but I'm unable to implement it in power BI. When trying to create a relationship between 'New Table'[Category] to 'Existing Table'[Category], it says that the relationship could not be created becuase one of the columns must contain unique values. In fact, I don't even see the option of 'many-to-many' cardinality in the dropdown list. Does it require creation of an intermediate table with unique values? Thanks.

      To clarify, I'm using Power BI Desktop Optimized for Power BI Report Server - May 2021.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi 047 ,

    Has your problem been solved by amitchandak 's solution, if yes, please consider accept it as the solution to help the other members find it more quickly. If not, please feel free to let me know.

    Best Regards,
    Community Support Team _ kalyj

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi 047 ,

    If your sample cannot make relationship, you can try this method.

    1.Create a table with all categories, donn't make relationship between the two tables.

    2. Create a measure.

    Check =
IF (
    NOT ( ISFILTERED ( 'Table (2)'[Category] ) ),
    1,
    IF (
        ISFILTERED ( 'Table (2)'[Category] )
            && (
                MAX ( 'Table'[Category] ) = SELECTEDVALUE ( 'Table (2)'[Category] )
                    || MAX ( 'Table'[Category] ) = "Both"
            ),
        1
    )
)

    3.Put the new category table in the slicer, and put the measure in the ItemName visual filter, select the value is 1.

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • 047's avatar
      047
      Frequent Visitor
      4 years ago

      Thanks Kalyj.

      Sorry for my late reply. I was a bit preoccupied and couldn't check your solution. As you've already tested, your solution works well. Sorry for not being clear enough with my requirement, but my case is a little bit different.

       

      First, my Category slicer is set to single select, so I modified the measure to exclude the check for isfiltered.

       

      Check2 =
      IF( MAX ( 'Table'[Category] ) = SELECTEDVALUE ( 'Table (2)'[Category] ) || MAX ( 'Table'[Category] ) = "Both",1,0)

       

      But, In my case, there're multiple visuals on the same/different pages of the report. I was unable to set the measure Check or Check2 as a page/report level filter. Any suggestions?

      What I'm doing as a temporary workaround is to duplicate all records where category is 'both'. One of the duplicated rows has Category set as 'Vegetable' & the other row as 'Fruit'. Thanks