Distinct Count with Case statement not working

jessymarteen

This is a bit of an odd issue, the below calculation ends up adding 1 when rolling up data:

Distinct count of ID (if Flag=1)=COUNT(DISTINCT CASE WHEN Flag=1 THEN ID ELSE 0 END)

ID	Flag	Distinct count of ID (if Flag=1)
GRAND TOTAL	1	2
456123	1	1
123456	0	1
654321	0	1

Any ideas why this might be happening? This is a mega table with no sort applied.

ColemenWilson

Got it. So I believe it is the ELSE 0 part of your case statement that is causing there to be a 1 where you expect a 0. Because it will count either the ID's if flag =1 or it will count the 0. Either way you will get a 1. I think you're on the right track removing duplicates in ETL - especially if the only function of your beastmode is to "remove" duplicates in the card.

ColemenWilson

Can you help us understand why the 3rd column is needed? It seems repetitive having the flag column and the Distinct count of ID (if Flag=1) column. Every row in the table card will have a single ID and the flag column is already populated so all you'd really need to do is use the flag column. The distinct count of ID column will always be 1 because there is only ever 1 ID in the ID column in a table card. Maybe I am missing something?

jessymarteen

Hi! In the data table, there is a possibility of duplicate rows so that's why I've set up the distinct count. This is a helpful call out though, I'm guessing it would be best to remove the duplicates in the ETL vs solve for this in a beast mode?

jessymarteen

Sorry, I may have misunderstood your questions, Iadded the Flag column here just for context so you can see that the value is 0 but the calculation is resulting in 1. I would expect the calculation to result in 0 as well.

ColemenWilson

Got it. So I believe it is the ELSE 0 part of your case statement that is causing there to be a 1 where you expect a 0. Because it will count either the ID's if flag =1 or it will count the 0. Either way you will get a 1. I think you're on the right track removing duplicates in ETL - especially if the only function of your beastmode is to "remove" duplicates in the card.

jessymarteen

Oh my gosh, of course! You are completely right, I removed the ELSE 0 and it's no longer counting the extra. That being said, I'll go back into the ETL and adjust for duplicates there so the beast modes removing duplicates aren't necessary, thanks so much for your insight!