Slicer & Categories Query

[lherbert501]

Hi,

If I had simple layout like in the image below, and I highlighted all four slicer options then I would like to only see items that have all four categories attached. In the example if I select all slicer options then this will show me item 1, as it technically is correct with it covering 3 categories that are selected.

How can I go about only returning in the table items that had all four categories?

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @lherbert501 ,

According to your description, here's my solution. 

1.Don't make relationship between the two tables.

2.Create a measure:

Measure =
IF (
    ISFILTERED ( Category[Category] ),
    IF (
        COUNTROWS (
            FILTER (
                DISTINCT ( Category[Category] ),
                'Category'[Category]
                    IN SELECTCOLUMNS (
                        FILTER ( ALL ( 'Table' ), 'Table'[Item] = MAX ( 'Table'[Item] ) ),
                        "category", 'Table'[Category]
                    )
            )
        )
            >= COUNTROWS ( DISTINCT ( 'Category'[Category] ) ),
        1,
        0
    ),
    1
)

Put the measure in the visual level filter and select "is 1", get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[lherbert501]

This is great - Thankyou @v-yanjiang-msft  for your time with it.

Seems uneccesary code instead of having an OR or AND switch on a slicer though!

Thanks again