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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.