Forum Discussion
Solved
Row aggregation - count blanks across rows
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!
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:
1 Reply
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: