Previously, I discussed the importance of data model relationships in Power BI. I also introduced the one-to-many and many-to-one relationships.
Now, I’ll teach you about the difference between active and inactive data relationships. These are important concepts that you need to be familiar with in data modelling.
In this example, you can see that the Date column from the Dates table is already connected to the Order Date column of the Sales table.
However, there’s also an existing date column in the Sales table. This is the Ship Date column. There might be a time when you need to include both of these tables in your report.
To make sure, you also need to create a relationship between the Dates table and Sales table again. This time, you should create a relationship between the Date column and Ship Date column.
Now, you can see that the new line that connects the two tables is a dotted one. The dotted line signifies an inactive data relationship.
In Power BI, you can only create one active relationship between the same tables at the same time. But sometimes, you have to create multiple relationships between the tables. Thus, you need to properly identify which relationships are active and which ones are inactive.
For example, grab the Date column from the Dates table.
After that, grab the Total Revenue column from the Sales table.
The results that you can see under the Total Revenue column are all done according to the order date because of the existing active relationship.
If you double-click the dotted line, you can see the Edit relationship window. In this window, you can see that the Make this relationship active checkbox is disabled.
This is how you can identify that these columns have inactive data relationships.
Enabling Inactive Data Relationships
If you want to look at the data by the shipping date in the example, you can turn on the inactive relationship. The best way to do this is by using a DAX formula.
First, click New Measure, and then name it as Sales by Ship Date.
Don’t worry too much about using DAX formulas here. I just wanted to show you a simple example of how you can calculate the total revenue.
You need the USERELATIONSHIP function to reference the Date column from the Dates table to the Ship Date column of the Sales table.
By using the USERELATIONSHIP function, you can override the existing active relationship into the new one.
After you apply the formula, drag the Sales by Ship Date column into the table. This time, you can see different results because the revenue is now based on the ship date.
I hope you understand the importance of these additional insights about data modeling. It’s essential to differentiate between the active and inactive data connections so you can utilize them properly.
Also, the USERELATIONSHIP function is very useful, but a bit advanced. Don’t worry, you can learn more about this function when we discuss DAX formulas.
If you want to get more tutorials related to data modelling, check out the related links below.
***** Related Links *****
Creating Virtual Relationships In Power BI Using The TREATAS Function
Power BI Data Model For Advanced Scenario Analysis Using DAX
How To Organize Your Power BI Data Model – Best Practice Tips
***** Related Support Forum Posts *****
Filtering And Displaying Data Based On Inactive Relationship
Active Relationships Using DAX
Data Model Relationships
For more data relationships queries to review see here….