Forum Discussion

mmace1's avatar
mmace1
Icon for Impactful Individual rankImpactful Individual
8 years ago
Solved

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?  

 

 

 

 

3 Replies

    • mmace1's avatar
      mmace1
      Icon for Impactful Individual rankImpactful 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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion

        Hi mmace1

         

        See the first example in the article i mentioned above

         

        The Sales table contains a number of transactions:

         

        CustomerProductDateQuantity
        MarcoMouse1/20/20172
        MarcoTablet2/16/20171
        AlbertoMouse1/30/20171
        AlbertoTablet1/30/20171
        AlbertoWatch2/23/20171

         

         

        The Promo table contains a number of promotions, with a primary key that corresponds to Month and Product.

         

        MonthProductCampaignMedia
        1MouseBundleRadio
        1TabletBundleBanner
        1WatchTwo-for-oneNewsletter
        2MouseSaleMagazine
        2WatchSaleNewsletter

         

        The article gives the formulas how related Campaign and Media values are pulled in the Sales Table using LookUp Value