cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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:
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!
5 REPLIES 5
Anonymous
Not applicable

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

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:
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!
Helper I

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!

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors