Forum Discussion

Hong_HW's avatar
Hong_HW
Frequent Visitor
3 years ago
Solved

Lookup value within number range

Hi,

I would like to seek your help. I have 2 tables as per below.

 

Table 1 is the reference table with Bonus Range

 

Table 2 is the exact sales amount

 

I want to insert a column in Table 2 to indicate the Bonus range. Appreciate it if there are any DAX formulas that could help.

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    One of ways to achieve this is to create a bonus range table like below.

    I tried to create a sample pibx file, and please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your dataset.

     

     

     

     

    Bonus range CC =
MAXX (
    FILTER (
        BonusRange,
        Sales[Sales amount] > BonusRange[min]
            && Sales[Sales amount] <= BonusRange[max]
    ),
    BonusRange[Bonus range]
)

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    One of ways to achieve this is to create a bonus range table like below.

    I tried to create a sample pibx file, and please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your dataset.

     

     

     

     

    Bonus range CC =
MAXX (
    FILTER (
        BonusRange,
        Sales[Sales amount] > BonusRange[min]
            && Sales[Sales amount] <= BonusRange[max]
    ),
    BonusRange[Bonus range]
)