Forum Discussion
Matrix counting blank values in table
Hi!
I'd like to count all blank values in all columns in a data table and show them in a matrix, rows being the column names and one column showing the number of blanks in each column.
Is there a way of writing a measure that handles counting blanks without specifying the column in the DAX-formula (doing this requires creating a measure for every column).
This is an example:
Data table:
| Name 1 | Name 2 | Name 3 | Name 4 | Name 5 |
| 1 | 2 | 1 | ||
| 1 | 1 | 2 | 2 | |
| 2 | 3 |
Displayed like this:
| Name of column | Number of blanks in table (measure) |
| Name 1 | 1 |
| Name 2 | 0 |
| Name 3 | 3 |
| Name 4 | 0 |
| Name 5 | 2 |
Can this be done?
4 Replies
Anonymous best approach would be to unpivot your table and then one measure can take care of this. To unpivot, go to Transform Data
add index column
select index column
right click, select unpivot other columns
you will have two new columns, Attribute and Value
and you can use COUNTBLANK() DAX to count blanks and on table visual add Attribute column and Measure and you will get the result.
Would appreciate Kudos 🙂 if my solution helped.
