cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RustyNails Regular Visitor
Regular Visitor

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

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

IDColorValue
101Reda
101Green 
101Blueb
102Reda
102Greenb
102Bluec
103Red 
103Green 
103Blue 
104Red 
104Greena
104Blue 
105Red 
105Green 
105Blue 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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

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]="")) 

View solution in original post

4 REPLIES 4

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

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 Regular Visitor
Regular Visitor

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

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.

Highlighted
Super User III
Super User III

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

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]="")) 

View solution in original post

RustyNails Regular Visitor
Regular Visitor

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

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors