Get Count of Columns that Contain At Least one Non-Null Value

[jsmith435]

Hello, 

I would like to create a dax formula (or find another way) to create a measure that sums the number columns that have at lease one non-null value in it. I have a number of features for a program where each column is a feature. The rows for each column hold a value (image file, date, true/false). Now I want to summarize these columns by the Total amount of columns that contain at least one non-null value.

Thanks! :))))

[bolfri · Accepted solution]

Number of columns with not null values = 
var _column1 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column1]),NOT('SampleData'[Column1] in {"NULL","null"}))),0,1)
var _column2 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column2]),NOT('SampleData'[Column2] in {"NULL","null"}))),0,1)
var _column3 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column3]),NOT('SampleData'[Column3] in {"NULL","null"}))),0,1)
return _column1 + _column2 + _column3

Due to fact that your request do not have any expected results I've assumed that you want to calculate amount of column that has at least 1 row with different value than NULL or null, so the result will be 2.

Am I correct?

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file and show the expected result.

[v-binbinyu-msft · Accepted solution]

Hi @jsmith435 ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR _a =
    CONCATENATEX ( 'Table', [Column1], "," )
VAR _b =
    CONCATENATEX ( 'Table', [Column2], "," )
VAR _c =
    CONCATENATEX ( 'Table', [Column3], "," )
VAR _val1 =
    IF ( CONTAINSSTRING ( _a, "NULL" ), 1, 0 )
VAR _val2 =
    IF ( CONTAINSSTRING ( _b, "NULL" ), 1, 0 )
VAR _val3 =
    IF ( CONTAINSSTRING ( _c, "NULL" ), 1, 0 )
RETURN
    _val1 + _val2 + _val3

3. add a table visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bolfri · Accepted solution]

Number of columns with not null values = 
var _column1 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column1]),NOT('SampleData'[Column1] in {"NULL","null"}))),0,1)
var _column2 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column2]),NOT('SampleData'[Column2] in {"NULL","null"}))),0,1)
var _column3 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT('SampleData'[Column3]),NOT('SampleData'[Column3] in {"NULL","null"}))),0,1)
return _column1 + _column2 + _column3

Due to fact that your request do not have any expected results I've assumed that you want to calculate amount of column that has at least 1 row with different value than NULL or null, so the result will be 2.

Am I correct?