Count non-blank cells across a range of columns

[heidibb]

Hello,

I am trying to create a column that basically counts the number of columns for each row that are not blank. See image. The value i'm looking for would be 4 for the top few rows since ther are 4 columns that are not null.

Is there a way to do this in query editor?

[sohananahid]

Hi,

I had a similar issue where i need to count 5 different Columns with non blank values that indicate some actions were taken for specific MoC. So, when I select a MOC - i need to see the count of actions taken. I did the following DAX : a calculated Col in the table

Count Action =

VAR Col1nonblank = IF(ISBLANK('Table1'[Action 1]), 0, 1)

VAR Col2nonblank = IF(ISBLANK('Table1'[Action 2]), 0, 1)

VAR Col3nonblank = IF(ISBLANK('Table1'[Action 3]), 0, 1)

VAR Col4nonblank = IF(ISBLANK('Table1'[Action 4]), 0, 1)

VAR Col5nonblank = IF(ISBLANK('Table1'[Action 5]), 0, 1)

RETURN Col1nonblank + Col2nonblank + Col3nonblank + Col4nonblank + Col5nonblank

-A data card visual-> sum of -> Count Action

Just sharing this in case it helps someone else! Cheers! 🙂

[rtl]

Thank you so much!  This worked perfectly for me, much appriciated!

[OwenAuger]

@alicek 

I would suggest something like this in place of the above code, using List.Select to select the empty strings:

=List.Count(List.Select(Record.FieldValues(_), each _="" ) )

[alicek]

Amazing, thank you @OwenAuger !! 

I tried to combine what you sent above with the List.Range, because I need to count the number of blanks in the first five rows. So I tried various combinations:

List.Count(List.Range(Record.FieldValues(_), 1, 5, each _=""))

List.Count(List.Range(Record.FieldValues(_), each _="", 1, 5))

List.Count(List.Select(Record.FieldValues(_), 1, 5, each _=""))

List.Count(List.Select(Record.FieldValues(_), each _="", 1, 5))

None of them worked... do you think it's possible to do? Perhaps I need to nest a List.Select within a List.Range or vice versa?

[alicek]

Whoops, nevermind, got it! I had to nest them. Just in case this ever helps anyone else, here is what the final formula looked like: 

List.Count(List.Range(List.Select(Record.FieldValues(_), each _=""), 1, 5))

Thanks @OwenAuger !

[OwenAuger · Accepted solution]

@heidibb

Yes, if you click Custom Column on the ribbon, in the dialog box enter this in the formula section:

=List.NonNullCount(Record.FieldValues(_))

Regards,

Owen

[Anonymous]

Dear 

Thank you for sharing the custom column DAX for List.NonNullCount(Record.FieldValues(_)) is working but 178,232 row showing blank out of 672,746 Rows. i cross-checked the information available. i can not why its showing blank. can you please help me to solve this problem  

Regards

Munish Bawa

[jtolmen]

What if I don't want to include every column in the count, what if I only want to count columns 2 - 8?

[OwenAuger]

@jtolmen 

If you want to select columns by position, you could wrap List.Range around Record.FieldValues. For example, this would be the Custom Column Formula in the Custom Column dialog box:

=List.NonNullCount(List.Range(Record.FieldValues(_),1,7))

Regards

Owen

[alicek]

@jtolmenand @OwenAuger thanks for your help on this - it also helped me! 

Instead of null, my cells are blank. Is there an equivalent M formula for that? 

I can't make them null instead of blank, because I am also creating a custom column that concatenates their values. If I make any of the cells null, the whole cell becomes null for some reason. 

[OwenAuger]

@alicek 

If you are wanting to count nonempty strings, you could write something like this, using List.Select:

= List.Count( List.Select( Record.FieldValues(_), each _ <> "") )

Does that give you the expected result?