DAX Measure - CALCULATE DISTINCTCOUNT with ALL not working as intended

[huguest]

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?

[GuilhermeDias]

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?

[v-yulgu-msft]

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

[huguest]

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.

[v-yulgu-msft]

Hi @huguest,

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

Regards,

Yuliana Gu

[huguest]

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!

[AlB]

Hi @huguest

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

[huguest]

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

[AlB]

@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?

[huguest]

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.

[AlB]

@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.