Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RustyNails
Helper III
Helper III

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

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
evandrocunico
Resolver III
Resolver III

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

 

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.

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.