Calculated Measure not working in the Matrix

[reh169]

So I created a calculated measure to count the number of new customers and new customer quotes each month. See logic below. I also have a matrix where I have all the sales people listed and I want to be able to add these measures to the matrix and see the break down there. When I add them it gives me the total number for month for every person. But if I filter the page by sales person then the tiles that show the measures above adjust so I know that the connection is there. How do I get it to reflect accurately in the matrix as well?

• CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'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"))

[v-kelly-msft]

Hi @reh169 ，

If you wanna calculate in “customer status =A”, the related measure is as below:

Measure = COUNTROWS(FILTER(SUMMARIZE(FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Customer Status]="A"),'Quotes Estimates US'[Customer],"MinDate",FORMAT(MIN('Quotes Estimates US'[Customer Created Date]),"MMM YY")), [MinDate] in FILTERS(Calender[Month Year])))

If you wanna calculate in all statuses, pls see below:

Measure 2 = 
var c = MAX('Quotes Estimates US'[Job Start Year])
var prem = CALCULATE(MAX('Quotes Estimates US'[Job Start Year]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Job Start Year]<c))
var predis = CALCULATETABLE(DISTINCT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Job Start Year] = prem),VALUES('Quotes Estimates US'[Customer Status]))
 
return
CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US',NOT('Quotes Estimates US'[Customer] in predis )))

For the related .pbix file ,pls click here.

Hope this would help.

Best Regards,

Kelly

[JarroVGIT]

This is a calculated column and not a measure. Therefor, the ALLSELECTED is evaluated for each row in your table. If this is a measure, it would result in a syntax error because there is no context for (for example) MONTH('Quotes Estimates US'[Customer Created Date]). In a measure, that part would never evaluate on its own, but this might work in a calculated column. Are you sure this is a measure? ❓

EDIT: This is a confirmed measure but used in different contexts.

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! 🙂

[reh169]

I created a new measure to create it and am using it in cards as shown below. But I need to add it to my matrix and when I do instead of the numbers adjusting by the salesperson they stay the same for all of the sales people. But when I filter the page by sales person the cards adjust. So what do I need to change? Taking out the all selected and just having all makes the numbers wrong.

[mwegener]

Hi @reh169 ,

ALLSELECTED removes the Row and Column filter.

https://docs.microsoft.com/en-us/dax/allselected-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[reh169]

Taking out the All Selected makes the cards wrong so I feel like it is needed.

[JarroVGIT]

Hi @reh169 ,

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! 🙂

[reh169]

Hi so I checked and there is nothing be filtered on either the cards or the page to cause the difference between the two when in card form vs being in the matrix. So the point of the cards is to highlight at a high level the number of new customers and quotes that  they have for a given time frame determines by the date chiclets. A new customer has the status of A, and the customer creation date occurs during the same month as the what is being filtered for on the page then that customer counts as a new customer and I want to count the  number of quotes assoicated wtih them. It took me a lot of reserach to get a function that worked. How do you suggest I modify the function?

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'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"))

[JarroVGIT]

Like I suggested in my previous post, remove the interaction between the slicers and the Card visual except for the Year and Month slicers. Check this tutorial to see how to do that: https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

Then the formula is:

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),'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")

That should work and fit your requirements. Please re-read my explanation on context as I don't think you fully grasped it yet. If you want a Card measure to be ignorant of your selection in Slicers, then give it a NEW context. 

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! 🙂

[reh169]

So the formula is giving me an error, where I have the customer created date it is wanting a measure not a field and the error is that the expression referes to multiple clolumns and multiple columns cannot converted to a scalar value.

[JarroVGIT · Accepted solution]

Whoops sorry:

CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US','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"))

[v-kelly-msft]

Hi @JarroVGIT ,

Sorry I just found an error in the measure, I have modified it, pls see below:

1.First you need to create a new  calendar table, using calendarauto (),then create a calculated column, expression is as below:

Ym = FORMAT('Calender'[Date],"mmm yyyy")

2.Then  create a measure as below:

Measure = COUNTROWS(FILTER(SUMMARIZE(FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Customer Status]="A"),'Quotes Estimates US'[Customer],"MinDate",FORMAT(MIN('Quotes Estimates US'[Customer Created Date]),"MMM YYYY")), [MinDate] in FILTERS(Calender[Ym])))

Finally you will see :

As for measure 2,it also needs to corrected, steps are as below:

1.create a calculated column:

Ym = 'Quotes Estimates US'[Job Start Year]*100+'Quotes Estimates US'[Job Start Month]

2.Then create a measure:

Measure 2 = 
var c = MAX('Quotes Estimates US'[Ym])
var prem = CALCULATE(MAX('Quotes Estimates US'[Ym]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym]<c))
var predis = CALCULATETABLE(DISTINCT('Quotes Estimates US'[Customer]),FILTER(ALLSELECTED('Quotes Estimates US'),'Quotes Estimates US'[Ym] = prem),VALUES('Quotes Estimates US'[Customer Status]))
 
return
CALCULATE(DISTINCTCOUNT('Quotes Estimates US'[Customer]),FILTER('Quotes Estimates US',NOT('Quotes Estimates US'[Customer] in predis )))

Finally you will see :

For the related .pbix file,pls click here.

Best Regards,

Kelly

[JarroVGIT]

Hi Reh169,
Is it possible to share your pbix file (please remove confidential data)? If you don't want to share publicly you can share via PM with me and I will have a look tonight.