topic Re: DistinctCount ONLY the lowest values of a column in case of multiple rows in DAX Commands and Tips

DistinctCount ONLY the lowest values of a column in case of multiple rows

Anonymous — Wed, 06 Nov 2024 09:31:59 GMT

I'm looking for a DAX calculation which does DISTINCTCOUNT with a certain condition. If a Customer has two different Priorities, it should only do a count on the lowest Priority. To demonstrate with dummy data:

Customer Priority

Jan	5
Tom	2
Dirk	6
Jan	1

You see that Customer Jan has two rows, one with Priority 5 and 1. In this case I want to DISTINCTCOUNT the Customer only for the lowest Priority, being 1 in this case. The end result should look as follows.

Thanks in advance!!

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

johnt75 — Wed, 06 Nov 2024 09:58:17 GMT

Just do a distinct count of the customer, that will only count them once no matter how many times they appear in the table.

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

Anonymous — Wed, 06 Nov 2024 10:03:02 GMT

That's the thing, I don't want to just do a Distinct Count of the Customer because then a Customer with two rows will be counted twice (in this case in Priority 5 and 1). While I only want it to be counted once, for the lowest Priority (1).

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

johnt75 — Wed, 06 Nov 2024 10:09:34 GMT

That's not how a distinct count works, that's how a count works. A count would return 2 for a customer with 2 rows, a distinct count will only return 1 for a customer no matter how many times they appear.

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

shafiz_p — Wed, 06 Nov 2024 10:12:31 GMT

Hi Anonymous Try this:

DistinctCountLowestPriority = CALCULATE( COUNT('Table'[Customer]), FILTER( 'Table', 'Table'[Priority] = CALCULATE(MIN('Table'[Priority]), ALLEXCEPT('Table', 'Table'[Customer])) ) )

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

Anonymous — Wed, 06 Nov 2024 10:14:21 GMT

That's true yes. But as I said, I want to return 1 for a Customer which appear twice in the data (in Priority 5 and 1). Distinct Count returns a 1 for both the Priorities, but I only want to return it for the lowest Priority. So not with Priority 5.

Re: DistinctCount ONLY the lowest values of a column in case of multiple rows

Anonymous — Wed, 06 Nov 2024 10:29:22 GMT

Had to change COUNT to DISTINCTCOUNT but worked like a charm, thank you sir!