PowerPivot and DISTINCTCOUNT

[txfrazier]

Hi,
I have a a column in powerpivot where I want to count the number of distinct entries but exclude entries that are blank in the count. The formula in the calculated field is:
UniqueIDProcessID Records:=DISTINCTCOUNT([UniqueIDProcessID])

For example: I know I have three unique records given a set of conditions in other columns, however the count shows 4 because it is including Blank as a distinct record.

Thanks for your help

 

[Luke M]

I don't have PowerPivot on my machine, so just guessing, but can you do something like this?
=DISTINCTCOUNT([YourRange])-COUNTBLANK([YourRange])

 

[txfrazier]

Luke, thanks for the suggestion. I tried it but got an error that a circular dependency was detected. Even without the error, I don't think counting the blank etnries would work since I have quite a few and would have resulted in a negative number.

 

[Luke M]

Hmm, good point. Don't know about the circular error, but to at least correct the math, could change formula to:
=DISTINCTCOUNT([YourRange])-(COUNTBLANK([YourRange])>0)
By changing the last bit into a Boolean check, we end up subtracting either 1 or 0 (True or False), preventing a negative count. Hope someone else with PP experience can shed some more light.

 

[Kyle McGhee]

Try this:
=CALCULATE(DISTINCTCOUNT([UniqueIDProcessID]),FILTER(Table1,NOT(ISBLANK([UniqueIDProcessID]))))
(replace Table1 with the table name in your data model)

 

[txfrazier]

It works!!!!

Thanks Kyle - You Da Man