Forum Discussion
Counting Distinct Combinations
Hi All,
For a scenario of consultants at a business providing service to clients, I have the following simple measures to count the number of distinct clients (individuals at companies) to receive high touch service within a time period, as well as the number of distinct consultants providing high touch service during the period. I use this in a matrix with companies in rows and months as columns.
Distinct Clients High Touch =
CALCULATE(DISTINCTCOUNT(ServiceHistory[Client Name]), ServiceHistory[Service Type]=“High Touch”)
Distinct Consultants High Touch =
CALCULATE(DISTINCTCOUNT(ServiceHistory[Employee Name]), ServiceHistory[Service Type]=“High Touch”)
I want to create a measure to count the total across variations of consultants/clients (e.g. if consultant #1 services 4 clients, consultant #2 services 6 clients, consultant #3 services 5 clients, the total would be 15 at that company). I tried the following measure, but the values are too high. What have I got wrong?
ConsultantClient Combinations =
SUMX(SUMMARIZE(ServiceHistory, ServiceHistory[Client Name], ServiceHistory[Employee Name]), CALCULATE(DISTINCTCOUNT(ServiceHistory[Activity ID]), ServiceHistory[Service Type]=“High Touch”))
Activity ID here is a unique identifier for interactions (although there could be duplication, e.g. if a consultant meets with 3 clients as a group, there would be three records with the same Activity ID).
Thank you!
- Anonymous4 years ago
Hi gambleave ,
I have created a data sample for test.
Could you please tell me what's your expected output? Refer to:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- AnonymousNot applicable
Hi gambleave ,
I have created a data sample for test.
Could you please tell me what's your expected output? Refer to:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. - Ashish_MathurSuper User
Hi,
Does this work?
Measure 1 = DISTINCTCOUNT(ServiceHistory[Activity ID])
Measure 2 = SUMX(FILTER(SUMMARIZE(generate(values(ServiceHistory[Client Name]),values(ServiceHistory[Employee Name])), ServiceHistory[Client Name], ServiceHistory[Employee Name]),"ABCD",[Measure 1]),ServiceHistory[Service Type]="High Touch"),[ABCD])
If this does not work, then share the link from where i can download your PBI file and show the expected resutl very clearly.
- gambleaveHelper II
Thank you very much for your help Ashish!