cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mullz
Frequent Visitor

Data Modelling and relationships for 2 sets of data.

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 NumberFull NameCheckOutToDesk Booked
e000111Bob Banjo4/1/20225.05
e000112Jennifer Jill4/1/20223.05

 

Example Room Access Data:

 

Staff NumberFull NameDateofAccessFloor
e000111Bob Banjo4/1/20225th
e000113Auditor4/1/20225th
e000112Jennifer Jill4/1/20223rd

 

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

1 ACCEPTED SOLUTION
JoaoMarcelino
Resolver II
Resolver II

Hi @Mullz 🙂

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

JoaoMarcelino_0-1653730331273.png

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

JoaoMarcelino_7-1653731600474.png

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

JoaoMarcelino_8-1653731650037.png

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

JoaoMarcelino_9-1653731743609.png

 

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

JoaoMarcelino_10-1653731790041.png

2.6 - Right-click again and choose Remove Duplicates

JoaoMarcelino_11-1653731833371.png

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:

JoaoMarcelino_12-1653731944223.png

2.8- Click on Close and Apply

JoaoMarcelino_13-1653731993829.png


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

JoaoMarcelino_14-1653732070782.png

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:

JoaoMarcelino_15-1653732264724.pngJoaoMarcelino_16-1653732286868.pngJoaoMarcelino_17-1653732297130.pngJoaoMarcelino_18-1653732321939.png

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: 

JoaoMarcelino_19-1653732536933.png

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

JoaoMarcelino_20-1653732688720.png

Hope I was of assistance!
Cheers
Joao Marcelino

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

View solution in original post

4 REPLIES 4
Mullz
Frequent Visitor

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
Super User
Super User

Hi,

Please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JoaoMarcelino
Resolver II
Resolver II

Hi @Mullz 🙂

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

JoaoMarcelino_0-1653730331273.png

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

JoaoMarcelino_7-1653731600474.png

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

JoaoMarcelino_8-1653731650037.png

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

JoaoMarcelino_9-1653731743609.png

 

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

JoaoMarcelino_10-1653731790041.png

2.6 - Right-click again and choose Remove Duplicates

JoaoMarcelino_11-1653731833371.png

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:

JoaoMarcelino_12-1653731944223.png

2.8- Click on Close and Apply

JoaoMarcelino_13-1653731993829.png


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

JoaoMarcelino_14-1653732070782.png

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:

JoaoMarcelino_15-1653732264724.pngJoaoMarcelino_16-1653732286868.pngJoaoMarcelino_17-1653732297130.pngJoaoMarcelino_18-1653732321939.png

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: 

JoaoMarcelino_19-1653732536933.png

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

JoaoMarcelino_20-1653732688720.png

Hope I was of assistance!
Cheers
Joao Marcelino

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

It was a pleasure, David!

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors