Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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!

5 Replies

  • WaqarArshad's avatar
    WaqarArshad
    Helper I
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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!