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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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