Discount count multiple tables

[Anonymous]

I have these two tables:

Assigment:

empid projid      

101	1054
101	1050
103	1052
107	1053
102	1053
104	1051

Projects:

projid  name   customer

1050	Sun	Philips
1051	Moon	ASML
1052	Stars	ASML
1053	Galaxy	DLL
1054	Mars	Philips

As you can see, projid 1050 and 1054 and projid 1051 and 1052 have the same customer. I want to calculate the amount of projects an employee had for different customers, but if an employee has projid 1050 and 1054, it will count as 1 project. Does someone knows how to do this?

[v-jayw-msft · Accepted solution]

Hi @mrstillchicken,

Here’s the sample data I used according to your description:

Please check following steps as below and see if the result achieve your expectation:

1. Manage relationship between two tables:

2. Create measure:

    distinctcount = CALCULATE(DISTINCTCOUNT(Projects[customer]),FILTER(Assigment,Assigment[empid] = MAX(Assigment[empid])))

3. Result would be shown as below:

BTW, Pbix as attached. Hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.