Forum Discussion

ecky12's avatar
ecky12
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 list of Grades associated with a range of scores (GradeMapping.LowScore,GradeMapping.HighScore,GradeMapping.LetterGrade)

 

I want to calculate the average value of the scores in AllGrades.score (which I am already displaying in my report) and lookup that value in GradeMapping so I can display the associated letter grade on my report.

 

Can someone help me with that? I am having all sorts of problems...and I pretty sure this should be simple.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Can you please post some sample data with expected result set? That will be easiier to understand.

     

    Thanks
    Raj

  • v-frfei-msft's avatar
    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" )
    )

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi ecky12,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,
      Frank