Relationship in Power BI with Multiple Columns



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:


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)


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;


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.



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;


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)


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


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


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



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:
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.

14 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).


  • 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.


  • 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.

  • 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.

  • 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?

Leave a Reply

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