Count of distinct values of one column, grouped by values of another column

[Vaibhav_S]

Hello Experts,

I am relatively new to PowerBI and have a query regarding counting distinct values and grouping them by values in another column.

I have a dataset with Customer ID's and the groups that they have shopped. 

I need a column which will return the number of different groups (unique groups) that each customer has shopped.

Sample dataset:

Customer ID	Group
A1	Mercedes
A1	BMW
A1	Mercedes
A1	Audi
A1	BMW
A2	Jaguar
A2	Hyundai
A2	Hyundai
A2	Hyundai
A3	Jaguar
A3	Jaguar
A3	Honda
A3	Honda
A3	Hyundai
A3	Toyota

Sample Output:

Customer ID	Group	Output Column
A1	Mercedes	3
A1	BMW	3
A1	Mercedes	3
A1	Audi	3
A1	BMW	3
A2	Jaguar	2
A2	Hyundai	2
A2	Hyundai	2
A2	Hyundai	2
A3	Jaguar	4
A3	Jaguar	4
A3	Honda	4
A3	Honda	4
A3	Hyundai	4
A3	Toyota	4

Thanks !

[rajendraongole1 · Accepted solution]

Hi @Vaibhav_S - create a measure as below. it works

UniqueGroupsCount =

CALCULATE(

    DISTINCTCOUNT('carss'[Group]),

    ALLEXCEPT('carss', 'carss'[Customer ID])

)

or create a calculated column it works :

Hope this helps.