Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
These are my tables, Table 1--- skills- > employees have multiple skills, Development item---> Focussed to do in future.
In Table 2 and Table 3, you can see there is a relation from Table 1 SKILLS & Development item.
Table 1 basically denotes total organization information, from that Skills and Development items created in Table 2 and Table 3.
So, Table 1 has a total of 6 employees in an organization.
My expected result is, that I want to bring the count of employees as 6 (table 1) to table 3. anyone pls Let me know the solution how it can be achieved.
Table 1
Employee name | skills | Development item |
arjun | Python, data, excel | Prepare my team |
rithik | Program, java | 0 |
edvid | Chemistry,physics,maths,biology | 0 |
tharun | javascript | Training |
elsa | 0 | mentorship |
jose | 0 | Workflow, create model |
Table 2
Employee name | skills |
arjun | Python |
arjun | data |
arjun | excel |
rithik | Program |
rithik | java |
edvid | Chemistry |
edvid | ,physics, |
edvid | ,maths, |
edvid | biology |
tharun | javascript |
Table 3
Employee name | Development items |
arjun | Prepare my team |
tharun | Training |
elsa | mentorship |
jose | Workflow |
jose | create model |
Expected Output.
Employee name | Development items(count) | Count of employees from table 1 |
arjun | Prepare my team |
|
tharun | Training |
|
elsa | mentorship |
|
jose | Workflow |
|
jose | create model |
|
Totals | 5 | 6 |
Solved! Go to Solution.
Hi @AtchayaP ,
I think you can try this code to create a measure to calcualte [Count of Employees from table 1].
Count of employees from table 1 = IF(HASONEFILTER('Table 3'[Employee name]),BLANK(),DISTINCTCOUNT('Table 1'[Employee]))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@AtchayaP , Create two common table
Employee = distinct(union(distinct(Table1[Employee name]),distinct(Table2[Employee name]),distinct(Table3[Employee name])))
Development items= distinct(union(distinct(Table1[Development items]),distinct(Table2[Development items])))
Join the First one with all three tables and the second with Tables 1and 2, Now use measures from tables 1 and 3 with common dimension
Employee name | skills |
arjun | Python, data, excel |
rithik | Program, java |
edvid | Chemistry,physics,maths,biology |
tharun | javascript |
elsa | 0 |
jose | 0 |
Table 2
Employee name | skills |
arjun | Python |
arjun | data |
arjun | excel |
rithik | Program |
rithik | java |
edvid | Chemistry |
edvid | ,physics, |
edvid | ,maths, |
edvid | biology |
tharun | javascript |
Table 3
Employee name | Development items |
arjun | Prepare my team |
tharun | Training |
elsa | mentorship |
jose | Workflow |
jose | create model |
This is how my tabel looks, I do not have Development items in table 1. just for explanation, I attached how development item relates to table 3.
Now can you tell me the possible solution?
Hi @AtchayaP ,
I think you can try this code to create a measure to calcualte [Count of Employees from table 1].
Count of employees from table 1 = IF(HASONEFILTER('Table 3'[Employee name]),BLANK(),DISTINCTCOUNT('Table 1'[Employee]))
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |