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.

Inactive Relationship

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.

Role-playing Dimension

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.

Summary

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.

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.