Counting Distinct Combinations

[gambleave]

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!

[Ashish_Mathur]

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.

[gambleave]

Thank you very much for your help Ashish!

[v-eqin-msft · Accepted solution]

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.