Lookup with Measure

[gill_thornton]

Hi

I work in a school and am creating some headline measures for my post-16 cohort.  All grades have point values attached to them, e.g; A* = 60; A = 50 etc.  I have a calculated overall measure for average point score.  I now need to show this as a grade based of DfE technical guidelines sample below:

APS Band Min	APS Band Max	Grade
58.34	60	A*
55	58.33	A*-
51.67	54.99	A+
48.34	51.66	A
45	48.33	A-
41.67	44.99	B+
38.34	41.66	B
35	38.33	B-
31.67	34.99	C+
28.34	31.66	C
25	28.33	C-
21.67	24.99	D+
18.34	21.66	D
15	18.33	D-
11.67	14.99	E+
8.34	11.66	E
5	8.33	E-
0	4.99	U

 For example I have an A level APS as a measure of 30.33 - I need to create a measure to show the equivalent grade.  How can I do this?

[MFelix]

Hi @gill_thornton,

Taking into account that you have the table above for your grade you need to do something similar to this:

Grade Value =
MINX (
    FILTER (
        'Grade Table',
        'Grade Table'[APS Band Min] <= [A level APS]
            && 'Grade Table'[APS Band Max] >= [A level APS]
    ),
    'Grade Table'[Grade]
)

[gill_thornton]

Thank you for this solution.  It looks as though it should work perfectly, but when I apply, it is returning a grade A, which is incorrect.  I'm not quite sure where I'm going wrong.

[MFelix · Accepted solution]

Hi @gill_thornton,

I have made a simple model with only the grades table and a metric with a value input, no other tables or relationships.

How are you calculating the metric? is there any relationship between the grades and the table from where you are calculating the metric?

[gill_thornton]

Thank you - it is always relationships!!  I should learn to check and delete any auto-detected on a new table.  Appreciate your help.

[Greg_Deckler]

@gill_thornton Not sure if I understand completely but maybe something like a SWITCH(TRUE(), ...) statement like:

Measure =
  SWITCH(TRUE(),
    [Current Measure] > 45, "A",
    [Current Measure] > 35, "B",
    [Current Measure] > 25, "C",
    [Current Measure] > 15, "D",
    "F"
  )