topic Re: Measure that shows count of distinct items based on two conditions in DAX Commands and Tips

Measure that shows count of distinct items based on two conditions

whodis118 — Wed, 14 Sep 2022 21:23:32 GMT

I am having a problem calculating my measure correctly. Let me explain...

I need the DISTINCTCOUNT of equipment items, where the status is active and the utilization is negative. I can easily get a distinct count of active equipment items by using ->

Total Active = CALCULATE(DISTINCTCOUNT(Utilization[Equipment]), FILTER(Utilization, Utilization[Status] = "A"))

However, when I try to account for items that are under utilized, I can't get it to work. Here is one iteration of what I have tried ->

Total Active Under Utilized = CALCULATE(DISTINCTCOUNT(Utilization[Equipment]), FILTER(Utilization, Utilization[Status] = "A" && SUM(Utilization[Over Under] )< 0 ))

Which gives me the same result.

Based on the screenshot here, you can see that I want the Total Active Under Utilized to be 2 because the Total Over Under is only Under for two pieces of equipment in this filter.

Does anyone know how I can acheive this?

Re: Measure that shows count of distinct items based on two conditions

Anonymous — Thu, 15 Sep 2022 05:24:30 GMT

Hi whodis118 ,

So you want the result show the two equipments, not three?

But this sytnax is to filter "over under"<0, in your table , it seems to actually have three, so I think you need to change this filter object, choose this column, filter it <0.

If I misunderstand your meanings, tell me in time. Thanks in advance~

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Measure that shows count of distinct items based on two conditions

whodis118 — Thu, 15 Sep 2022 14:02:10 GMT

I may be mis-understanding, but it sounds like the proposed solution is the same as what I have already done?

"choose this column, filter it <0" is what I have done, no?

I need this calculation to reflect the total for the given time frame. Here, the data is filtered to show the last week, and this is the result. Filtering it back two weeks, would add more items and the number would change.