LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
This article is about LOOKUPVALUE function, which assigns values from another table. There doesn’t´t have to be a relationship between these tables.
How to use it?
In this example we need to connect data from these two tables. One of them contains info about sales, but we miss the prices of items. The prices are in the second table and we will use the LOOKUPVALUE to assign them.
So we need to add the proper price from Pricelist table to every row in Sales table..
Let´s type this into a new column:
- Price of item = LOOKUPVALUE(
Pricelist[Price per unit];
Pricelist[Item name];
Sales[Item];
123)
Because:
- Price from pricelist is a name of new column
- Pricelist[Price] is a column in the “second” table, that contains the values we need
- Pricelist[Item] is the column in a “second” table, containing the “key”.
- Sales[Item]) is the column in the same table we are typing, that contains the key.
- 0 is an optional parameter. Declares the result for situations, where no equal value can be found – something like IFERROR.
The prices are added:
Few notes:
- Its principle is very similar to VLOOKUP (but LOOKUPVALUE works in DAX, when VLOOKUP works in common Excel).
- Also RELATED from DAX is has similar logic. It si easier then LOOKUPVALUE to create, but needs a relation between tables.
- If LOOKUPVALUE finds multiple relevant values to assign, it generates error.
- LOOKUPVALUE can use multiple columns as a key. For example if you need to assign prices based on the combination of Month and Item, it works like this.
- There the key in the table with results is not unique, you get an error by default. To get any value in this case, you may use something like this instad of LOOKUVALUE:
- CALCULATE(
MIN(resulttable[resultcolumn]),
FILTER(resulttable,resulttable[keycolumn]=thistable[keycolumn]
This article is about LOOKUPVALUE function, which assigns values from another table. There doesn't´t have to be a relationship between these tables.
How to use it?
In this example we need to connect data from these two tables. One of them contains info about sales, but we miss the prices of items. The prices are in the second table and we will use the LOOKUPVALUE to assign them.
So we need to add the proper price from Pricelist table to every row in Sales table..
Let´s type this into a new column:
- Price of item = LOOKUPVALUE(
Pricelist[Price per unit];
Pricelist[Item name];
Sales[Item];
123)
Because:
- Price from pricelist is a name of new column
- Pricelist[Price] is a column in the "second" table, that contains the values we need
- Pricelist[Item] is the column in a "second" table, containing the "key".
- Sales[Item]) is the column in the same table we are typing, that contains the key.
- 0 is an optional parameter. Declares the result for situations, where no equal value can be found - something like IFERROR.
The prices are added:
Few notes:
- Its principle is very similar to VLOOKUP (but LOOKUPVALUE works in DAX, when VLOOKUP works in common Excel).
- Also RELATED from DAX is has similar logic. It si easier then LOOKUPVALUE to create, but needs a relation between tables.
- If LOOKUPVALUE finds multiple relevant values to assign, it generates error.
- LOOKUPVALUE can use multiple columns as a key. For example if you need to assign prices based on the combination of Month and Item, it works like this.
- There the key in the table with results is not unique, you get an error by default. To get any value in this case, you may use something like this instad of LOOKUVALUE:
- CALCULATE(
MIN(resulttable[resultcolumn]),
FILTER(resulttable,resulttable[keycolumn]=thistable[keycolumn]
Good, but what if you have 2 or 3 prices for every item?
what is lookup, what is the use how it works i need clarity with examples
what is the difference between lookup and Related
Related needs you have create a relationship (one to more) between the two tables you are considering.
Lookupvalues instead allows you to do the same without create a relationship.
how can i look up last quantity sold of a product in price table from sales table?
Can you use LOOKUPVALUE for text values
Yes, why not?
Need your help in doing this. I have 2 tables and i want Actuals against budget in the budget table, and Budget against actuals on the actual table using LOOKUPVALUE
CCC Nominal Month Budget
50001 88033 01/04/2020 100
50002 88034 01/04/2020 200
CCC Nominal Month Actuals
50001 88033 01/04/2020 200
50002 88034 01/04/2020 200
It depends. If you want to assign values based on CCC only, then the syntax is:
=lookupvalue(budgets[Budget];
budgets[CCC];
actuals[CCC])
This creates a calculated column in Actuals, showing relevant Budget value.
If you want to use all calumns for assigning, then syntax is like this:
=lookupvalue(budgets[Budget];
budgets[CCC];
actuals[CCC];
budgets[Nominal];
actuals[Nominal];
budgets[Month];
actuals[Month])
Look at this:
https://exceltown.com/wp-content/uploads/lookupvalue-2.png
Hi, Thanks for the solution. However it work on 2 rows only, i have 80k lines and this show me an error – A table of multiple values was supplied where a single value was expected . Does more rows with multiple dates, nominal and CCC will not work?
And if there are multiple values meeting the criterias, what should happen? Do you want to summarize them, count them, average them?…
I have a calendar year starting from Apr and ending Mar – Can some one help in writing a DAX – attached is the table and expected results
FY Calendar (Table)
Month FY Calendar
1/4/19 2018-19
1/5/19 2018-19
1/6/19 2018-19
1/7/19 2018-19
1/8/19 2018-19
1/9/19 2018-19
1/10/19 2018-19
1/11/19 2018-19
1/12/19 2018-19
1/1/20 2018-19
1/2/20 2018-19
1/3/20 2018-19
Cost table
Month Cost YTD
1/4/19 100 100
1/5/19 100 200
1/6/19 100 300
1/7/19 100 400
1/8/19 100 500
1/9/19 100 600
1/10/19 100 700
1/11/19 100 800
1/12/19 100 900
1/1/20 100 1000
1/2/20 100 1100
1/3/20 100 1200
I am not sure I understand correctly. But can´t TOTALYTD help you?
https://exceltown.com/navody/power-bi/dax-dotazovaci-jazyk-pro-power-pivot/time-ingelligence-funkce-dax/totalytd-totalqtd-totalmtd-dax-powerpivot-power-bi/
Hi, I need help with the lookupvalue. I am solving similar task as the second example with the pricelist with candies. Just the pricelist is not based on months but the price is valid in time interval from-to (two columns) and I need to fill the approprite price valid at the date of selling the item to the second table. Please what would be the syntax then?
Maybe this can be helpful:
https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/power-query-and-assigning-to-ranges-merging-of-tables-like-vlookup-with-last-argument-1-true/
Is there a way to attach files
Actual $ 50 Budget $ 100 and variance $ 50 and % variance is 50% – working fine with below formula
Actual $100 Budget $0 and variance $100, ideally the % diff should be 100% but i am getting no values
Formula used
DAX formula % Var Cost = DIVIDE([Var_Cost],[Bud_Cost],””)
its not same with excel vlookup, because it doesnt bring the first value that it found. If the table has lot of same values lookupvalue doesnt bring the first value . its a problem about it, and no solution I guess, right?
Yes it works like excel, i reply myself. but why it says “While waiting for a single value, a table of multiple values was provided” when i do lookupvalue. I concatenate mont()&year() of date , and usermail for both table and then I set up lookupvalue but it doesnt work.
But if I concatenate just date and usermail, it works. there are multiple similar items on both table by the way.
How to allocate Total Amount till its 0
Decending Order
EX;
Total Paid : $500
Invoice :
Jan 1 INV00001 $100
Jan 6 INV 000025 $200
Feb 8 INV000058 $400
So want to apply 500 Dollars to INV 000058 & 25
Want result as
Total Paid : $500
Jan 6 INV 000025 $100
Feb 8 INV000058 $400
Hi , I need to know how do do a vlook from text columns. i have 2 tables in BI both in text format