cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User
Super User

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

Super User
Super User

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

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,914 guests
Please welcome our newest community members: