Forum Discussion
Mapping between four table
Hi, I have four type of tables and I must map them to get count result for reporting but I have issues about it. Please help me on solving this problem. First I want to show you my minimal design this columns needed for mapping
My employee table
| id | name | position_id |
| 1 | Nabi | 1 |
| 2 | John | 2 |
| 3 | Jack | 1 |
My position table
| id | name |
| 1 | Software Developer |
| 2 | Data analyst |
My training table
| id | name |
| 1 | Power bi |
| 2 | SQL |
| 3 | C# |
My pos_tra_mapping table. This is how I managed training for position like(man - for mandatory, asap - for As soon as possible, pref - for Preffered trainings)
| id | position_id | training_id | type |
| 1 | 1 | 1 | man |
| 2 | 1 | 2 | pref |
| 3 | 1 | 3 | asap |
| 4 | 2 | 2 | asap |
And finally my emp_tra_mapping. Is employee passed this trainings or not. At the end I must create matrix for trainings will show after this table how my report must look like.
| id | emp_id | training_id | training_date |
| 1 | 1 | 1 | 2020-06-01 |
| 2 | 1 | 2 | null |
| 3 | 1 | 3 | 2019-06-01 |
| 4 | 2 | 2 | 2020-06-01 |
| 5 | 3 | 1 | null |
| 6 | 3 | 2 | null |
| 7 | 3 | 3 | 2019-06-01 |
Now lets look at my final report. Must train gets from emp_tra_mapping where his position in pos_tra_mapping for selected training is "man". Mandatory already trained gets count from emp_tra_mapping where not null and where his position in pos_tra_mapping for selected training is "man". pref and asap also as an mandatory
| Courses | Power bi | SQL | C# |
| Mandatory must train | 2 | 0 | 0 |
| Mandatory already trained | 1 | 0 | 0 |
| ASAP must train | 0 | 1 | 2 |
| ASAP already trained | 0 | 1 | 2 |
| Pref must train | 0 | 2 | 0 |
| Pref already trained | 0 | 0 | 0 |
Hope you understand me what I want 😞
4 Replies
Hi Anonymous ,
Just want to check if you have got any relationships between these tables set-up in Power BI?
If yes, can you share screenshot on the data model please?
Thanks,
Pragati
Based on your business logic follow the filter direction arrows in your data model and see if they allow you to apply the desired filters. Adjust the direction as needed.
