How to display nulls in PowerPivot using DAX

Question

I have heard amazing things about PowerPivot but for me it's not been so helpful. Pivot charts are often using a count function and this is where me and PowerPivot have our differences. I have a very large data set and why I am using PP. However it doesn't recognize nulls, so my counts are not true counts. My thought is to add a calculated column with DAX like this:

=if(ISBLANK([status]),0, [status])

So I want a 0 instead of a blank, but the status value to show when there is one. I'm not the best at excel, so I think my function is wrong and why PP doesn't like it. I can get 0's for nulls and 1's for not nulls, but I need 0's for nulls and the actual cell value to display when not null.

Answer 1

DAX has no NULL, it has only BLANK, where BLANK has very different semantics from a SQL NULL.

These two pages might help understanding the semantics of a DAX BLANK.

That being said, if you want to count something regardless of the whether [Status] is BLANK, just use COUNTROWS(). This function, aptly named, will count the number of rows in the table, rather than count the non-BLANK rows in a specific field.

Answer 2

So this is a work around and not an answer to the Question "how to display nulls in PowerPivot using DAX"

Instead of doing this in PowerPivot, depending on the complexity of your query it may be easier to replace the nulls in the SQL query first using isnull.

Answer 3

Even if this post is older... I found the following solution helpful: instead of using e.g. averagex over a set that might contain 0 values, I use sumx()/countrows, where countrows count the rows in the table to which I like to aggregate. Then 0 values are considered. While this works fine to calculate an average, I am still struggeling to calculate a standard deviation.