topic Dax code for identifying if an ID is part of multiple groups in DAX Commands and Tips

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

Anonymous — Thu, 28 Sep 2023 11:26:33 GMT

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.

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

SamInogic — Fri, 29 Sep 2023 06:24:54 GMT

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/

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

Anonymous — Wed, 04 Oct 2023 09:22:24 GMT

It works.
Thanks a lot for your help here.