Connect fact sheets with different dimension

[joslaz]

Good day everybody!



I'm currently desperately trying to link three tables together so I can filter everything.


I following three tables:
tblEmployees
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Kevin[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arnold[/TD]
[/TR]
</tbody>[/TABLE]

tblPlan

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ID[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]02.01.2019[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]03.01.2019[/TD]
[TD]1[/TD]
[TD]Ill[/TD]
[/TR]
</tbody>[/TABLE]

tblQualification
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Qualification[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drill[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Package[/TD]
[/TR]
</tbody>[/TABLE]

Both tblQualification and tblPlan are connected to tblEmployees via the ID.

Now I would like to investigate
How many employees I have per week or per day, who have the qualification "Mount" and are not registered as "ill".


Unfortunately, I always get the error message that there is no relationship. Probably because they are different dimensions, right?


Best Regards

 

[Misca]

Looks to me like your fact table is (or at least should be) the tblPlan and the Employees and Qualifications should be the dimension tables. If you can set up your tables to a star schema around the plan table you should have no troubles filtering it.

 

[peter789]

If you're working in Power Pivot looks like you need to create a many to many relationship.
The solution to this is to create two extra tables with lists of unique IDs and Qualifications linked to a bridge table (your tblQualification). An Internet search will guide you through the process and the special use of CALCULATE to get the correctly filtered count of employees per day.

Peter