finding the nearest

[fransbus]

Although I have worked with "Gemini" ages ago I consider myself new to Powerpivot. I am looking for assistance on this question:
I have two unrelated tables like this:

Table1:
Index    Year    Score
    1    2009    10
    2    2009    12
    3    2009    14
    4    2009    16
    5    2009    18
    6    2010    14
    7    2010    16
    8    2010    18
    9    2010    20
   10    2010    22

Table2:
Year    Score    Min    Index
2009    8        2        1
2009    17       1       16 or 18
2010    8        6        6
2010    18       0        8
2010    26       4       10

Min and Index in Table2 are Calc columns. What I need: for each row (Year/Score) in Table 2 I want to get Index from Table 1 where Table2.Score is closest to Table1.Score, and Table2.Year = Table1.Year.

This is easy in Excel using an array formula with Index, Match and If, but I can't find the formula for Index in Powerpivot. Any ideas? Thx.

Note: the formula for Min could be something like
=MINX(VALUES(Table1[Score]); ABS(Table1[Score] - Table4[Score]))
But it's not Min I need. I need Index.

- Frans

 

[fransbus]

Solved it. Used 4 calc columns.
Table1 above is now tblScores, Table2 is now tblNewScores.

Calc formula SmallestDistanceToScore:
=MINX( FILTER(tblScores; tblScores[Year] = tblNewScores[Year]); ABS(tblScores[Score] - tblNewScores[Score]))

Calc formula Index1:
=MINX( FILTER(tblScores; tblScores[Year] = tblNewScores[Year] && ABS(tblScores[Score] - tblNewScores[Score]) = tblNewScores[SmallestDistanceToScore]); tblScores[Index])

Calc formula CountMatches:
=COUNTX( FILTER(tblScores; tblScores[Year] = tblNewScores[Year] && ABS(tblScores[Score] - tblNewScores[Score]) = tblNewScores[SmallestDistanceToScore]); tblScores[Index])

Calc formula Index2:
=IF([CountMatches]=1; BLANK(); MAXX( FILTER(tblScores; tblScores[Year] = tblNewScores[Year] && ABS(tblScores[Score] - tblNewScores[Score]) = tblNewScores[SmallestDistanceToScore]); tblScores[Index]))

Regards,
- Frans