Bridge Tables

[PedroModa]

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).

[adgeku7]

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.

[PedroModa]

But how would this new table relate to the point table?

[adgeku7]

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.

[PedroModa]

Okay, I don't know if I understand very well... Could you give me an example of how I would calculate the total overtime (taking it from the first table) since it doesn't have a team column? Would you do this by measure?

[adgeku7]

I think if you could provide some more sample information that would help. Looking back at your original question, do you need to know overtime by date and team, or just by team? Here is an example diagram of the orginal table based on the information you provided:

A straight calculation about be to calculate "TOTAL OVERTIME" on the orginal TEAM table (red text above), but this would not take into account the "DATE" because you have no date field on ERP. If you don't need to know overtime by date, then you could calculate a table this way:

Create the summarized table with just "ID", "NAME", "TEAM", then create a calculated column to sum overtime.