Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Distinct by Grouping

Hi, This is a very simple thing but I cant seem to get to the bottom of it.   I need to create a measure that gives me the count distinct by a grouping.   For example TableA Group   Attributes...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try a measure 

    calculate(distinctcount(Table[Attributes]), filter(allselected(Table), Table[Group] = max(Table[Group] ) ) )

smpa01's avatar
smpa01
Community Champion
4 years ago

Anonymous  try this

 

 

Measure2 =
CALCULATE (
    COUNTX ( SUMMARIZE ( t2, t2[Group], t2[Attribute] ), t2[Group] ),
    ALLEXCEPT ( t2, t2[Group] )
)
Measure3 = 
CALCULATE (
    SUMX ( VALUES ( t2[Group] ), CALCULATE ( DISTINCTCOUNT ( t2[Attribute] ) ) ),
    ALLEXCEPT ( t2, t2[Group] )
)

 

 

 

nothing wrong with amitchandak 's solution. But for large tables I find ALLSELECTED not to be performant and I would only use that where it can't be avoided.

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

smpa01 Is there a reason for the iterators rather than writing this?

CALCULATE ( DISTINCTCOUNT ( t2[Attributes] ), ALLEXCEPT ( t2, t2[Group] ) )

 

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    AlexisOlson  great..skipped my mind.