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

[Braunnz]

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!

[sturlaws · Accepted solution]

@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.

[Braunnz]

@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. 

[Nathaniel_C]

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

[Braunnz]

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

[Nathaniel_C]

Hi @Braunnz ,

You are welcome!

Nathaniel