IF ELSE alternative solution

[rvita0101]

Hi, 

I need help with the best way to approach this requirement. Technically I could write a long nested SWITCH and IF statements but it becomes clunky and difficult to maintain. I would really appreciate it if someone could point me to a more efficient approach. 

I have a table with the list of number sold products in different categories. I need to run a report showing sales ratings (e.g. "High","Good","Average","Slow") for each category based on the number of products sold and the criteria for that category. 

Table 1: Product Sold

Product	Category	Sold
A	Category 1	10
B	Category 1	6
C	Category 1	5
D	Category 2	15
E	Category 2	20
F	Category 3	1
G	Category 3	2
H	Category 3	1

Sale Rating Criteria (this is the criteria, shown here in a table format but I don't have a table in PowerBI)

Category	High	Good	Average	Slow
Category 1	>15	10-15	9-5	4-0
Category 2	>40	39-30	29-20	19-0
Category 3	>5	5-4	3-2	1-0

The report needs to be like this

Category	Sale Count	Sale Rating
Category 1	21	High
Category 2	35	Good
Category 3	4	Good

What I initially tried but didn't want to pursue further.

1. Create a visual table that show Sale Count by Category. 
2. Create a New Calculation as below

Sale Rating = SWITCH(

    True(),

    [Category] == "Category 1", IF ([Sale Count] > 15), "High", IF ([Sale Count] >= 10), Good, .....

   [Category] == "Category 2", IF ([Sale Count] > 40), "High", IF ([Sale Count] >= 30), Good, .....

   [Category] == "Category 2", IF ([Sale Count] > 5), "High", IF ([Sale Count] >= 4), Good, .....

)

[rvita0101]

Thank you Vicky for the quick response. 

[vicky_ · Accepted solution]

I would create something like a lookup table, and then write a measure to calculate the corresponding sales rating:

The table i created based on your sample data looks something like the above. I left the lower bounds empty for the High sales rating and just adjusted my DAX to handle that condition. Feel free to adjust as you need.

Sales Rating = CALCULATE(MAX('Criteria Table'[Attribute]), FILTER('Criteria Table', SUM('Products Sold'[Sold]) >= COALESCE('Criteria Table'[Lower], 999999) && SUM('Products Sold'[Sold]) <= 'Criteria Table'[Upper]))