Calculate Vlookp "TRUE" with DAX

[daveinitiv]

Hi all,

is there a way to perform a VLOOKP "TRUE" calculation in DAX?

I want to solve the following problem. A list of salaries should be classified in the right tax bracket to assign the correct tax rate.

Bracket	Tax Rate
0-20.000	0%
20.000-40.000	25%
40.000-60.000	35%
60.000-80.000	45%
> 80.000	50%

An employee with a salary of 50.000 should be assigned 35%, for example.

How would I do this in DAX? 

Many thanks for your help!

BR

David

[CNENFRNL · Accepted solution]

Hi, @daveinitiv , the answer to your question in such a case, is definitely YES!

A very small reshape of rate hierarchy helps do the trick.

Then it's a easy job to leverage DAX to achieve the goal. You might refer to the attach file for more details.

VLOOKUP TRUE = MINX( FILTER(Rates, Rates[UpperBound] > MAX( Emp[Salary] ) ), Rates[Tax Rate] )

Btw, Power Query is able to do the trick easily.

[MattAllington]

There are various methods. One is to use the banding technique I cover here 

https://exceleratorbi.com.au/banding-in-dax/

My demo groups ages into bands. You need to select a tax rate based on a salary - it's the same principle.