Calculated column for number of blanks in a related column

Question

I have a table in powerBI called "Table1" with X number of columns (Col1, Col2, Col3...Coln). I am trying to calculate the number of blanks each column has in a separate table. Using PowerQuery, I have created a second table, "Table2", that has the column names from Table1 as a column called "Column_Name".

In Table2, I am trying to calculate the number of blanks each column has in Table1 by creating a calculated column. This is my DAX so far:

No_of_blanks = CALCULATE(COUNTROWS('Table1'), FILTER('Table1', ISBLANK('Table2'[Column_Name])))

However, I just get a blank column as a result, and I believe I am close but I am probably missing something. I have also tried the DAX with RELATEDTABLE but I get the same result.

Thanks for your help.

Answer 1

So I started by creating a table to contain the names of the columns in Power Query like following :

1. I created a reference of the original table :

2. Unpivoted the columns :

3. Removed the other columns and kept only the one with the column names :

4.Finally, I removed the duplicates :

Now, jumping to DAX, I created for each count of blank values in a specific column a measure :

CountBlankColA = COUNTBLANK(MyTable[ColA])

CountBlankColA = COUNTBLANK(MyTable[ColA])

CountBlankColA = COUNTBLANK(MyTable[ColA])

and then :

Measure_Column_Blanks = SWITCH(
    TRUE(),
    SELECTEDVALUE(ColumnNames[Attribute]) = "ColA", [CountBlankColA],
    SELECTEDVALUE(ColumnNames[Attribute]) = "ColB", [CountBlankColB],
    SELECTEDVALUE(ColumnNames[Attribute]) = "ColC", [CountBlankColC],
    // Repeat for each column...
    BLANK() // Return blank or a default value if no match is found
)

Output :

Link to the pbix file