Relationship in Power BI with Multiple Columns

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

16 thoughts on “Relationship in Power BI with Multiple Columns

  • 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

  • Hi Reza,
    Great blog and resources. I am fairly new to Power BI and trying to streamline bunch of reports that are currently being manipulated manually in Excel. My problem is I have one detailed Table and one look up Table that need to be joined. However, there are no unique composite keys that I can create in each Table and then join them in either SQL or Power BI. In fact, the lookup Table is in Matrix format but that’s easy to restructure it. So far, I tried to incorporate the lookup Table into the detail one manually. But this technique defeats the purpose of streamlining and automating the report. my thoughts are more geared towards design issues. Any tips as how to approach this problem?

    Thank You,

    • Hi.
      As mentioned in this post, you need to find a unique combination. I suggest change your lookup table from Matrix format with Unpivot, and then you should most probably find the unique combination.
      Cheers
      Reza

  • I have similar scenario but I need aggregation at Fiscal Year, Fiscal Quarter and Month. If I use composite key based join or merged join, then how can we still aggregate at Quarter and Year level?

  • Hi Reza
    I followed the above guide but Power BI (Version: 2.72.5556.801 64-bit (August 2019))
    But no “DateKey” is created .
    Is what you described here still doable in the August Version of Power BI?
    Cheers
    Paul

    • Hi Paul
      You can create the relationship yourself if it is not created automatically. Drag the Datekey from one table to the Datekey in the other table.
      Cheers
      Reza

Leave a Reply

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