DAX Measure Based on Group and Range

[ahoeter]

Hello, 

I am attempting to write a DAX expression to assist in assigning a 1-5 value based on the performance of a representative, with 1 being "Far from Met" and 5 being "Significantly Surpassed." There are three different groupings: Alpha, Bravo, and Charlie.

For example:

So what I am looking to accomplish is assigning a 1 to 5 value based on the groupings illustrated above in one visual. 

I thought of this: IF('sheet name [Group] = "Alpha" && 'sheet name[Range] <425, 5 and so on for 4, 3, 2, 1 and repeat for the other two groups, however it's not working the way I thought it would.

Any assistance would be greatly appreciated - if you have any additional questions, please let me know. 

Thanks, 

Andrew

[tamerj1]

@ahoeter 

You need to combine the 3 lookup tables into one adding a column [Group]. On the other hand, the range column shall be splitted into two [Lower Range] and [Upper Range] integer data type columns. You can do that comfortably using power query or DAX. 
the new dimension table can then be linked with fact table via the [Group] columns from both tables. It would be a many to many relationship. If this is ok with you then I can guide you through the next step which is creating the measure. 

[ahoeter]

@tamerj1 

Thank you for the response. 

I would like to see how the measure can be created. 

Additionally, if we are getting the data from a singular Excel table with a VLOOKUP for the grouping, would we still have to combine the three lookup table as you indicated in your initial reply?

Thanks!

[tamerj1]

@ahoeter 

I cannot answer this unless I know how does your fact table look like?

[ahoeter]

Sorry - corrected visual attached.