cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Row aggregation - count blanks across rows

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

Member

## Re: Row aggregation - count blanks across rows

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 228 members 2,280 guests
Recent signins: