Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 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]
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
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |