Data Modelling and relationships for 2 sets of data.

Hi Mullz 🙂

David, from what I could understand, you wish to have a table that looks something like this:

If that is the case, before you start building your report, you need to perform a little bit of data modelling:

1- To get the basis of a dimensional model I recommend you to read this small article: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
This will allow you to understand the need of working with dimensions tables and facts tables 🙂
(Dimensions are like the "perspectives" of analysis you can do of the facts tables; the facts tables are the events, that happen over time and that can be measured - usually dimensions filter the facts in a 1 to many relationships.)

2- Perform a bit of data modelling
2.1- Create a date/calendar dimension. Example on how to create a simple Calendar table dimension: Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD

2.2- Create the dimension "Staff", which of employees uniques (only do this if you don't have it already):
Go to Transform Data and select the query related to desk booking and click on Append Queries - as new

2.3- Select the other table (Room Acess) and click ok

2.4- Click on column Staff number and on CTRL key also click on Full name

2.5- Right-click with your mouse and choose remove others

2.6 - Right-click again and choose Remove Duplicates

2.7- This will leave you with a list of uniques of Staff number and full name, then rename your query to, for example, Staff:

2.8- Click on Close and Apply

3- Make the needed relationship:
3.1- Go to home -> Model

3.1 - Connect Calendar to Room table and desk table by picking up Date field and dragging and dropping it on "date  of acess" and then again on Checkoutto. Then do the same with Table staff, pick up the Staff number from Staff, drag and drop it to staff number on Room table and again to Staff number on Desk table:

3.1- If this relationships don't appear like this, you might need to edit them by clicking twice on the relationship and choosing the calendar to filter the other in a 1 -> * and the same with Staff to other in a 1 ->*
Example: 

Last but not least, create a table, drag the fields "Date" from Calendar (and not the date fields from facts tables), Full Name from dimension Staff (and from facts tables) and the Desk Booked from your facts table

Hope I was of assistance!
Cheers
Joao Marcelino

Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂