Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sem1
Frequent Visitor

Data Modeling: Connecting to a Dimension Table Twice

Hello,

 

I have a question about data modeling in Power BI. This is a simplified example of the data but it will suffice:

I have a main shipment fact table which includes keys to origin ports and destination ports:

 

BookingIDOriginPortIDDestinationPortID
11113
11221
11332

 

I also have a Locations table which contains information about the ports that both of these keys link to:

LocationIDLocations
1Shanghai
2Antwerp
3Rotterdam

 

These tables contain far more columns and information which is used, however, my question is what is the best way to model this?

Option 1:

In power query: merge queries to pull needed data (Port Name, Region, Country, etc.) into the main bookings table for both the origin and destination. Don't load the location table.

 

Option 2:

Load the location table twice. Create a relationships from the booking table to each copy of the location table.

 

Option 3:

Load the location table once. Use lookup to add DAX columns to my main bookings table.

 

Option 4:
??

 

Does anyone have feedback on the best way to handle situations like this? This isn't the only instance of this type of relationship in my data so I'll need to apply the solution elsewhere as well.

1 ACCEPTED SOLUTION

@sem1 you will be able to slice on original port (assuming it is active relationship) but if you also want to slice on destination port, in that case recommendation would be to have two table one for origin and one for destination and then set relatnship with respective table with shipment table.

 

Above will be more flexibile and will meet most/all the requirements.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@sem1 the best option is option 4, set relationship between location table and shipment table on both origin and destination port id. One relation will be active and other inactive coz there can be only one active relationship between tables. In mesure use USERELATIONSHIP  function for inactive relationship and you will able to achieve desired results.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

sem1
Frequent Visitor

Would that enable me to use both dimensions in a chart? Or would I only be able to use one. 

I.e.:

OriginPortDestinationPortShipments
ShanghaiRotterdam10
AntwerpShanghai5
RotterdamAntwerp13

 

 

I'd also like to be able to use slicers/filters to filter them. So for example if I look at the origin of Shanghai, I want to see the destinations I'm shipping to.

@sem1 you will be able to slice on original port (assuming it is active relationship) but if you also want to slice on destination port, in that case recommendation would be to have two table one for origin and one for destination and then set relatnship with respective table with shipment table.

 

Above will be more flexibile and will meet most/all the requirements.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors