Forum Discussion

ahoeter's avatar
ahoeter
Regular Visitor
4 years ago

DAX Measure Based on Group and Range

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

 

DAX
multiple
range

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      ahoeter
      Regular Visitor
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        ahoeter 

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