Data Modeling with Timesheets (Data Ambiguity Relationship Issue)

[williamvicary]

I'm attempting to visualise our timesheets taking into account holiday time available, however I'm running into problems with needing to join the same tables together to populate all of the data, I'm sure I'm close but the visualisation element of this is currently baffling me.

I've got the following tables (there is much more but this is the crux of the issue):

- DateDim (Standard Date table with datekey as lookup plus workinghours (which is essentially, psuedo code, "if not weekend = 7.5")

- TimeSheets (datekey, hours, staffkey)

- Holidays (staffkey, datekey, hoursoff)

- Staff (staffkey, name)

I'm looking to produce a report such as:

- Staff:name, Timesheet:hours, Holidays:hoursoff, DateDim:workinghours

Which is sliceable by date and filterable by staff member (to provide deeper insight into hours worked in a chart etc). 

Am I missing something painfully obvious? I've got a background in SQL and I'd typically just do the multiple joins (which PowerBI complains about data abiguity) and then use that to generate the data. I tried duplicating the Staff table but this didn't really work out as expected.

Anything I should look at as reference? I'm very new to this, just learning the ease of reporting PowerBI offers - it's great but comes with a bit of a learning curve!!

Edit: Typo.

[williamvicary · Accepted solution]

Managed to resolve this using an example from another thread. The issue was that some of my fact tables had other relationships, removing other joins and rebuilding my relationships resolved the issue and I was able to create the relationships based on my example.

For reference, "WorkingHours" needs to be a calculated metric in case anyone else is in need of this, otherwise you just get the number of working hours based on the filters applied, this filter would only apply to time not staff member and/or number of staff selected - you can multiply this up with something like as follows:

WorkingHours = (SUM(DateDim[isWorkingDay])*7.5)*COUNTA(Employees[Full Name])