topic Unique count on same table subgroup data in DAX Commands and Tips

Unique count on same table subgroup data

ab2022 — Fri, 04 Nov 2022 17:43:56 GMT

I am new to Power BI DAX coding. I’m trying to figure out how to create a measure to unique count

1. How many ProductID counts that have at least 1 comment
2. How many ProductID counts that don't have any comment?

Any tips or guidance it would be very much appreciated.

The data look something like this:

DeptID	ProductID	InspectorID	InspectorComments
1A	101	10	Needs improvement
1A	101	20	null
1A	101	30	Waiting for solution from engineers
2B	201	10	null
2B	201	15	null
2B	201	30	null
2B	201	40	null
2B	211	1	Ref. to factory
2B	211	2	Waiting for approval
2B	211	3	Waiting for parts
2B	211	4	Waiting for customer response
3C	301	5	No respone from customer
3C	302	5	Wrong parts
3C	303	6	Waiting for parts
3C	304	7	null

Expect result

DeptID	ProductID	InspectorComments
1A	101	Yes
2B	201	No
2B	211	Yes
3C	301	Yes
3C	302	Yes
3C	303	Yes
3C	304	No

Unique ProductIDs count have at least 1 comments : 5
Unique ProductIDs count that do not have any comments at all : 2

Thanks in advance.

Re: Unique count on same table subgroup data

tamerj1 — Fri, 04 Nov 2022 18:27:43 GMT

Hi ab2022

Please try

Measure

InspectorComments = IF ( ISEMPTY ( VALUES ( 'Table'[InspectorComments] ) ), "No", "Yes" )

Measure

Count Yes = SUMX ( SUMMARIZE ( 'Table', 'Table'[DeptID], 'Table'[ProductID] ), IF ( [InspectorComments] = "Yes", 1 ) )

Measure

Count No = SUMX ( SUMMARIZE ( 'Table', 'Table'[DeptID], 'Table'[ProductID] ), IF ( [InspectorComments] = "No", 1 ) )

Re: Unique count on same table subgroup data

Greg_Deckler — Fri, 04 Nov 2022 18:38:13 GMT

ab2022, tamerj1 beat me to the punch but posting my very similar solution here since I took the time to create it. PBIX is attached below sig.

YesNo = IF(COUNTROWS(FILTER('Table',[InspectorComments] <> BLANK())) + 0 > 0,"Yes","No") Unique ProductIDs with Comments = COUNTROWS(FILTER(SUMMARIZE('Table',[DeptID],[ProductID],"__YesNo",[YesNo]),[__YesNo] = "Yes")) + 0 Unique ProductIDs without Comments = COUNTROWS(FILTER(SUMMARIZE('Table',[DeptID],[ProductID],"__YesNo",[YesNo]),[__YesNo] = "No")) + 0

Re: Unique count on same table subgroup data

ab2022 — Fri, 04 Nov 2022 20:09:15 GMT

Hi Greg_Deckler,

Thank you so much for your solution🙂. It took me couple days to search on Internet, but could not figure it out.