Lookup Percentil Value in difrent Tables

Hi Anonymous 

For your calculated column in Table1 (Table Tests), extracting the percentile from Table2 (Tables Scores). You might have to tweak it a bit to get exactly what you need. Note the code checks whether the scorevalues are increasing or decreasing as the percentile increases, so that it can perform the correct operation. 

Extracted_Percentile = 
VAR relevantT_ = FILTER(Table2;Table2[AgeCategory]=Table1[AgeCategory] && Table2[Position] = Table1[Position] && Table2[Gender] = Table1[Gender] && Table2[Protocol]= Table1[Protocol])

VAR valueat100_ = MINX(FILTER(relevantT_;Table2[ScorePercent] = 100);Table2[ScoreValue])
VAR valueat5_ =  MINX(FILTER(relevantT_;Table2[ScorePercent] = 5);Table2[ScoreValue])
VAR orderIsdescending_ = valueat100_ > valueat5_  
VAR percentile_ = 
IF(orderIsdescending_;
    MAXX( FILTER(relevantT_; Table1[Test Result] >= Table2[ScoreValue]);Table2[ScorePercent]);
    MINX( FILTER(relevantT_; Table1[Test Result] >= Table2[ScoreValue]);Table2[ScorePercent]))
RETURN 
percentile_

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers