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]

21 Comments

  2. what is lookup, what is the use how it works i need clarity with examples

  4. 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.

  5. how can i look up last quantity sold of a product in price table from sales table?

  8. 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

  9. 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

  10. 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?

  11. And if there are multiple values meeting the criterias, what should happen? Do you want to summarize them, count them, average them?…

  12. 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

  14. 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?

  17. 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],””)

  18. 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?

  19. 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.

  20. 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

  21. Hi , I need to know how do do a vlook from text columns. i have 2 tables in BI both in text format

Comments are closed.