Forum Discussion

ab2022's avatar
ab2022
Frequent Visitor
3 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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 )
)

 

ab2022's avatar
ab2022
Frequent Visitor
3 years ago

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.