Counting Blank rows for multiple columns

DAX DAX Calculations
7

@michellepace,

I took a much crazier approach then @MudassirAli, attempting for some reason to avoid creating the physical unpivoted table, doing it all through virtual table functions. Same end result, but what took him 9 lines over two measures, took me 80 lines.

Here’s the first measure. Second one is identical, but just <> BLANK() in the relevant filter condition.

Count Blank2 = 

VAR vTable = 'Table'
VAR Unpivoted =
UNION(
        SELECTCOLUMNS(
            vTable,
            "@ColumnName", "MyColumn1", "@Column1", [MyColumn1]
        ),
        SELECTCOLUMNS(
            vTable,
            "@ColumnName", "MyColumn2", "@Column1", [MyColumn2]
        ),
        SELECTCOLUMNS(
            vTable,
            "@ColumnName", "MyColumn3", "@Column1", [MyColumn3]
        )
    ) 
VAR CountVals = CALCULATE(
        COUNTROWS(
            FILTER(
                Unpivoted,
                [@Column1] = BLANK() &&
                [@ColumnName] = MAX( 'Col Names'[ColumnName] )
            )
        )
    ) 
VAR vFinalTable = ADDCOLUMNS(
        SUMMARIZE( Unpivoted, [@ColumnName] ),
        "@CountVals", CountVals
    ) 
VAR Prelim = CALCULATE(
        MAXX( vFinalTable, [@CountVals] ),
        FILTER(
            vFinalTable,
            [@ColumnName] = SELECTEDVALUE( 'Col Names'[ColumnName] )
        )
    ) 

RETURN
Prelim

image

I hope this is helpful. Full solution file attached below.

2 Likes