Forum Discussion
Bridge Tables
Good morning everyone! I need some help!
I have a table (coming from my ERP) with the overtime records of employees. It has columns for ID, COST CENTER, and TOTAL OVERTIME. This is a fact table.
I also have another table (which is manually filled out by managers) that contains the TEAM each employee belongs to. This is also a fact table. It has columns for DATE, ID, NAME, and TEAM.
I need to create a chart that shows OVERTIME BY TEAM, however, the team information is in one fact table, and the overtime information is in another.
I need to find a way to bring this answer.
I thought about creating a bridge table between the two tables, establishing the relationship via the ID.
Bridge table[ID] -> overtime table[ID]
Bridge table[ID] -> team table[ID]
However, I'm encountering some problems...
If I select the ID from the bridge table and the overtime hours, it works.
If I select the ID from the bridge table and the teams, it works.
However, if I select the ID from the bridge table, the team, and the measure that calculates the overtime hours, my chart looks strange, as if there is no relationship with the team table.
I believe this happens because the relationship is set by ID and not by the team, but the only common field between the two tables is the ID column.
I cannot merge these tables because if there is an error in filling out the team table, it could affect the overtime table (which comes from the ERP and is validated).
5 Replies
I think the simplest solution would be to create a calculated table that contains unique Team data, and create the necessary calculations on this table. Something like:
TEAM = SUMMARIZE(<2nd Fact Table>, <2nd Fact Table>[TEAM], <2nd Fact Table>[DATE])Then create a calculated columns on the new TEAM called "Amount Overtime" which calculates the sum of overtime from your first table (ERP). This way you have the sum for overtime for each team on each date.
But how would this new table relate to the point table?
If you only need to "create a chart that shows OVERTIME BY TEAM", then the new calculated table does not need to be related, and the chart can use the new calculated table as the data source. If additional charts/data points are needed, you could related the new TEAM table to your other table by the "team name" column on the original table. But depending on your other need for other charts/visualizations, additional steps may be required.
