Distinct count of column A if column B has blank

[Aydeedglz]

Hi I am trying to distinct count all of the "accounts: and count how many blanks are in the column "customer". 

Account    Customer
1

1                A

2                B

3

3

4                C

5                D

5                D

5                D

Example there are 9 rows, 5 accounts and of those 5, 2 have blank costumers, but there are 3 blank rows (account 3 is two times). The result I want to see is 2, there are 2 accounts with blanks.

Take in consideration is a much larger table with more rows and columns.

[Anonymous]

Hi @Aydeedglz ,

I used your sample data and you can check the results below:

Count of Blank = VAR _t = ADDCOLUMNS('Table',"Count of Blank",COUNTAX(FILTER(ALL('Table'),[Account]=EARLIER([Account])&&[Customer]=BLANK()),[Account]))
RETURN MAXX(_t,[Count of Blank])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Aydeedglz]

Is not working, I have several columns to be taken in consideration and at the moment I am adding them I get an error, and when only using one I get as a result "Blank"

[Anonymous]

Hi @Aydeedglz ,

Could you share your sample data?

Best regards,
Community Support Team_ Scott Chang

[Aydeedglz]

I can't is private data, sorry

[vicky_]

Hey - try something like the following:

Measure = CALCULATE(DISTINCTCOUNT(Table[Account]), Table[Customer] = "")

[Aydeedglz]

Thanks! I am doing some testing in the data and I am getting the same as the number of blank rows, is there a way to calculate also the blank rows in customer and that it counts the rows in the account? following the example it would be "3", so I can compare the result, thanks. (I can't go row by row is a big file)