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

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

teepee3234 — Tue, 10 Jan 2023 02:02:11 GMT

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!

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

amitchandak — Tue, 10 Jan 2023 03:34:56 GMT

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

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

Anonymous — Wed, 11 Jan 2023 05:38:29 GMT

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