Forum Discussion

lherbert501's avatar
lherbert501
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

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

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

     

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      lherbert501
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      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