Forum Discussion
Unique count on same table subgroup data
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.
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
3 Replies
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")) + 0Hi 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 ) )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.
