Forum Discussion
Advocate IICOUNTROWS Vs. DISTINCTCOUNT when I have a Key
Hi! This is my table:
These are two metrics that I have developed:
Here the first result:
Obviously, they are the same.
Now the second result:
What is happening here?
Thank you.
Hi Strizzolo ,
I've been investigatting this, and i think that we are in scenario of DAX Auto-Exist.
What is auto-exists?
Auto-exist is an optimization feature in the DAX engine that automatically filters out invalid combinations of values when multiple columns from the same table are used in calculations or filters. It helps improve performance and accuracy by ensuring that only valid data combinations are considered in the evaluation of a query or measure.This is a bit complex to explain here, i'll recommend you to watch this video or follow this link: Understanding DAX Auto-Exist - SQLBI
Look that using star schema to avoid use multiple columns from the sample table to filter we have the same results for both:
6 Replies
- Bibiano_Geraldo
Super User
Hi Strizzolo ,
I initially wrote a detailed explanation of what might be happening. However, before posting, I decided to replicate your scenario on my side. After testing, I cleared everything I wrote because it seems this might actually be a bug.
Why do I think this is a bug?
Take a look at my screenshots:
When I select Type A and B:
- The results are exactly the same as yours
When I select "All" in the slicer:
- I get different values
Why is this unexpected?
Type A and Type B together should represent all types in my table. Logically, selecting both should yield the same results as selecting "All" in the slicer. However, as you can see, that's not the case.
I’m not sure why this is happening, but it doesn’t seem to align with how the slicer should behave.
You can report this issue here
- Bibiano_Geraldo
Hi Strizzolo ,
I've been investigatting this, and i think that we are in scenario of DAX Auto-Exist.
What is auto-exists?
Auto-exist is an optimization feature in the DAX engine that automatically filters out invalid combinations of values when multiple columns from the same table are used in calculations or filters. It helps improve performance and accuracy by ensuring that only valid data combinations are considered in the evaluation of a query or measure.This is a bit complex to explain here, i'll recommend you to watch this video or follow this link: Understanding DAX Auto-Exist - SQLBI
Look that using star schema to avoid use multiple columns from the sample table to filter we have the same results for both:
StrizzoloExactly. But in this case column "Index" has distinct values, so the results should be the same in the second case too.
- Strizzolo
But in the screen they aren't.. I'm missing something but I don't know what 😞
Ajithkumar_P_05Helper I
COUNTROWS counts all rows in a table or table expression, including duplicates. DISTINCTCOUNT counts only unique values in a column, ignoring duplicates.
