Row aggregation - count blanks across rows

[bheepatel]

Hi 

I have a table where the first column is a unique Company ID and the remaining columns are metrics (Metric 1, Metric 2, Metric 3 etc.). For each row (i.e. Company ID) I want to determine how many blank metrics I have. Is there a way to count blanks across a row in Power BI? So if for Company1, Metrics 1 and 3 are blank then the count would be 2.

Thanks in advance!

[Iamnvt · Accepted solution]

hi,

you can try this Measure:

Count blank = 
VAR
Metric1blank = 
CALCULATE(COUNT(Table1[Company ID]),
FILTER(VALUES(Table1[Company ID]), [Metric 1] = BLANK()))
VAR
Metric2blank = 
CALCULATE(COUNT(Table1[Company ID]),
FILTER(VALUES(Table1[Company ID]), [Metric 2] = BLANK()))
VAR
Metric3blank = 
CALCULATE(COUNT(Table1[Company ID]),
FILTER(VALUES(Table1[Company ID]), [Metric 3] = BLANK()))
RETURN
Metric1blank + Metric2blank + Metric3blank

here is PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6pynAIAUTgUSAV7EQ