How do I return the first value by order related to an ID as a window function?

[Hogan]

What I am working with:

    I have the limitation that my data is sourced from a sharepoint folder and it appends tables from about 24 excel documents for a grand total of 17,000,000 records and about 40 columns. Half these columns are ID columns so they have a large number of distinct values, and they use text in the key values. I know this isn't how things should be done, but this is what I have to work with and I have to figure it out.

    This is why the solution has to be in DAX (preferably calculated columns) because there is no database to fold the query back on, and when I tried this in power query I had to cut off the report at about 45 minuts of refresh time. I need the attributes to all stay in the same relationship to each other that they started in, so MINX() and MAXX() will not work. LOOKUPVALUE() also does not work because it is not feasible to duplicate the ID column, that hurts performance too much and refresh time is already at 30 minutes.  

What I have:     

ID_COLUMN	NAME_COLUMN	REGION_COLUMN
1	Jon	west
2	Bob	west
2	Mary	east
2	Aaron	south
3	Tammy	north
3	Bill	east
4	Judith	south
5	Ally	west
5	Josh	north

What I would like DAX to do:   (See the last two columns)           

ID_COLUMN	NAME_COLUMN	REGION_COLUMN	My_New DAX_Measure	My_New_DAX_Measure_column_2
1	Jon	west	Jon	west
2	Bob	west	Bob	west
2	Mary	east	Bob	west
2	Aaron	south	Bob	west
3	Tammy	north	Tammy	north
3	Bill	east	Tammy	north
4	Judith	south	Judith	south
5	Ally	west	Ally	west
5	Josh	north	Ally	west

I am open to any suggestion that can get this solution.

[Greg_Deckler · Accepted solution]

Well, you will need to define "first" and "last". You could add an Index column in Power Query to do this, then you could use Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

You also might find this amusing - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528

[Hogan]

Thank you Greg, this not only works for this but I can see a broad number of uses for it. It is a really cool DAX trick, using this in a table variable to create a dynamic index based on the initial filter context has me geeking out a little.