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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Filters on DAX measure problem

Dear community, hopefully someone knows how to fix this seemingly simple issue that I can't figure out how to solve myself. 

I am creating measures that count the amount of employees that fit a certain set of criteria. 

one of them is 

NoPromotionGradeEntryDate =
CALCULATE(DISTINCTCOUNT('main data'[Person Number]);
'main data'[Promoted] = 0;
 'main data'[Grade Entry Date] < 04-01-2011) 
this returns blank. that is strange because if I put [person number] in a visual and apply the [promoted] and [grade entry date] as filters to the visual, it works fine. Also if i change to 'main data'[Grade Entry Date] > 04-01-2011 it also works. 
Does anyone know why "<" doesnt work, even though I definitely have a lot of rows that match the criteria, and ">" does work? 
Any input is welcome, thanks in advance!
Kind regards
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found a workaround and now it works.  
the old formula is 

NoPromotionGradeEntryDate =
CALCULATE(DISTINCTCOUNT('main data'[Person Number]);
'main data'[Promoted] = 0;
 'main data'[Grade Entry Date] < 04-01-2011) 
 
I changed this to the new formula using DATE as a variable:
NoPromotionGradeEntryDate =
VAR _date = DATE(2011; 04; 01)

VAR _result = CALCULATE(DISTINCTCOUNT('main data'[Person Number]);
'main data'[Promoted] = 0;
 'main data'[Grade Entry Date] < _date)
RETURN _result
 
Hope this helps others with the same problem!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I found a workaround and now it works.  
the old formula is 

NoPromotionGradeEntryDate =
CALCULATE(DISTINCTCOUNT('main data'[Person Number]);
'main data'[Promoted] = 0;
 'main data'[Grade Entry Date] < 04-01-2011) 
 
I changed this to the new formula using DATE as a variable:
NoPromotionGradeEntryDate =
VAR _date = DATE(2011; 04; 01)

VAR _result = CALCULATE(DISTINCTCOUNT('main data'[Person Number]);
'main data'[Promoted] = 0;
 'main data'[Grade Entry Date] < _date)
RETURN _result
 
Hope this helps others with the same problem!
WaqarArshad
Helper I
Helper I

Hi, its asking for sign in 

Anonymous
Not applicable

Hi, Im sorry, i dont find a way to send or attach the PBI file to this post. but thank you for trying to help!

WaqarArshad
Helper I
Helper I

Can you check what format your date is in. Is it dd-mm-yyyy or mm-dd-yyyy. 

Sharing a sample file would also be helpful in diagnosing the issue

Anonymous
Not applicable

sample PBI I created a sample file I hope you can open it

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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