How to count blank row group by a specific field

[Anonymous]

Hello, 

I have the following table and I would like to calculate the number of blank invoice group by wbs1.  The expected result should be 2.  What's the good dax formula for that.

Date	WBS1	Account	Invoice
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEM-00002-00	1180-00
2019-09-18	GEP-00002-00	1180-00
2019-09-18	GEP-00002-00	1180-00

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

=CALCULATE(DISTINCTCOUNT(Data[WBS1]),Data[Invoice]=BLANK())

Hope this helps.

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Measure = 
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS (
            'Table'[WBS1],
            "Max_Invoice", MAX ( 'Table'[Invoice] )
        ),
        [Max_Invoice]
            = BLANK ()
    )
)

Best regards,

[FrankAT]

Hi,

give this a try:

Count blank row =
SUMX (
    SELECTCOLUMNS (
        SUMMARIZE ( 'Table'; 'Table'[WBS1] );
        "Count"; IF (
            CALCULATE ( COUNTROWS ( 'Table' ); 'Table'[Invoice] = BLANK () ) > 0;
            1;
            0
        )
    );
    [Count]
)

Regards Frank AT