Forum Discussion

SDittmannFleet's avatar
SDittmannFleet
Advocate IV
3 years ago

Distinct Count not working as expected

I have a small report that is giving me a headache. There are two tables, "Users" and "Login History". The two were merged in Power Query to whittle things down, and they are also connected in the model.

  • Total row count for "User" is 227.
  • When I remove duplicates for "Login History", I get 161.

I also have two other tables which were created using joins as follows: 

  • Users w/o Login History - joining "User" and "Login History" using LeftAnti. The table has 66 rows.
  • Users w/ Login History - joining "User and "Login History" using Inner. The table has 161 rows, which matches the above when removing duplicates in the full table.

So far, so good. Everything matches. However, when I go for either "Count(Distinct)" in the fields pane of a card visual, or if I put a measure there achieving the same thing, the count is off by 20, and I cannot figure out why. There are no blanks in the column I am aggregating.

 

 

 

2 Replies

  • Can you please clarify your question? The two tables I'm mentioning in the second part were created as a check. I would expect DISTINCTCOUNT or Count(distinct) to yield the same number, but it's off.