Slicer with overlapping values

[047]

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:

ItemName	Category
Apple	Fruit
Potato	Vegetable
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 · Accepted solution]

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]

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

[Whitewater100]

Hi:

If you set up your model a bit different it it easier.

Please see attached. I hope this helps!https://drive.google.com/file/d/1IHA0JL2M-_il6S6N_ggGP8Jq_MR0812K/view?usp=sharing 

[v-yanjiang-msft]

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

[amitchandak]

@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]

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.