Lookup value within number range

[Hong_HW]

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 · Accepted solution]

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]
)

[Hong_HW]

It helps, thank you very much