Help with DAX measure - Count values if > than

[Fusilier2]

Hi,

Struggling with a DAX measure. Hope somebody can help?

I have a table listing employees and the number of days sickness thay have had in a particular month.

I'm trying to create a measure to count the number of instances where sickness days were greater than 27..

So far I have got this measure:

Number of LTS Incidents = COUNT(Sickness[Calendar Days])>27

Which is giving the answer True. instead of a number.

Can anybody advise me where I am going wrong please? I'm sure this is a pretty simple DAX calculation, I just can't work it out!

Thanks in advance.

[Anonymous · Accepted solution]

@Anonymous  Thanks for your contribution on this thread.

Hi @Fusilier2 ,

@Anonymous has provide the solution, please check if it can return your expected result.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Employee No]),'Table'[Calendar Days]>27)

Best Regards

[Fusilier2]

Thank you so much, your help is really appreciated. 

I was part way there! Much to learn in DAX...

[Anonymous]

Much to learn, keep going!

[Fusilier2]

Here's some sample data:

The measure should be returning the value 4

[Anonymous]

calculate(distinctcount(employer No), calendar days > 27)

[Anonymous]

That's because your measure is asking if the count of sickness days is greater than 27, not counting the number of instances that the number of days was over 27. 

If you can show a sample of data with no sensitive information, that'd help

[Fusilier2]

Thanks. I've uploaded some sample data above.

[Anonymous · Accepted solution]

@Anonymous  Thanks for your contribution on this thread.

Hi @Fusilier2 ,

@Anonymous has provide the solution, please check if it can return your expected result.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Employee No]),'Table'[Calendar Days]>27)

Best Regards

[Fusilier2]

That worked. Thank you.

How would I change the DAX expression to sum all the calendar days greater than 27 instead of count the instances?

[Anonymous · Accepted solution]

calculate(sum(calendar days), calendar days > 27)