Relationship in Power BI with Multiple Columns

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-04-06_00h04_41

You can create relationships in Power BI between tables. Relationships are useful for some functions to work across multiple tables and produce the result. Relationship between tables also makes visualization and report elements more efficient, because result of selection in one chart can affect another chart from different table. However there is a limitation in Power BI relationship that you can’t create relationship based on more than one column. In other words if you want to create relationship (or join two tables) with two or more columns, you cannot! Fortunately there is a work around that I’ll explain in this post. For this post you need to be familiar with Power BI and Power Query, if you are not, read them through Power BI online book.

Defining the Problem

Assume that we have a budget table with fiscal year, fiscal period, and budget amount. Here is an screenshot of this table:

2016-04-05_23h40_53

If I want to do date analysis and date based calculations it is best to create a relationship between the budget table and a date dimension. Here is a view of my date dimension: (Here is an example of creating date dimension with Power Query, and the script for creating date dimension in SQL Server)

2016-04-05_23h50_48

To join these two tables I can add a day column to the budget table, and then join them based on three columns: fiscal year, fiscal period, and day (day of month). So here is the budget table with the new day column added and month value a bit polished to remove “Mth” from the month label;

2016-04-05_23h52_26

Now if I want to create relationship between date dimension and budget table based on these three columns I cannot! The create relationship dialog doesn’t allow me to select multiple columns, and because with a single column a Key field won’t be identified so the relationship can’t be created.

2016-04-05_23h54_35

Workaround

The workaround for this problem is easy. Power BI doesn’t allow relationship in model based on multiple columns, but Power Query can join tables with as many as columns we want. So what I can do as a workaround is to join budget table to date dimension in Power Query and fetch the date key. then I’ll use the date key as a single field relationship in Power BI modelling section.

First I open Merge Queries from the Combine section of Home tab;

2016-04-05_23h56_26

Here is how I join two tables based on multiple columns: I can hold CTRL key and select columns one by one (in the right order of joining)

2016-04-06_00h00_40

Then I’ll have the new table embedded as a result of the join;

2016-04-06_00h02_17

So I’ll just pick the key field from embedded table;

2016-04-06_00h03_20

And after save and closing query editor window I can create relationship in Power BI model based on a single column;

2016-04-06_00h04_41

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

8 Comments

  • Hi,
    is there something like that in SSAS tabular 2016?
    I don’t think so… But it would be very useful though.

    Thank you!

    • Hi Roberto,
      SSAS Tabular doesn’t have Power Query. So joining tables needs to be done at the data source level (if it is database, then SQL scripts).

      Cheers,
      Reza

  • Why prefer the join instead of concat 3 fields to 1 column, which is equal to the unique field in the date dim table? is the join way faster?

    • Hi Amein,

      This is just an example to give you an understanding what to do in such scenarios. the table to join to might not be date table, it might be anything else. In this particular example concatenation make sense, however if the other table is something else that concatenation of three fields or more doesn’t provide a way to create the connection to that table then join is the only choice.
      on the other hand in my example concatenation of those three connect me to a wrong date field, because my date dimension has concatenation of all three with Calendar date year and month, however the year and month in my budget table are fiscal.

      Cheers,
      Reza

  • Hello Reza,

    Awesome blog!!

    I am new to power bi and was looking to filter date columns in a table based on a DAX created calendar, but it does not help. I tried to understand your aforementioned solution but I guess that would not help me either. Could you please advise…

    • Hi Shoeb,
      This post is not about DAX created calendar. it is mostly about relationship between two tables. Let me know what you want to achieve, maybe I can be of help.
      Cheers
      Reza

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">