Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm hoping the experts here can help me with a measure that I'm trying to create. Here is my data model. Table 1 is connected to Table 2 in a single direction Many to one relationship. Table 2 is connected to Table 3 in a Many to one Both direction relationship. I need measure that will count the distinct number of employees that have a a certain product. For example, If I put the Manger ID and the measure (the one I need created) in a table, I want to be able to filter on a product and get the distinct count of employees for each manager. Is that possible?
Table 1
Opportunity | Customer ID | Customer Name | Product | Qty |
DX-14 | 1234 | ABC | Blue | 3 |
DX-14 | 1234 | ABC | Green | 5 |
DX-15 | 5678 | ABC123 | Green | 7 |
DX-15 | 5678 | ABC123 | Blue | 9 |
DX-15 | 5678 | ABC123 | Black | 11 |
DX-15 | 5678 | ABC123 | Red | 13 |
DX-16 | 8798 | ABC456 | Red | 15 |
DX-16 | 8798 | ABC456 | Orange | 17 |
DX-16 | 8798 | ABC456 | Purple | 19 |
DX-16 | 8798 | ABC456 | Blue | 21 |
DX-17 | 3648 | ABC789 | Purple | 23 |
DX-17 | 3648 | ABC789 | Red | 25 |
DX-17 | 3648 | ABC789 | Black | 27 |
DX-17 | 3648 | ABC789 | Yellow | 29 |
Table 2
Customer ID | Customer Name |
1234 | ABC |
5678 | ABC123 |
8798 | ABC456 |
3648 | ABC789 |
Table 3
Manager ID | EMP ID | Customer ID | Customer Name |
B12 | A1234 | 1234 | ABC |
B12 | A1234 | 8798 | ABC456 |
B11 | A1238 | 8798 | ABC456 |
B12 | A1237 | 3648 | ABC789 |
B11 | A1238 | 3648 | ABC789 |
B11 | A1239 | 3648 | ABC789 |
B11 | A1235 | 1234 | ABC |
B11 | A1236 | 1234 | ABC |
B11 | A1235 | 5678 | ABC123 |
B11 | A1235 | 3648 | ABC789 |
B12 | A1237 | 1234 | ABC |
B12 | A1237 | 5678 | ABC123 |
B12 | A1237 | 8798 | ABC456 |
B11 | A1236 | 3648 | ABC789 |
Hi @Anonymous , could you share your expected result data?
For example, If I were to filter on Blue I would expect to see Manager B12 with a count of 2 employees and Manager B11 with a count of 3 employees.
Results
Manager | Product | Employee Count |
B12 | Blue | 2 |
B11 | Blue | 3 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |