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

Comparing Values From Different Non-Related Tables

Basically, I am trying to generate a geographical map for my cell phone records.  

I have a table of cell records and created a new column called 'Area Code' that pulls the first three digits from my incoming/outgoing calls.  I have a second table with longitude's and latitudes in relation to every US area code. 

I am looking for a way to create new columns on my cell records tables for Longitude and Latitude.  I would like to compare the values for each 'Area code' column if they equal each other, then I would like the result to be the corresponding longitude and latitude.

9 REPLIES 9
Super User III
Super User III

Re: Comparing Values From Different Non-Related Tables

Hi @jrutherford

 

If I understand correctly what you need, you could try this:

Create a relationship between both tables through the Area Code (Many-to-one, the table with the coordinates on the many side)

You can then bring the longitude and latitude into your cell records table with RELATED( 😞

 

LongitudeColumn = RELATED(TableCoordinates[Longitude])

LatitudeColumn = RELATED(TableCoordinates[Latitude])

jrutherford Frequent Visitor
Frequent Visitor

Re: Comparing Values From Different Non-Related Tables

It's not letting me relate the tables Many to One.  " The cardinality you selected isn't valid for this relationship."  The only selection it lets me accept is many to many but when I go to use the RELATED(): it will not let assign a column or table. 

 

Can you think of a non-relationship DAX for this?

Super User III
Super User III

Re: Comparing Values From Different Non-Related Tables

@jrutherford

That's weird. Are the area codes in your Longitude/Latitude table not unique? They should. Would it be possible to eliminate the duplicates in that table?  Otherwise we'll face issues with other approaches as well. 

jrutherford Frequent Visitor
Frequent Visitor

Re: Comparing Values From Different Non-Related Tables

Yeah, Area Codes are not great when it comes to location based plotting.  For example Area Codes for Key West, Florida are 305 & 786 and they both have the same latitude & longitude.  It may not be possible since a majority of my calls are in the Dallas area which has three area codes that serve the same coordinates.   I wonder if I should find like values are just barely change them so that they are unique like 32.78306, 32.78307, 32.78308.  It might slightly throw off my coordinates but by how much?

 

Or maybe look for a better area code data source. 


 


 

 

Super User III
Super User III

Re: Comparing Values From Different Non-Related Tables

@jrutherford

Ok but you seem to be saying that what is not unique is the longitude and latitude. That should not matter. If the area codes in your Longitude/Latitude table are unique we're fine. Are they?

Can you share the pbix? That would make things faster

Or if you have sensitive info maybe just a pbix with the two tables involved that we are discussing  

jrutherford Frequent Visitor
Frequent Visitor

Re: Comparing Values From Different Non-Related Tables

https://www.dropbox.com/s/k5fwlht3etpe6w1/Template%20Dashboard%20-%20Phone%20Records.pbix?dl=0

 

Yes. It is personal information but I've grab a limited section to give you a snapshot of the dashboard. 

jrutherford Frequent Visitor
Frequent Visitor

Re: Comparing Values From Different Non-Related Tables

Basically I'm trying to create a heat map of call frequency to certain area codes.  So I will need distinct count of area codes as well.  I got stumped on the longitude/latitude before I got to that measure. 

jrutherford Frequent Visitor
Frequent Visitor

Re: Comparing Values From Different Non-Related Tables

Was that dropbox link able to work for you?

 

 

Super User III
Super User III

Re: Comparing Values From Different Non-Related Tables

@jrutherford

 

I had a look but couldn't make much of it. The problem is that the Latitude/Longitude pairs are defined not only by area codes but by area codes and the City. So either:

 

1. You need to take the city into account to make the lookup (i.e., are code plus City) --> Can the 'Call to' column can be used to extract the city?

2. or  you have to make the area codes in the Latitude/Longitude pairs table unique. Otherwise what Latitude/Longitude do you take if there are several for the same area code? How you do this depends on your requirements. Maybe keep one that is representative? Maybe it doesn't matter that much because they are all close to one another? Maybe you need the average?

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors