DAX to map values for range of numbers

[Vijay08V]

Hi,

I need some help in writing a DAX to map the values based on range of number. I have a main table A with Ranks and another table B with Rating and rank. Below are the sample tables. Here the required output is rank 1 should be mapped as "Far Exceed". Between Rank 2-3 , it should be mapped as "Exceed" and between rank 4-9 should be mapped as "Meet". In Excel its simple using the Vlookup function with approximate match. Any help how to do this in Power BI ?

Table A :    

Rank	Rating
1	Far Exceed
2	Exceed
3	Exceed
4	Meet
5	Meet
6	Meet
7	Meet
8	Meet
9	Meet
10	Doesnot

Table B :

RankRating

1	Far Exceed
2	Exceed
4	Meet
10	Doesnot

[v-tianyich-msft]

Hi @Vijay08V ,

You can simply use the minimum value aggregation that comes with Power BI and you can check the results as follows:

Then I updated the data:

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Vijay08V]

Hi @Sahir_Maharaj ,

Thanks for your response but here the table B values will not be static. It would change. Table B values change based on the Headcount value. Say for example if the HC is 58 then the ranking would as below table. In excel either using lookup or vlookup with approximate match I was able to do it but not sure how to do that in Power Bi.

[Sahir_Maharaj]

Hello @Vijay08V,

Can you please try this DAX:

Mapped Rating = 
VAR CurrentRank = 'Table A'[Rank]
RETURN
    SWITCH(TRUE(),
        CurrentRank = 1, "Far Exceed",
        CurrentRank >= 2 && CurrentRank <= 3, "Exceed",
        CurrentRank >= 4 && CurrentRank <= 9, "Meet",
        CurrentRank = 10, "Doesnot",
        BLANK()
    )