Forum Discussion
Using measure in a DAX filter does not work
Im trying to use a measure in a DAX filter to count the numbers of customers for each Key Account Manager (KAM) at a given date, but it does not seem to work.
My measure is calculated like this, and gives me the dateID for the end of the completed month which is two completed months ago (in january, last date of october previous year etc). M_MaxDateID is the latest dateID in Sales.
M_StartDate2MonthsInterval =
VAR SlicerDate = MAX('Sales'[DateID] )
RETURN CALCULATE(max('Sales'[DateID])
, Filter(ALL('Sales'[DateID]), IF(VALUE(MID([M_MaxDateID],5,2))<2,'Sales'[DateID] <= (SlicerDate-200),'Sales'[DateID] <= (SlicerDate-9000))))
When I put these measure on a card, I see the values I want, i.e. today it's 20221031 for M_StartDate2MonthsInterval and 20230130 for M_MaxDateID.
But then I try to put M_StartDate2MonthsInterval in a Filter in another Measure it fails.
This measure is blank:
M_NumberOfCustomersTwoMonthsAgo = CALCULATE(DISTINCTCOUNT('Sales'[CustomerID]),'Sales'[KAM_email]="[email protected]",'Sales'[DateID] = [M_StartDate2MonthsInterval]))
If I instead put in the value I know M_StartDate2MonthsInterval holds (20221031), I get my number of customers of 20221031:
M_NumberOfCustomersTwoMonthsAgo = CALCULATE(DISTINCTCOUNT('Sales'[CustomerID]),'Sales'[KAM_email]="[email protected]",'Sales'[DateID] = 20221031))
Why can't I put the measure in a filter?
Regards,
John Martin
Does it work if you store the result of the measure in a variable ?
M_NumberOfCustomersTwoMonthsAgo = VAR ReferenceDate = [M_StartDate2MonthsInterval] RETURN CALCULATE ( DISTINCTCOUNT ( 'Sales'[CustomerID] ), 'Sales'[KAM_email] = "[email protected]", 'Sales'[DateID] = ReferenceDate )
6 Replies
Does it work if you store the result of the measure in a variable ?
M_NumberOfCustomersTwoMonthsAgo = VAR ReferenceDate = [M_StartDate2MonthsInterval] RETURN CALCULATE ( DISTINCTCOUNT ( 'Sales'[CustomerID] ), 'Sales'[KAM_email] = "[email protected]", 'Sales'[DateID] = ReferenceDate )Thanks a lot, that was it!
I do know too little about the basic mechanism in Power BI, think I'll have to attend a class. Thanks again!
You can't use measures as filters because they are calculated at the aggregate level not the individual row level. To calculate at the row level you need to use an iterator to introduce a row context, so you need to use the FILTER function.
Try
M_NumberOfCustomersTwoMonthsAgo = CALCULATE ( DISTINCTCOUNT ( 'Sales'[CustomerID] ), FILTER ( 'Sales', 'Sales'[KAM_email] = "[email protected]" && 'Sales'[DateID] = [M_StartDate2MonthsInterval] ) )I tried your solution, but it didn't change anytning. And as far as I can see, the only difference is that the FILTER statement is written explicitly instead of being implisitt in the CALCULATE statement.
M_NumberOfCustomersTwoMonthsAgo is still (Blank) when using M_STartDate2MonthsInterval as value for criteria.Are you certain that you have sales on the exact date in question ?
Yes, because if I assign M_STartDate2MonthsInterval to a card it shows 20221130, and if I then replace <'Sales'[DateID] = [M_StartDate2MonthsInterval]> with <'Sales'[DateID] = 20221130> I get 397 customers. So there are data, but something about the use of the measure in the filter screws things up.
