topic Re: Lookup value within number range in DAX Commands and Tips

Lookup value within number range

Hong_HW — Mon, 30 Jan 2023 04:25:34 GMT

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.

Re: Lookup value within number range

Jihwan_Kim — Mon, 30 Jan 2023 04:43:18 GMT

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

Re: Lookup value within number range

Hong_HW — Mon, 30 Jan 2023 06:16:45 GMT

It helps, thank you very much