Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lherbert501
Helper V
Helper V

Slicer & Categories Query

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

 

lherbert501_0-1686665966704.png

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @lherbert501 ,

According to your description, here's my solution. 

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

vyanjiangmsft_0-1686814604862.png

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:

vyanjiangmsft_1-1686814717947.png

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.

 

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @lherbert501 ,

According to your description, here's my solution. 

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

vyanjiangmsft_0-1686814604862.png

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:

vyanjiangmsft_1-1686814717947.png

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.

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors