2 fields requiring reference from the same field

[michaelkwb]

hi,

I have 2 fields, POL (port of loading) and POD(pod of discharge). These are 5 letter codes that shows both country and city. I also have a master list of all portcodes with seperate columns for country and city.

I initially created a relationship between both sheets selecting POD and the portcode in the master list. When I select country in the charts, it shows the POD countries. This was what I wanted.

however, when I decided to include POL into my visualisations and wanted to show the countries of the POL, it was unable to link the POL countries when I select country from the master list in the visualisation. It still shows the POD countries.

Can someone advise on the steps I will need to take?

[SteveCampbell · Accepted solution]

A coiuple of ways, you could have multiple relationships and write DAX with USERELATIONSHIP. But this is quite complicated.

For the descriptopn, it sounds like you could create a star schema with three dimensions:

Country/City,

Product (with a unique product id to link to pod and pol)

Then two fact tables, with

POD 

POL

Check out the documentation:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can!