topic DistinctCount shows 1 when it is 0 in DAX Commands and Tips

DistinctCount shows 1 when it is 0

Cadenze — Wed, 27 Sep 2023 12:10:41 GMT

I have added a Card to my PowerBI report that shows how many distinct customers are matched, per Reason code. I have 8 lines for 4 customers. so with no filters the card shows 4, which is correct. If I filter on Reason=Material, the card shows 3, which is also correct. And when Reason=Process, the card shows one, which is also correct. If I exclude Material and Process from the filter, the card should show 0, but shows 1. Why??? And more importantly, how do I fix it?

/*CountOfMatched = DISTINCTCOUNT(Table[Customer])+0*/

CountOfMatched for Reason Material = 3 (correct)
CountOfMatched for Process = 1 (correct)
CountOfMatched for none of them = 0 but the visual shows 1

Re: DistinctCount shows 1 when it is 0

Anonymous — Fri, 29 Sep 2023 02:35:01 GMT

Hi Cadenze ,

Please try to create a measure with below dax formula:

Count of Matched = IF ( ISFILTERED ( 'Table'[Reason] ), DISTINCTCOUNT ( 'Table'[Customer] ), 0 )

Please refer the attached .pbix file

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DistinctCount shows 1 when it is 0

Cadenze — Fri, 29 Sep 2023 06:34:48 GMT

What a cool way to present a solution - thank you! It works 🙂

Re: DistinctCount shows 1 when it is 0

cotswoldclass — Wed, 24 Jan 2024 22:28:35 GMT

Hi, I had the same issue when I was trying to use the data directly from Salesforce. I was wondering if there is a formula for Salesforce Data in this case? And it's filtered by Company name for example from the data source (Salesforce Reports). Thank you!