Forum Discussion
Discount count multiple tables
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?
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.
1 Reply
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.