Linking 3 tables together

[Power_BI_C_user]

Hello

I'm having a problem in powerbi desktop with a connection to one of my tables, I'll put example tables here first then the problem with pictures, sadly I'm not allowed to share the original files since it contains sensitive data.

I did look into other peoples posts, but couldn't fix my connection with those, maybe someone can help 🙂

Table 1 connects to Table 2 Many to Many: Both

Table 1 

date	user id	Name	Norm hours per person	Monthly norm hours
01.01.2023 00:00	447	Username 1	176	100,00%	176
01.01.2023 00:00	451	Username 2	176	100,00%	176
01.01.2023 00:00	383	Username 3	132	75,00%	176
01.01.2023 00:00	455	Username 4	176	100,00%	176
01.01.2023 00:00	321	Username 5	140,8	80,00%	176
01.01.2023 00:00	395	Username 6	176	100,00%	176
01.01.2023 00:00	453	Username 7	44	25,00%	176
01.01.2023 00:00	459	Username 8	114,4	65,00%	176
01.01.2023 00:00	307	Username 9	132	75,00%	176

Table 2 connects to Table 3 Many to One: Single

Table 2

user id	name	Deparment
447	Username 1	department 0
451	Username 2	department 1
383	Username 3	department 1
455	Username 4	department 1
321	Username 5	department 1
395	Username 6	department 1
453	Username 7	department 1
459	Username 8	department 1
307	Username 9	department 1

Table 3

date	user id	projects that are related to users	Hours in those projects
01.01.2023 00:00	447	project1	2
01.01.2023 00:00	451	project1	2
01.01.2023 00:00	383	project1	3
01.01.2023 00:00	455	project1	3
01.01.2023 00:00	321	project1	4
01.01.2023 00:00	395	project1	4
01.01.2023 00:00	453	project1	5
01.01.2023 00:00	459	project1	5
01.01.2023 00:00	307	project1	6

Problem description:
I have created an excel table which is connected to several instances of MySQL data and it sums the year total.
The excel table has names of employees that I can use to link to the other data, which includes the data that I need (Norm month hours per person – each person has a set number based on either they are part time or full time).

Here is a badly photoshopped image of what it should look like:

Sum of Norm_Employee is the number which should appear, but instead I get this:

Now to the data, how it’s setup.
Report view\Matrix table
Rows:
Nimi(name) – dw employees[user_full_name]
Columns:
Month – dw nominal hours[period]
Sum of Norm_Employees – ID ja nimi_v3v2_work_excel[Norm_employee]
Max hours per month – dw nominal hours[hours]

Model view\connections

Connections
1. ID ja nimi_v3_v2_work_excel – dw employees (Many to many *:*) Both (ID ja nimi_v3_v2_work_excel is the only excel table in the project, others are all MySQL connections)
2. dw employees – dw hours (Many to one *:1) Single
3. dw nominal_hours – dw hours (many to many) Both
4. dw projects – dw hours (many to many) Both
The problem here seems to be only with the first one or the second one (many to one) single, I’ve tried all possible permutations, the one that seems to show real numbers are the way it is setup now, just it totals everything and I’m not sure where to go from here.

I've been trying on my own for a while now, but it seems I have hit a wall.

Thank you so much for reading this and if you can help even a bit it is greatly appreciated

Kind regards

Tom

[Power_BI_C_user]

Hello

I found professional help for this issue and it has been resolved. It seems that the whole problem was with the model and the logic behind it, we solved this issue by creating a separate Date table and linking dates to it instead, if anyone has this issue then I hope this can help 🙂 

Kind regards

Tom