Calculate Count of groups where values for all items in the group is blank

[RustyNails]

Hello, I have a seemingly very simple problem but I'm having trouble creating DAX for it. I have the following table:

ID	Color	Value
101	Red	a
101	Green
101	Blue	b
102	Red	a
102	Green	b
102	Blue	c
103	Red
103	Green
103	Blue
104	Red
104	Green	a
104	Blue
105	Red
105	Green
105	Blue

All I want to count in this table are ID's where the ALL values are blank for the color categories. In this case, my answer is 2 because only 103 and 105 satisfy the condition. I was trying to do this: 

CALCULATE(

DISTINCTCOUNT(Table[ID],

     SUMMARIZE(Table, Table[ID], "criteria",

          COUNT(Table[Value])>1

          )

     )

But its summarizing all counts for ID. Any help is appreciated. Thanks.

[evandrocunico]

Hi @RustyNails check this way:

new table:

tab_summarized = SUMMARIZE(Table;Table[id]; "col_nulls";COUNTBLANK(Table[value]) - COUNT(Table[value]))

measure:

distinct id with nulls = CALCULATE(DISTINCTCOUNT(tab_summarized[id]);tab_summarized [col_nulls]=0)

regards

[RustyNails]

Hello, it didn't work 😞 I tried to filter on the list to see if it brought back the correct ID's but it is still including the ones that have values in it.

[Cmcmahan · Accepted solution]

I feel like there should be a much more elegant way to get this result, but I was able to get it with this:

GroupBlankCount = COUNTROWS( FILTER(SUMMARIZE(Table2, Table2[ID], "Values", MAX(Table2[Value])), [Values]="")) 

[RustyNails]

EUREKA! That worked. I fully agree. Business Objects allows you to write queries like these using "For Each" function, which makes life much easier. 

Thank you.