IF Distinct

[powerdev]

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?

[powerdev · Accepted solution]

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.

[v-yulgu-msft]

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

[Anonymous]

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.