topic Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended in DAX Commands and Tips

DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

huguest — Fri, 04 Jan 2019 18:55:48 GMT

I have a table that contains 2 types of records (Audits and Observations). I am trying to use a measure to count the total number of Audits records.

DistinctCountAudit_All_Categories = CALCULATE(DISTINCTCOUNT('Audits_and_Obs'[ID]),'Audits_and_Obs'[Type]="Audit",ALL('Audits_and_Obs'[Category]))

I am adding the ALL('Audits_and_Obs'[Category]) to exclude a Visual Level filter that selects a Category that applies to the Observation records only, but it is not working as intended.

As an example, I know that I have 100 audits for a given month. If I select one of the Categories for the Observations, the number of audit drops to 30. I want that count to remain at 100 regardless of the Category selected.

Any idea what I might be doing wrong?

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

AlB — Fri, 04 Jan 2019 20:16:16 GMT

Hi huguest

Can you share the pbix? It'd be much easier to see what is going on.

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

huguest — Fri, 04 Jan 2019 20:17:28 GMT

I wish I could but unfortunately, because of confidentiality issues, I cannot share the file :(

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

AlB — Fri, 04 Jan 2019 20:22:48 GMT

huguest

OK, that's going to be hard then.

Is the visual level filter on 'Audits_and_Obs'[Category] as well? Or perhaps a [Category] from another table?

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

huguest — Fri, 04 Jan 2019 21:03:55 GMT

It is on ‘Audits_and_Obs'[Category]. What I find to be strange is that when I select a Category in the filter, the measure value changes but I have no idea based on what. None of the audit rows have a value in the Categiry column, so I have no idea what records it is counting. If I exclude the “ALL” portion of the measure, it returns 0 if I select a Category.

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

AlB — Fri, 04 Jan 2019 21:29:25 GMT

huguest

It's tough to guess what might be going on like this.Could you maybe share a simple pbix with dummy data, to avoid the confidentiality issues, that reproduces the problem? Not sure if that will be worth your while.

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

v-yulgu-msft — Mon, 07 Jan 2019 09:36:46 GMT

Hi huguest,

How about:

DistinctCountAudit_All_Categories = CALCULATE(DISTINCTCOUNT('Audits_and_Obs'[ID]),'Audits_and_Obs'[Type]="Audit",ALL('Audits_and_Obs'))

Best regards,

Yuliana Gu

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

huguest — Mon, 07 Jan 2019 12:43:09 GMT

Thank you for all the suggestions. Unfortunately, DistinctCountAudit_All_Categories = CALCULATE(DISTINCTCOUNT('Audits_and_Obs'[ID]),'Audits_and_Obs'[Type]="Audit",ALL('Audits_and_Obs')) did not work either (same output as before). Although I still don't understand why it is behaving that way, I found a solution to my problem. If I select the desired Category value and also select (Blank), I get the desired result.

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

v-yulgu-msft — Wed, 09 Jan 2019 09:47:52 GMT

Hi huguest,

Would you please kindly share your solution so that it can benefit other community members having similar problem?

Regards,

Yuliana Gu

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

huguest — Wed, 09 Jan 2019 11:51:59 GMT

As mentioned in my previous post, my solution is more of a workaround. If I select the desired Category value and also select (Blank), I get the desired result. Thanks to everyone who tried to help!

Re: DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

Anonymous — Wed, 12 Jun 2024 17:05:31 GMT

I have the same problem when I use DISTINCTCOUNT, I don't find the correct values. However, when I use SUM, COUNT, I get the expected result. Were you able to find a solution for this?