DAX measure to count the number of columns that are not blank

[DarylRob]

So I have multiple columns in my table and I want to be able to add up each non blank column and give a final total of number of non blanks. 

I've tried using (If(isblank(Column1),0,1)) + (If(isblank(Column2),0,1)) + (If(isblank(Column3),0,1)) but keep getting more ))) added on the ends and then error out. 

Any ideas?

Cheers, 

[v-kkf-msft · Accepted solution]

Hi @DarylRob ,

If you want to calculate the number of blank cells in all columns of the table, try the following measure.

Total null cells = 
var Co1 = COUNTROWS( FILTER( 'Table', 'Table'[Column1] = BLANK() ) )
var Co2 = COUNTROWS( FILTER( 'Table', 'Table'[Column2] = BLANK() ) )
var Co3 = COUNTROWS( FILTER( 'Table', 'Table'[Column3] = BLANK() ) )
var Co4 = COUNTROWS( FILTER( 'Table', 'Table'[Column4] = BLANK() ) )
return Co1 + Co2 + Co3 + Co4

If you just want to count the columns where the entire column is empty, try the following measure.

Blank Columns = 
var Co1 = IF( HASONEVALUE( 'Table'[Column1] ) && MAX( 'Table'[Column1] ) = BLANK(), 1, 0 )
var Co2 = IF( HASONEVALUE( 'Table'[Column2] ) && MAX( 'Table'[Column2] ) = BLANK(), 1, 0 )
var Co3 = IF( HASONEVALUE( 'Table'[Column3] ) && MAX( 'Table'[Column3] ) = BLANK(), 1, 0 )
var Co4 = IF( HASONEVALUE( 'Table'[Column4] ) && MAX( 'Table'[Column4] ) = BLANK(), 1, 0 )
return Co1 + Co2 + Co3 + Co4

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alexrai]

Assuming you know the number of columns you could use generate (which is an iterator) to flag each column for counting and then run SUMX over the result. 

Test = SUMX(

GENERATE(MyTable,

  VAR Column1Sum= if(isblank(Column1), 0,1)

  VAR Column2Sum= if(isblank(Column2), 0, 1)

     RETURN ROW ("Column1", Column1Sum, "Column2", Column2Sum)),

[Column1] + [Column2])

[amitchandak]

@DarylRob , Are you looking for the column, that has no blank value?

The above is new column telling you how many non blank value per row