Lookup closest value to a number

[frankhofmans]

hi PBI experts,

i have the following questions:

i have 2 tables, one with all employee ID and their weekly salary, and one table with the amount of taxes that we have to withheld from the salary.

Table 1 (simplified):

Employee ID	Salary
111	800
112	1100
113	600
114	1300
115	1800
116	3000
117	1600
118	700
Etc	Etc

Table 2 (simplified):

Salary	Taxes
0	0
450	50
650	80
850	110
1050	130
1250	170
1650	220
2050	260
3050	380

I need the following output in the employee table:

Employee ID	Salary	Taxes
111	800	80
112	1100	130
113	600	50
114	1300	170
115	1800	220
116	3000	260
117	1600	170
118	700	80

So i need the closest lower number. Does anyone know how to solve this? it is a large table, so an if(and(salary>=xx,salary<=xx) is no option.

Thanks in advance!

Regards,

Frank

[d_gosbell · Accepted solution]

---

@frankhofmans wrote:

it is a large table, so an if(and(salary>=xx,salary<=xx) is no option.

---

I'm curious as to why this is not an option? A between filter like that would probably be the most efficient sort of scan compared to alternative approaches. What do you mean by the table being large - how many rows are in the employee and tax tables?

I can think of two possible solutions that would look up the lowest value.

You could do it in DAX using a calculated column in your employee table. You would find the max salary value in the tax rates table that is less than the salary for the current employee row, then use lookupvalue to get the tax for that lower salary range.

Tax = 
var salary = Employee[Salary]
var lowerRange = maxx(filter(all('Tax Rates'[salary]), 'Tax Rates'[Salary] < salary), 'Tax Rates'[Salary])
return LOOKUPVALUE('Tax Rates'[Taxes], 'Tax Rates'[Salary] , lowerRange)

Or you could use similar logic in Power Query by adding a custom column and putting the following expression in

Table.SelectRows(#"Tax Rates", (taxrow) => taxrow[Salary] = List.Max(List.Select(#"Tax Rates"[Salary], (salary) =>  salary < [Salary] ))){0}[Taxes]

[frankhofmans]

hi D_Gosbell,

Thanks for your reply. The employee table is a table with around 100k rows, the taxes table has around 2k rows. Option 1 works perfect for me. Many thanks!

Regards,

Frank