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
eggn1n3
New Member

Find latitude and longitude from another table

Hi all,

 

I am having a hard time in Power BI to do the following:

 

One table called "location info" with fields: location-ID, city name, longitude and latitude

Another table called "circuit" with: circuit-ID, location-ID-source, location-ID-destination.

 

How can I create a new table that shows: circuit-ID, longitude/latitude-source and longitude/langitude-destination?

 

I created a relation between 'location info'[location-ID] -> 'circuit'[location-ID-source]. 

Then I was able to create new columns with: Source latitude = related('location info'[latitude]) and Source longitude = related('location info'[longitude])

 

However, I cannot do the same for destination latitude/longitude as Power BI only accept one relation?

 

Any insight would be appriciated.

1 ACCEPTED SOLUTION
Shivu-2000
Resolver III
Resolver III

Hi @eggn1n3,
These might be helpful.

Method 1: Using Inactive Relationships

  1. Create an inactive relationship between 'location info'[location-ID] -> 'circuit'[location-ID-destination].

  2. With the inactive relationship selected, you can still use the RELATED function similar to what you did for the source. Just replace "source" with "destination" in your formula:

    • Destination Latitude = RELATED('location info'[latitude])
    • Destination Longitude = RELATED('location info'[longitude])

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

 

Method 2: Using LOOKUPVALUE Function

  1. This method doesn't require any additional relationships.

  2. Create new calculated columns for Destination Latitude and Destination Longitude:

    • Destination Latitude = LOOKUPVALUE('location info'[latitude], 'location info'[location-ID], 'circuit'[location-ID-destination])
    • Destination Longitude = LOOKUPVALUE('location info'[longitude], 'location info'[location-ID], 'circuit'[location-ID-destination])

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax

 

Both methods will achieve the same result. Choose the one you find easier to understand and maintain.



If this post helps, then please consider Accept it as the solution to help the other members find it...

View solution in original post

2 REPLIES 2
eggn1n3
New Member

Thanks Shivu-2000; option 2 did work for me.

 

Shivu-2000
Resolver III
Resolver III

Hi @eggn1n3,
These might be helpful.

Method 1: Using Inactive Relationships

  1. Create an inactive relationship between 'location info'[location-ID] -> 'circuit'[location-ID-destination].

  2. With the inactive relationship selected, you can still use the RELATED function similar to what you did for the source. Just replace "source" with "destination" in your formula:

    • Destination Latitude = RELATED('location info'[latitude])
    • Destination Longitude = RELATED('location info'[longitude])

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

 

Method 2: Using LOOKUPVALUE Function

  1. This method doesn't require any additional relationships.

  2. Create new calculated columns for Destination Latitude and Destination Longitude:

    • Destination Latitude = LOOKUPVALUE('location info'[latitude], 'location info'[location-ID], 'circuit'[location-ID-destination])
    • Destination Longitude = LOOKUPVALUE('location info'[longitude], 'location info'[location-ID], 'circuit'[location-ID-destination])

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax

 

Both methods will achieve the same result. Choose the one you find easier to understand and maintain.



If this post helps, then please consider Accept it as the solution to help the other members find it...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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