Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jmkvalsund
Helper II
Helper II

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))))

 

M_MaxDateID = MAX('Sales'[DateID])


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]="someone@mycompany.com",'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]="someone@mycompany.com",'Sales'[DateID] = 20221031))

Why can't I put the measure in a filter?

Regards,

John Martin

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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] = "someone@mycompany.com",
        'Sales'[DateID] = ReferenceDate
    )

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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] = "someone@mycompany.com",
        '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!

jmkvalsund
Helper II
Helper II

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.

jmkvalsund
Helper II
Helper II

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 ?

johnt75
Super User
Super User

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] = "someone@mycompany.com"
            && 'Sales'[DateID] = [M_StartDate2MonthsInterval]
    )
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors