Forum Discussion
Indirect lookupvalue different sheets
Hello PBI experts,
i have a working file:
| Salary | Table | Column |
| 532 | Table 1 | Column 3 |
| 157 | Table 3 | Column 7 |
| 589 | Table 13 | Column 4 |
| 195 | Table 7 | Column 5 |
| 195 | Table 3 | Column 4 |
| 430 | Table 4 | Column 1 |
| Etc |
And i have 20 other sheets with the different tax tables (al 20 sheets have the same layout and column structure, but different taxes based on country, age, salary etc.):
Table 1:
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 | Column 7 |
| 100,3 | 10,3 | 11,5 | 11,7 | 12,1 | 13,5 | 14,1 |
| 201,5 | 20,1 | 22,5 | 22,8 | 23,5 | 25,1 | 27,1 |
| 307,6 | Etc | |||||
| 415,8 | ||||||
| 520,9 | 52,9 | |||||
| 750,1 | ||||||
| 950,1 |
Table 2: etc
In the first row of the main table i want to search for the value in table 1 and column 3 (52,9, because 520,9 is het first earlier row that i look for). In the second row and want the value in table 3 column 7.
In excel, i can just create a vlookup formule with a "indirect" function and the column number. In powerBi i can only create this with 120 (20 sheets and 6 columns from 2 -7) if formulas: if(and(table = table 1, column = column 3), lookupvalue(table 1[column3],table 1[column1],main table[salary]), etc, etc, etc.
Does anyone have a more simple formule to solve this?
Many thanks in advance,
Regards,
Frank
1 Reply
- AnonymousNot applicable
Hi frankhofmans ,
You can try to use lookupvalue in Power BI.
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
Returns the value for the row that meets all criteria specified by one or more search conditions.
You can also use Switch instead of if.
SWITCH function (DAX) - DAX | Microsoft Learn
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.