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:     Item...
  • 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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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.