Custom measure that assigns a null as 0 else 1

[Sid10]

I want to create a measure that counts a column if it has a non-null value and not count it when null.

To do that I am using the function of SUMX and IF(ISBLANK(Column),0,1). However this still treats the null values as 1. Is that expected behavior? What is an alternative to this function to accomplish what I want? Thank you for your help. 

[Ashish_Mathur]

Hi,

This measure should work

Measure = calculate(countrows(Data),Data[Column]<>blank())

If it does not work, then share some data to work with and show the expected result.

[Idrissshatila]

Hello @Sid10 ,

the measure you mentioned should work, can you show us how you're using it?

[Sid10 · Accepted solution]

Thank you for that note. You were actually right - the function used was indeed correct - it was an oversight on my part. I will mark your response as a solution. 

This is the formula I use for Table (tbl1) which has various columns (Col1, Col2...):

Sum_of_Columns = SUMX(tbl1,IF(ISBLANK(tbl1[Col1]),0,1)+IF(ISBLANK(tbl1[Col2]),0,1)+...

[arava]

Check in Power Query to ensure that the values are in fact "null" and not an empty string or invisible unicode character. If that does not fix your issue, I would employ the LEN( ) function (IE a length of 0 is blank).  

[Sid10]

Hi - I do see that the values are "null" in query editor. And the data type is number and not a text string. So, not sure if LEN () will work or will it? Any other alternative?