Calculated Measure not working in the Matrix

Hi Anonymous ,

Thanks for sharing the screenshots. You stated you felt that the ALLSELECTED needs to be in there because otherwise the cards are off. However, Let me explain what your measure actually does:

CALCULATE( 
   //we are going to calculate something BUT with a different context. You can give a totally NEW context by using FILTER or manipulate the CURRENT context by using statements like [col1] = "A".  
   
   DISTINCTCOUNT('Quotes Estimates US'[Customer]),
   //You want the distinct count of the column Customer, but with a totally NEW context (not modified from the current context!)
   
   FILTER(ALLSELECTED('Quotes Estimates US'),
   //You are creating a NEW context by looking at the dataset filtered by explicit filters (like slicers) from outside the current evaluation context. So this returns all the rows from the dataset, based on the current selection of page or report filters or slicers. The current visual is not taken into account.
        
         'Quotes Estimates US'[Job Start Year] = YEAR('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Job Start Month] = MONTH('Quotes Estimates US'[Customer Created Date])&&'Quotes Estimates US'[Customer Status] = "A"))
         //Here you are filtering the NEW context for rows where the above evaluates to TRUE. 

So the context you create by using the FILTER(ALLSELECTED()) statement is the same wether you evaluate this in a Matrix or a Card. However, in the Card visual, the current data context is the same as the context you create with FILTER(ALLSELECTED()). I can think of two reasons why this would result in a different outcome:

1. You have a Visual Filter applied on the Card visual (on the Filters pane), or
2. You have edited the interactions between the slicers and the Card visual.

If neither is the case, then removing ALLSELECTED() should return the same value. Now moving on to your Matrix visual. Normally, the Measure is evaluated per every row/column combination. That row/column essentially filters the dataset (current context), so in your case it would filter the data context first with the slicers, but then would filter it down further based on the SalesPersons column. However, because you recreate your entire context with FILTER(ALLSELECTED()), it ignores the SalesPerson column filter that it gets from the Matrix visual. 

So the remaining question is: what value do you expect to be in the Card when you apply the slicers in your report? You want the card to be responsive to the Year and Month slicers, but always show 175 New Customer Quotes regardless of the what a user selects from the other slicers? Because then you should just turn off the interactions of the slicers you don't want to have an impact on the cards. 

Let me know if this solves your issue. I decided to respond in the thread because the explanation of how the context works in you measure when it is evaluated might be of interest of others. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂