How to create a grade scale for each row with different grading ranges in DAX?

[teepee3234]

Hi,

I'm relatively new to this and I'm trying to figure out if I can do this formula in DAX and configure it onto my dashboard. For each row in my table, I am trying to incorporate a grading system between 1-5 based on what grade a certain program has gotten (1 < 10, 2 is 11-20, 3 is 21-30, etc). I could do conditional formatting for this row, but it would apply to all rows and not be sufficient as every program has different grading ranges. I have over 1,000+ plus programs with different grading ranges and trying to find the best method.

For example,

Program A could have a grading scale of

1 (<10)

2 (11-20)

3 (21-30)

4 (31-40)

5 (41-50)

But Program B could have a grading scale of

1 (<15)

2 (16-20)

3 (21-25)

4 (26-31)

5 (32-35)

Is there a way I could format each row with its own set of ranges without formatting all the other rows? How do I even approach this? Thank you so much!

[Anonymous · Accepted solution]

Hi  @teepee3234 ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
IF(
   'Table'[Program]="ProgramA" ,
   SWITCH(
       TRUE(),
       'Table'[Grade]<10,1,
       'Table'[Grade]>=11&&'Table'[Grade]<=20,2,
       'Table'[Grade]>=21&&'Table'[Grade]<=30,3,
       'Table'[Grade]>=31&&'Table'[Grade]<=40,4,
       'Table'[Grade]>=41&&'Table'[Grade]<=50,5),
 SWITCH(
       TRUE(),
       'Table'[Grade]<15,1,
       'Table'[Grade]>=16&&'Table'[Grade]<=20,2,
       'Table'[Grade]>=21&&'Table'[Grade]<=25,3,
       'Table'[Grade]>=26&&'Table'[Grade]<31,4,
       'Table'[Grade]>=32&&'Table'[Grade]<=35,5))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@teepee3234 , You can a new column like

Switch ( [Program],
"A" ,Switch ( True() ,
[Scale] <10, "1",
[Scale] <20, "2",
[Scale] <30, "3",
[Scale] <40, "4",
[Scale] <50, "5"),
"B" ,Switch ( True() ,
[Scale] <15, "1",
[Scale] <20, "2",
[Scale] <25, "3",
[Scale] <31, "4",
[Scale] <35, "5")
)

Change as per need