Count the accounts THAT have a blank value in Column B, C, D and F - DAX

[Aydeedglz]

Hi,

I am trying to calculate the number of accounts in my table that have a blank value in different columns, example:

Account	ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF
1DC	A	UP		BLACK	X	364
32A	B	DOWN	2		Y	789
85B	C		3	WHITE	Y
5JF	C	DOWN	1	BLACK	Y	456
6IT	B	DOWN	1		X	566
3CB	A		2	WHITE	X	921
1AL
6KT	B	UP	3	WHITE	X	421
4JK		UP		WHITE	X	689
7LZ				BLACK	Y
9IM	C	DOWN	2		X	674

The result that I want is 9, because 9 accounts have blank values, I don't want to se how many blanks, 19 would be wrong.

How can I make in DAX a measure that will calculate that? I was trying with this but is not working when soing the sum

d_HighestBlank_AccountOwner =

VAR BLANKA = COUNTBLANK(TABLE[COLUMNA])

VAR BLANKB = COUNTBLANK(TABLE[COLUMNB])

VAR BLANKC = COUNTBLANK(TABLE[COLUMNC])

VAR BLANKD = COUNTBLANK(TABLE[COLUMND])

VAR BLANKE = COUNTBLANK(TABLE[COLUMNE])

VARBLANKF = COUNTBLANK(TABLE[COLUMNF])

VAR IFBLANK =

IF( BLANKA <> 0, BLANKA) ||

IF( BLANKB <> 0, BLANKB) ||

IF( BLANKC <> 0, BLANKC) ||

IF( BLANKD <> 0, BLANKD) ||

IF( BLANKE <> 0, BLANKE) ||

IF( BLANKE <> 0, BLANKF) ||

Is there a DAX function that is equal as "Do this THAT contains..."?

[gadielsolis]

Hello @Aydeedglz ,

Please try the following DAX:

If this works please mark this as accepted solution.

[Aydeedglz]

Hi, I am doing the same and is not working for me, the issue I think is that my table have more columns and rows, I need to count the Account column, not the whole table. I provided an example table but is much bigger becuase I can't share the original data.

How can instead of sumx the whole table, sum only the number of "account"?

I want the distinct count of the "account" column. How many distinct accounts have a blank in column a or b or c or d or e or f.

[v-yiruan-msft · Accepted solution]

Hi  @Aydeedglz ,

You can update the formula of measure [d_HighestBlank_AccountOwner] as below and check if it can achieve your requirement.

d_HighestBlank_AccountOwner =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Account] ),
    FILTER (
        'Table',
        'Table'[ColumnA] = BLANK ()
            || 'Table'[ColumnB] = BLANK ()
            || 'Table'[ColumnC] = BLANK ()
            || 'Table'[ColumnD] = BLANK ()
            || 'Table'[ColumnE] = BLANK ()
            || 'Table'[ColumnF] = BLANK ()
    )
)

Best Regards

[Aydeedglz]

This one worked! Really thanks for the help!