In a Power BI model, relationships are important for passing filters, filter propagates through relationships. However, sometimes you create the relationship between two tables, and the relationship is a dashed line. In this post, I’ll explain to you everything you need to know about a dashed relationship, or as it called Inactive relationship. I will explain two different methods that you can deal with this kind of relationship. So, ready? Let’s go through it.
If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Why Relationships in Power BI?
To start this post, the very first topic to discuss is to understand why a relationship in Power BI is important? A relationship in relational database systems is important to link tables to each other, but in Power BI, the relationship also plays another even more important role; Filtering.
To understand how the relationship works, let’s check this example:
I have a sample Power BI file getting data from the AdventureWorksDW Excel file example, and I get information from two tables: FactInternetSales, and DimDate. These two tables are NOT related to each other at the beginning.
Now, let’s create a simple column chart with the SalesAmount from the FactInternetSales table, and the FullDateAlternateKey from the DimDate table. Because the FullDateAlternateKey is a date field, Power BI brings the default hierarchy, and I’ll see the visual slicing and dicing data by the highest level of the hierarchy, which is Year.
But wait, it isn’t actually slicing and dicing! It is showing the same SalesAmount for every single year from 2005 to 2010! The value is very close to $30 million which is actually the grand total of the sales in my dataset. The fact is that the FullDateAlternateKey field is NOT filtering the FactSalesAmount table.
Relationship Means Filtering
Now, let’s create the relationship between these two tables, based on the OrderDateKey in the FactInternetSales table and the DateKey in the DimDate table;
That’s it, let’s go and check the same visualization again:
As you can see the same visual, this time filters by the date field. Or better to say; DimDate can now FILTER the FactInternetSales table. All of that because of the relationship. Without a relationship, we cannot filter data across tables just by itself, you may need to do some DAX expressions instead.
Relationship in Power BI means Filtering, and ability to slice and dice a table by another table.
Now that you now relationships are for Filtering, let’s check out what the inactive relationship is.
The type of relationship you have seen above is called an active relationship. There is another type of relationship called Inactive. Let’s see how an inactive relationship will be created. In the previous example, we sliced and diced data by the OrderDateKey field, because that was the field connected through the relationship to the DimDate table. Now, let’s say we want to slice and dice data by the ShipDateKey. The very simple approach is to create another relationship between the DimDate table and FactInternetSales but this time to the ShipDateKey. Here is the result:
As you can see this new type of relationship is different. It is dashed line, compared to the active, which was a solid line. This is an inactive relationship. You can only have one active relationship between two tables. Any other relationships will become inactive.
You can only have one active relationship between two tables. Any additional relationships will become inactive.
An inactive relationship doesn’t pass filtering. It doesn’t do anything by itself. I still see many people creating inactive relationships in their model thinking that just the inactive relationship by itself will do some filtering. It doesn’t. If I use the FullDateAlternateKey from the DimDate table to slice and dice the SalesAmount from the FactInternetSales table, which field I’m filtering based on? The field that is related through an Active relationship of course. Here is a result for that (which is apparently same as what you have seen in the previous example because the inactive relationship doesn’t do anything. It is just the active relationship that passes the filter);
Inactive Relationship Doesn’t pass the filtering by itself. It needs treatment!
Yes, the inactive relationship needs a special treatment to work. Let’s see how this can work. I explain two treatments for an inactive relationship; Role-playing dimension, and UseRelationship method.
A dimension that plays the role of multiple dimensions called role-playing dimension in the data warehousing terminologies. In the above example, DimDate is going to play the role of Order Date in some scenarios, and the role of Ship Date in other scenarios, and also sometimes role of Due Date in other times. I already explained a sample usage of Calculated tables in DAX to implement a role-playing dimension, so let’s go through it very quickly here too.
One method to deal with the inactive relationship is to remove the cause to create it! If having multiple relationships between two tables is causing the creation of inactive relationship, one way to avoid it seems to be creating multiple instances of the same table, and then you would need only one relationship not more than that.
Let’s create a copy of the DimDate, One way to create the copy is to use a Calculated Table with ALL DAX function in it;
ALL is a function that gives you the entire table. In this case, we are creating a copy of the DimDate table, and calling it ShipDate. Now you can create a normal active relationship between ShipDate and the FactInternetSales table (I have removed the inactive relationship from the previous section);
If you like to learn about the benefits of calculated tables, I recommend reading my post about calculated tables here.
And now, as a result, you have slice and dice by the ShipDate table as well as the Order Date (or let’s say DimDate table);
Role-playing dimension is one of the ways that you can handle an inactive relationship, but be careful of memory consumption!
Copy only small tables
Role-playing dimension method is actually copying the table, and you will have double up memory consumption. The extra memory consumption can be overlooked if the table is small. A Date table is a small table. For every year, it is 365 rows, and for 20 years, it will be around 7,000 rows. It is very small compared to a fact table with millions of rows. This solution is good for small tables. But don’t use this method for big tables. If you have a dimension table with 5 million rows and 30 columns, then role-playing dimension method means consumption of the same amount of space twice or three times or more.
Avoid role-playing dimension if you have large dimension. This method is only good for small tables.
UseRelationship Function in DAX
Another method to handle inactive relationship is to use a function in DAX called UseRelationship. This DAX function is literally saying to Power BI that for this expression, use this relationship, even if it is inactive. Let’s see how this function works.
If we continue the same example of slicing and dicing by Ship Date and assume that there is no Ship Date calculated table created, then we can do it this way; Create the inactive relationship between DimDate and FactInternetSales again based on the ShipDateKey.
Now, let’s create a Measure in Power BI with below expression:
Sales by Ship Date = CALCULATE( SUM(FactInternetSales[SalesAmount]), USERELATIONSHIP( FactInternetSales[ShipDateKey], DimDate[DateKey] ) )
This measure calculates the sum of sales by ship date. The whole secret is the usage of the UseRelationship function. This is a really simple function to use, you just need to provide two input columns to it, the two columns that are two sides of the relationship. Their order is not important.
UseRelationship (<column 1>, <column 2>)
The important tip to consider is that you HAVE to have an existing inactive relationship for this function to work, otherwise you get the error below:
Inactive relationship must exist otherwise the UseRelationship doesn’t work.
One table filters the other table based on multiple fields
The main benefit of using this method is that you can now have the DimDate table to filter the fact table based on both ShipDateKey and OrderDateKey at the same time, as illustrated below:
As you can see in the above screenshot, one date table filters fact table based on multiple fields. One is based on OrderDateKey which is an active relationship, and the other one is based on ShipDateKey through the usage of the UseRelationship method in the measure.
This method doesn’t consume extra memory, however, you do need to create a measure for every single calculation with the UseRelationship function.
In this post, you learned about inactive relationships, and how to handle them through two methods; Role-playing dimension, and UseRelationship function in DAX. Role-playing dimension method is good for smaller tables where the extra memory consumption is not the issue. UseRelationship method, on the other hand, can be a good substitute when the tables are bigger. There are other benefits such as getting one table filtering based on multiple fields at the same time as you’ve seen. Which of these methods or any other methods do you use? Please share it through the comments below, or if you have any questions, please don’t hesitate to ask.
16 thoughts on “UseRelationship or Role-Playing Dimension; Dealing with Inactive Relationships in Power BI”
I fall into this question today : use different tables or USERELATIONSHIP to handling the dimension role playing.
I already have one multi dimensional with their role playing dimension and it’s not terrible, so I decided to go with the USERELATIONSHIP for my tabular.
At first look, if you only have Date and Sales, it looks fine.
But it my model, I have role playing dimension everywhere (Customer Delivered To / Customer Order To / Customer Payee etc.) so it become quite an effort to write all the measures and not even think about how to maintain/show to the end users.
I hope in the near future MS can make this functionality become more intuitive.
The process behind the scene is, in fact, similar to the multi-dimensional model. the only difference is that in the multi-dimensional model the GUI will create the role-playing dimension tables for you. In tabular, you have to do to do it yourself. And also you have the option of using UseRelationship instead if you want.
Hi Reza, thanks for this post.
In the above example you show two separate charts (sales by order date and sales by ship date) which is fine.
But is it possible when using two date dimension tables as shown above to have *both* measures on the same chart for comparison? It seems to me you can only have one date on the axis, so only one of the measures will work?? If this is correct, is this a good reason to have multiple (inactive) relationships, and use USERELATIONSHIP in the measures? i.e. so that more than one measures can be included in the same chart?
Yes, you got the point. One of the advantages of using an inactive relationship and then using the UseRelationship method is that you are using one dimension to filter multiple measures.
I have multiple tables with active relation as project number all across the tables. 2 tables Pending requisitions and PO under approval have resource data. When I am trying to show the data resource wise for above 2 tables on one page the resource filter is not giving correct data due to project number relationship. Could you please guide me how can I use single filter and get the data on one report page.
Fields in Pending requisitions: – project number, item, status, indent date and resource
fields in PO under approval:- project number, item, status, proposal value, resource.
From reading your question, I understand your model doesn’t have a shared dimension for the project (with the project number). I recommend using this method.
We tried with a bar chart. We built out the measure correctly but when we moved the month/year attribute to the chart, it defaulted to the active relationship. Paraphrasing the above exmaple, we get ship totals by month/year of the order date, not the ship date.
where the new month/year in the bar chart is coming from? from which table?
Hi Reza, This is a very helpful information given by you. I have one doubt in Role-playing Dimension.
Can you please elaborate Role-playing Dimension with Direct Query and Import. My question is if I have connected data to PowerBI with Direct Query and made a copy of any table to achieve Role-playing Dimension post that any changes done in original table then can it reflect in copied table(Role-playing Dimension).
for DQ sources, you can use a database view of the source table, and use that as a second table.
Thank you Reza for clarifying.
Is there any limitation to create inactive relationship? How many inactive relationships we can create maximum to use USERRELATIONSHIP function. OR
How many USERRELATIONSHIP function we can write by referring single table?
No limitations as far as I know
That’s great…Thanks Reza.
Thank you for the post.
I have multiple date fields in my fact and decided to use the USERELATIONSHIP method to create measures by multiple date fields.
Advantage of this approach was that I was able to plot measures related to different date fields on the same graph by date/quarter/year etc.
But, the limitation I am seeing is with interactions between visualizations. Selecting a measure in a visualization that is calculated based on an inactive relationship (using USERELATIONSHIP) filters other related visuals on the page using the active relationship, and there is no way to specify the interaction to use the inactive relationship.
Hope the PowerBI team come up with a way to specify the relationship to be used for interactions as well.
You can somehow implement that using the Parameter table concept and creating virtual relationships and DAX measures, but it would need writing a lot of code.