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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jmkvalsund
Helper III
Helper III

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 III
Helper III

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 III
Helper III

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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