topic Re: Help with DAX measure - Count values if > than in DAX Commands and Tips

Help with DAX measure - Count values if > than

Fusilier2 — Tue, 23 Apr 2024 14:41:55 GMT

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.

Re: Help with DAX measure - Count values if > than

Anonymous — Tue, 23 Apr 2024 14:48:22 GMT

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

Re: Help with DAX measure - Count values if > than

Fusilier2 — Tue, 23 Apr 2024 15:02:31 GMT

Here's some sample data:

The measure should be returning the value 4

Re: Help with DAX measure - Count values if > than

Anonymous — Tue, 23 Apr 2024 15:07:26 GMT

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

Re: Help with DAX measure - Count values if > than

Fusilier2 — Wed, 24 Apr 2024 07:00:41 GMT

Thanks. I've uploaded some sample data above.

Re: Help with DAX measure - Count values if > than

Anonymous — Wed, 24 Apr 2024 08:04:38 GMT

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

Re: Help with DAX measure - Count values if > than

Fusilier2 — Wed, 24 Apr 2024 11:58:02 GMT

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?

Re: Help with DAX measure - Count values if > than

Anonymous — Wed, 24 Apr 2024 12:12:29 GMT

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

Re: Help with DAX measure - Count values if > than

Fusilier2 — Wed, 24 Apr 2024 12:20:47 GMT

Thank you so much, your help is really appreciated.

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

Re: Help with DAX measure - Count values if > than

Anonymous — Wed, 24 Apr 2024 12:22:28 GMT

Much to learn, keep going!