Forum Discussion

Regular Visitor

7 years ago

Looking up Score Values in another table

I have two tables. AllGrades and GradeMapping AllGrades has scores values of many users who took a survey. The one I care about is AllGrades.Score In the other table (GradeMapping) I have a l...

v-frfei-msft

Community Support

7 years ago

Hi ecky12,

I made one sample for your reference, if it doesn't meet your requirement, kindly share your sample data and excepted result to me.

1. Enter the sample data and create two measures as below.

Ave = AVERAGEX(ALL(Table1),Table1[Score])

Measure = 
VAR LowScore =
    CALCULATE (
        MAX ( Table2[Score] ),
        FILTER ( Table2, Table2[GradeMapping] = "LowScore" )
    )
VAR HighScore =
    CALCULATE (
        MAX ( Table2[Score] ),
        FILTER ( Table2, Table2[GradeMapping] = "HighScore" )
    )
RETURN
    IF (
        [Ave] > HighScore,
        "HighScore",
        IF ( [Ave] > LowScore && [Ave] < HighScore, "LowScore", "N/A" )
    )