Vlookup in PowerPivot PowerBI

When you just start working with PowerPivot or PowerBI this feature (performing vlookup) just jumps right off the bat and makes it a very compelling case for Excel users to shift to the BI tools.

In most cases this change is permanent!

But these tools are not just made for common lookups they can do a lot lot more things that you can’t even imagine doing in the excel. At-least not with ease

In this post, I’ll explain

  1. How can you perform a VLookup in PowerPivot / PowerBI
  2. Practical use cases – when and when not to do it

Let’s Begin

 

Consider these 2 Tables

Pivot table questions 1

 

Pivot table questions 2

For answering any of these questions, you’ll have to apply a bunch of Vlookups between Sales and Products table

  • Total Sales by Year and Months
  • Total Sales by Product Type
  • Total Sales by Category

 

Instead of going the conventional way let’s solve this using PowerPivot

 

Vlookup in PowerPivot / PowerBI

Since I wanted to explain a few nitty gritties I recorded a quick video. You’ll learn how to create relationships between tables and how to write the RELATED Function. Both of these are equivalent of writing a Vlookup in Excel

 

DOWNLOAD THE EXCEL FILE USED

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI