Next Youngsday








Home / Tech / Apps and software / Entrepreneur Toolkit: PowerPivot – Creating Relationships

Entrepreneur Toolkit: PowerPivot – Creating Relationships

In my previous article   I have introduced Microsoft BI tool named PowerPivot.

To begin with, the main advantages that PowerPivot provide are:

  1. Ease of Use (with DAX – Data Analysis Expressions formula)
  2. Relational Data (Relational data model based on tables, columns and relationships)
  3. Pivot Tables

DAX formulas is another topic to be described in more than 2 articles. So let’s skip that part for now.


I’m assuming that most of you already know the formulas that are used to link two different columns from same/different tables. The first one that comes to your mind is probably VLOOKUP and HLOOKUP functions.


Here is the good news:

In PowerPivot, you don’t need to get all the columns from the table array (meaning col_index num) one by one. Once you create a relationship between two tables according to one common column, you can use it for as many columns as you want.

Creating Relationship?

  1. A relationship is a linkage between two data sources that establish how data should be correlated together in PowerPivot
  2. Relationships join data together from unrelated sources.
  3. Some relationships are created automatically in data imports while others require manual creation in PowerPivot.
If you are familiar with SQL, you can consider relationships as in JOIN functions in a way.
Please see the illustrative below:

powerpivot youngsday

In this illustrative, the objective is to match customers with the relevant sales data. Instead of writing many vlookup formulas next to each other, you can just create a relationship between these two tables, using “name column” as lookup value in both tables. Please see the illustrative below:

powerpivot youngsday2

Once you click “create relationship” in Powerpivot Window > Design, you will have insert your table, column (to be matched), related lookup table, related lookup column. So for the previous example;

Table: Customer table  –  Column: Customer table[name]  –  Related Lookup Table: Sales Data – Related Lookup Column: Sales Data[name]

Most probably you will be working with multiple tables and multiple columns, which means you can easily get lost within the same document. Or, you might need to change or delete the relationships between tables. Here is what you need to do:

powerpivot youngsday3

If you find it hard to read from a list of tables, you can also view your relationships from “diagram view”, as below:

powerpivot youngsday4

In my next article, I will explain DAX formulas in detail, which also contains RELATED function for using these relationships.


Image source: Flickr


Orkun Basaran

Leave a Comment

Your email address will not be published. Required fields are marked *


* Copy This Password *

* Type Or Paste Password Here *

Scroll To Top
Sign up for our Newsletter to keep updated for

Enter your email and stay on top of things,

Youngsday on Twitter!
Follow us on Twitter!