Dax code for identifying if an ID is part of multiple groups

[Anonymous]

Hi,

I have a dataset as shown below:

ID	Group
100	A
100	B
200	C
300	D
400	E
400	F
500	G
500	A
500	B
600	C
600	D
600	E
600	F
600	G

From this dataset I want to get the number of IDs which are part of only one group and number of IDs that are part of 2 groups and so on. In this case, IDs 200 and 300 are part of only single groups while 100 and 400 are part of 2 groups. 
Similarly 500 and 600 are part of more than 2 groups.

So could someone help me with measure calculations that gives me the result as:
Number of IDs that are part of a single group           : 2
Number of IDs that are part of 2 groups                    : 2
Number of IDs that are part of more than 2 groups  : 2

Thanks a lot for your support here.

[Anonymous]

It works.
Thanks a lot for your help here.

[SamInogic · Accepted solution]

Hi @Anonymous ,

You can follow below steps,

Create a new custom column to count the ID that are part of a group,

CountData = CALCULATE(

    COUNT(Grps[Group]), // counting the Type

    FILTER(

        Grps, // filtering the table to get the count of type groupoed by Make

        Grps[ID] = EARLIER(Grps[ID])

    )

)

Then Create 3 Measure in order to get Single Group, Two Group, Multiple Group ID counts with below DAX expressions,

Single Grp = CALCULATE( COUNT(Grps[Group]), Grps[CountData] =1)

Two Grps = CALCULATE( COUNT(Grps[Group]), Grps[CountData] =2)

Multiple Grps = CALCULATE( COUNT(Grps[Group]), Grps[CountData] >2)

The result will be as follows,

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/