cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Braunnz Frequent Visitor
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

Thanks in advance for any advice!

1 ACCEPTED SOLUTION

Accepted Solutions
sturlaws New Contributor
New Contributor

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

@Braunnz,

 

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 =
    ADDCOLUMNS (
        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.

View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
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

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

sturlaws New Contributor
New Contributor

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

@Braunnz,

 

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 =
    ADDCOLUMNS (
        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.

View solution in original post

Braunnz Frequent Visitor
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
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
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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

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.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 199 members 2,545 guests
Please welcome our newest community members: