Count number of cells with non-numeric data per row

[nbrandborg]

Hi all,

I'm struggeling to do something which I thought would be fairly easy to do (at least it is in Excel), but I cannot find my way around it in PowerBI.
I need two additional columns in my dataset (marked with bold) based on the imported data. I need one column to count the number of blank cells and one with filled cells per row. The first column (in italic) should not be counted. The data is all formated as text.

How is this possible?

My dataset looks similar to this:

Material	Column A	Column B	Column C	Column D	Column E	Filled	Blank
1231	X	X		X	X	4	1
1242	X		X	X		3	2
1255	X	X	X	X	X	5	0
1266		X				1	4

[themistoklis]

@nbrandborg 

Try something like this:

Blank= INT(Table[Column A]= BLANK()) + INT(Table[Column B]= BLANK()) + INT(Table[Column C]= BLANK()) ...

Non Blank = INT(Table[Column A]<> BLANK()) + INT(Table[Column B]<> BLANK()) + INT(Table[Column C]<> BLANK()) ...

[nbrandborg]

@themistoklis 

Thanks for your suggestion! Do you know if it can be done on a range like 'ColumnA: ColumnE'? Some of the datasets has more than 50 columns? 

[themistoklis · Accepted solution]

@nbrandborg 

I cannot thing of any other way using DAX.

If you want to do it directly on Power Query you can check the following thread:

https://community.powerbi.com/t5/Desktop/Count-non-blank-cells-across-a-range-of-columns/m-p/494271

[nbrandborg]

@themistoklis Solving it via the Power Query was a great idea. I managed to get the additional count columns needed by this.
Thanks for your support!

[themistoklis]

Also you can try something like this:

Blanks =
VAR ColumnA = INT(Table[Column A]= BLANK()) -- OR IF (ISBLANK(Table[Column A]), 1, 0)
VAR ColumnB = INT(Table[Column B]= BLANK())
VAR ColumnC = INT(Table[Column C]= BLANK())
RETURN ColumnA + ColumnB + ColumnC