cancel
Showing results for
Did you mean:
Braunnz Frequent Visitor

## Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

Hi all,

I'm making the switch from Excel to PowerBI, and have a lot of learning to do about some of the changes in formulas/syntax.

My data (Table1) has columns for X and Y coordinates. I have a reference table (RefTable) with 20 locations and their XY coordinates, and basically determine which of the 20 points is the smallest absolute distance from the row in question.

The formula in Excel looked like this:

=LOOKUP(1,1/FREQUENCY(0,MMULT((RefTable[[Lat]:[Long]]-Table1[@[LAT]:[LON]])^2,{1;1})),RefTable[Location_Name])

I found the LOOKUPVALUE function in DAX, but unless I'm missing something, it only seems capable of dealing with a single column. To be honest, I found the above formula from another forum, so I don't have a deep enough understanding to try and rebuild it DAX.

So, in more plain terms, I'm trying to achieve something along the lines of:

= minimum of array1;

array1 = ((Table1[X]-RefTable[X])^2 +(Table1[Y]-RefTable[Y])^2) ... for every row of RefTable

I'd assume getting to the point where I can return the min of array1 would teach me enough to then use that min to look up the corresponding location name in the RefTable

1 ACCEPTED SOLUTION

Accepted Solutions
sturlaws New Contributor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

Could you try this code to create a calculated column?

``````column =
VAR _curX =
CALCULATE ( SELECTEDVALUE ( 'Table'[X] ) )
VAR _curY =
CALCULATE ( SELECTEDVALUE ( 'Table'[Y] ) )
VAR _tmp =
refTab;
"distance"; ( _curX - CALCULATE ( SELECTEDVALUE ( refTab[x] ) ) ) ^ 2
+ ( _curY - CALCULATE ( SELECTEDVALUE ( refTab[y] ) ) ) ^ 2
)
VAR _min =
MINX ( _tmp; [distance] )
VAR _final =
SUMMARIZE ( FILTER ( _tmp; [distance] = _min ); refTab[loc] )
RETURN
MINX ( _final; [loc] )``````

Since you have not posted any sample data, I have not been able to test it thoroughly, but it works on my very simple mockup dataset

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

5 REPLIES 5
Nathaniel_C Super Contributor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

Hi @Braunnz ,
Your best bet is to copy and paste what you have and what you want right into the post. You can dummy up your data in Excel, or share your pbix with us.

Thank you,

Nathaniel

sturlaws New Contributor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

Could you try this code to create a calculated column?

``````column =
VAR _curX =
CALCULATE ( SELECTEDVALUE ( 'Table'[X] ) )
VAR _curY =
CALCULATE ( SELECTEDVALUE ( 'Table'[Y] ) )
VAR _tmp =
refTab;
"distance"; ( _curX - CALCULATE ( SELECTEDVALUE ( refTab[x] ) ) ) ^ 2
+ ( _curY - CALCULATE ( SELECTEDVALUE ( refTab[y] ) ) ) ^ 2
)
VAR _min =
MINX ( _tmp; [distance] )
VAR _final =
SUMMARIZE ( FILTER ( _tmp; [distance] = _min ); refTab[loc] )
RETURN
MINX ( _final; [loc] )``````

Since you have not posted any sample data, I have not been able to test it thoroughly, but it works on my very simple mockup dataset

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Braunnz Frequent Visitor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

@sturlaws Thank you so much for the reply, and a working solution! The way you create the temporary variables as you go was something I didn't have a clue about; I definitely feel a little more grounded in how to do more complex calculations now. And don't worry, I swear to make this my only data-less post.

Braunnz Frequent Visitor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

@Nathaniel_C  Thank you for the useful link -- I will be sure to include data moving forward.

Highlighted
Nathaniel_C Super Contributor

## Re: Replicating Excel formula in DAX - use lookup table of XY coords vs. XY columns to assign row

Hi @Braunnz ,

You are welcome!

Nathaniel

Announcements Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 199 members 2,545 guests
Recent signins:
• jvanegmond • Maruti_K • vishanshul • Fabian89 • nandukrishnavs • thijsleufkens • rba1981 • MvBeers • thanatji • gunjan8421 • russtak • ciaranfkeane • bei • ethanlee 