cancel
Showing results for
Did you mean:

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

1               A

1               B

2               C

2               C

3               D

So the measure should say,

Group   Attributes    Measure (distinct values)

1               A                       2

1               B                       2

2               C                       1

2               C                       1

3               D                       1

So not that dissimilar to what a count distinct and group by clause would do in SQL.

Thanks a bunch!

1 ACCEPTED SOLUTION
Super User

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
4 REPLIES 4
Super User

``````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.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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

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

Super User

@AlexisOlson  great..skipped my mind.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!