Data Modelling and relationships for 2 sets of data.

[Mullz]

Hi all,

I've tried everything in my current knowledge of PBI (Isn't much) to try and solve this and am now stuck.

I have two systems I need to report on in one report:

Desk Booking System

Room Access

Example Desk Booking System Data:

Staff Number	Full Name	CheckOutTo	Desk Booked
e000111	Bob Banjo	4/1/2022	5.05
e000112	Jennifer Jill	4/1/2022	3.05

Example Room Access Data:

Staff Number	Full Name	DateofAccess	Floor
e000111	Bob Banjo	4/1/2022	5th
e000113	Auditor	4/1/2022	5th
e000112	Jennifer Jill	4/1/2022	3rd

So I need to generate a report which shows the date and people who had booked desks and those who haven't.

When I create just a plain table visual I add Date of Access and Full Name but when I add 'Desk Booked' it duplicates the date for each person and adds the desk booking even though they didn't book on that date.

For instance Bob Banjo could have booked in total 18 desks over a period of 4 months, the table will show 18 rows of Bob Banjo with the 'DateofAccess' being 4/1/2022 and then each desk booked rather than just matching the date of booking and date of access then just showing the desk for that date.

Is this a relationship issue between the two models?

I've tried adding relationships between staff number on both and that didn't work and also the two dates but they both give the same result 😞

Any ideas?

Thanks

David

[Mullz]

Hi @JoaoMarcelino 

Thanks for the in-depth response, it is indeed what I'm looking for, I have yet however had the time at work to run through it all to get it to work but hopefully I'll get time this week to look at it.

Thanks again!

David

[Ashish_Mathur]

Hi,

Please show the expected result.

[JoaoMarcelino · Accepted solution]

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 🙂

[JoaoMarcelino]

It was a pleasure, David!

Keep knocking data and having fun with Power BI 🙂
Cheers
Joao Marcelino