Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Ignoring multiple filters

Hi,

 

I have the following table:

 

and two slicers, one for Room Code and another for Room Code Dup Boys. I am trying to create a measure (new) that would output the sum of count for the items selected with the Room Code Dup Boys slicer while ignoring any other slicers on the page that have values selected. I have tried different combinations using CALCULATE(), ALL(), ALLEXCEPT(), etc., but nothing seems to work. I am using a card visualisation to display the measure, and I haven't been able to ignore the other slicers succesfully. My intention is to use the measure with other calculations, so I would prefer to use the measure to filter the data instead of the card visual filters.

 

Pbix File 

 

 

 

Any help with this will be very appreciated. 

 

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 

    ALLEXCEPT won't work as long as the column is not part of the visual. You need to use ALL + VALUES like

    CALCULATE (

    [Measure],
    ALL ( 'Table' ),

    VALUES ( 'Table'[Column] )

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1,

     

    Thank you very much for your help 😊. I have tried this:

     

    but I'm still getting Blank as an output. 

    Do you know where the error might be?

     

    Thank you!

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous Responding to your PM, try this:

      Measure = 
    VAR __DupBoys = DISTINCT('Sheet1'[Room Code Dup Boys])
    VAR __Table = FILTER(ALL('Sheet1'), [Room Code Dup Boys] IN __DupBoys)
    VAR __Result = SUMX(__Table, [Count])
RETURN
    __Result
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Greg_Deckler ,

        Thank you so much for your help and quick reply 😊. I have tried your suggestion but still not getting the expected outcome.

        Sample File 

         

        Do you have any other ideas? Thank you!