Forum Discussion
IF Distinct
Hi Team,
I work in the healthcare industry, and it is important that we obfuscate aggregated patient results if they result in <= 10.
So my viz is using a calculated measure of
DISTINCTCOUNT('TableName'[Patient Id])and it works great and instantaneously.
When I try to obfuscate anything 10 and under to a 5 using =
IF(DISTINCTCOUNT('TableName'[Patient Id] <= 10, 5, DISTINCTCOUNT('TableName'[Patient Id]) It doesn't seem to do the distinct count before evaluating. Before I was returning about 20 aggregated records, when adding the code above it turns into several hundred thousand and eventually times out.
Any suggestions?
Well after fighting this for a while, I noticed that I needed to control with a between statement to avoid zero values and then everything worked fine.
Solution:
# of Patients = IF(DISTINCTCOUNT('TableName'[Patient Id]) > 0 && DISTINCTCOUNT('TableName'[Patient Id]) <= 10, 5, DISTINCTCOUNT('TableName'[Patient Id]))Maybe this can help someone else.
3 Replies
Not sure if this will help, but try precalculating the measure as a variable so that it doesn't have to evaluate twice.
Obfuscated Measure = VAR Test = DISTINCTCOUNT(TableName[Patient ID]) RETURN IF( Test <= 10, 5, // Obfuscating Test )Also, this may have just been a typo, but you were missing a few ")" in your code.
Hi powerdev,
Based on my test, calculating Distinctcount nested in an IF function works great. But the result returned by a measure would be affected by context.
It doesn't seem to do the distinct count before evaluating. Before I was returning about 20 aggregated records, when adding the code above it turns into several hundred thousand and eventually times out.
Could you show me the unexpected result you got now? Also, I need more detailed description about how to simulate a similar report as yours.
Regards,
Yuliana Gu
Well after fighting this for a while, I noticed that I needed to control with a between statement to avoid zero values and then everything worked fine.
Solution:
# of Patients = IF(DISTINCTCOUNT('TableName'[Patient Id]) > 0 && DISTINCTCOUNT('TableName'[Patient Id]) <= 10, 5, DISTINCTCOUNT('TableName'[Patient Id]))Maybe this can help someone else.
