Forum Discussion
General Q: What's the difference - LOOKUPVALUE vs connecting two tables and using RELATED?
What's the reason for having both of these similar tools? Say in Excel we have VLOOKUP and INDEX-MATCH. But that's (I imagine) due to Excel's age - one tool came first, then much later a better one came out, but we'll keep the old tool because it's well established.
Power BI isn't that old though.
I'm comfortable using both, but all I can think of is:
LOOKUPVALUE is more dynamic - you could say, pull data from a column in Table 1 into a column in Table 2, using say columns 1A and 2A as the connection. Then you coudl pull different data from Table 1, but this time using column 1C and 2D as the connection.
On the other hand, if you're always going to be using the same 2 columns to make the connection between 2 tables anyway (which is typical), then connecting the tables, then using RELATED repeatedly, is a bit faster than typing out everything for LOOKUPVALUE.
Is there more to why we have both of these similar tools?
HI mmace1
Sometimes relationship cannot be established between 2 tables. So LOOKUPVALUE is inevitable.
You may find the following article by Italian Maestros quite useful
https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/
Do you know, you can even do LOOKUP within the SAME TABLE:smileywink: See the pic below.
3 Replies
- Zubair_Muhammad
Community Champion
HI mmace1
Sometimes relationship cannot be established between 2 tables. So LOOKUPVALUE is inevitable.
You may find the following article by Italian Maestros quite useful
https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/
Do you know, you can even do LOOKUP within the SAME TABLE:smileywink: See the pic below.
- mmace1
Impactful Individual
"Sometimes relationship cannot be established between 2 tables. So LOOKUPVALUE is inevitable."
Can you describe a scenario in which a relationship couldn't be established, but LOOKUPVALUE would still work?
Thanks!
- Zubair_Muhammad
Community Champion
Hi mmace1
See the first example in the article i mentioned above
The Sales table contains a number of transactions:
Customer Product Date Quantity Marco Mouse 1/20/2017 2 Marco Tablet 2/16/2017 1 Alberto Mouse 1/30/2017 1 Alberto Tablet 1/30/2017 1 Alberto Watch 2/23/2017 1 The Promo table contains a number of promotions, with a primary key that corresponds to Month and Product.
Month Product Campaign Media 1 Mouse Bundle Radio 1 Tablet Bundle Banner 1 Watch Two-for-one Newsletter 2 Mouse Sale Magazine 2 Watch Sale Newsletter The article gives the formulas how related Campaign and Media values are pulled in the Sales Table using LookUp Value