Power BI – DAX Use Relationship Function

By: David Rohlfs

Introduction

One of the most frustrating things that can happen is when you are building out your data model and you have to create a circular relationship in your model. This can happen for a number of reasons, but the issue is that this leaves your relationship inactive. A fix to this can be through using the Use Relationship function in DAX.

This blog will go over what the Use Relationship function in DAX is, when to use it, and a walkthrough demonstrating an example of this function.

When to use it

The most common time that you will be using this function is when you need to make a relationship active for a specific measure. What the use relationship function does is specify when an aggregation should use a specific relationship.

So, what can and can’t the use relationship function do?

• The use relationship function does not have an aggregation inside of it, so it is generally used in the filter expression of a calculate function, but it can be used in any DAX Boolean function.
• While you are able to put a use relationship function inside of a calculated column, this is not common practice because there are better functions to use in this situation. So, the use relationship function is generally regarded to be for measures only.
• The use relationship function is great at fixing situations where there is a circular relationship created for a specific calculation.
• The use relationship function needs to have an existing relationship to read. It does not have the power to create a temporary relationship, but instead reads inactive relationships.

A common scenario where the use relationship function will be used is when you need to connect a fact table to a date dimension and there are multiple connection points between the two. The fact table could contain dates for date of sale, ship date, invoice date, etc. Depending on what the situation is, you could either create multiple date dimensions for each connection, or you could utilize the use relationship function. Maybe your situation requires that you only want the sales amount from the invoice date, but the rest of your calculations will be focused on the date of sale. This would be a situation where utilizing a use relationship function inside of a calculate function would be ideal.

We would want to avoid creating another date dimension because using a measure will help the refresh speed instead of using a duplicated or referenced table. Although the final decision on what method to use is up to the report developer, after about three or four different calculations that will require a use relationship function, I prefer to reference the original table.

The syntax for the use relationship function is simple, it can be a little annoying to deal with. The syntax is
USERELATIONSHIP(‘Table1’[KeyColumn],’Table2’[KeyColumn])

One of the difficulties is that if any of the table or column names change after the use relationship function is used, the table and column names will NOT automatically update.

Walkthrough

Let’s go through a simple example where the use relationship function can be utilized. I have my fact table as a sales table and it contains two different dates. I have a sale date and an invoice date that is 30 days after the sale date. I want to see most of my calculations based on my sale date, but I also want to know when to expect the income based on my invoice date. My choice of method is to create a relationship between my fact table and my date dimension based on the sale date, then I will create a measure using the use relationship function between my fact table and date dimension based on the invoice date.

Here is my fact table:

I am going to create my first relationship between my Sales Table (Fact Table) and Date Dimension using the Date of Sale column. I am going to click on the Modeling ribbon and then go to Manage Relationships.

This is the view where you can see all of your relationships. You can also create relationships using the Model page (see Basic Relationship Building linked below). Now we are going to click on New in the bottom of the pop out.

Here we can build our relationship. I am going to choose my sale table and highlight the date of sale column. Then I will select my date dimension on the bottom view and select the date field.

Then we can check that our relationship cardinality is correct and click OK in the bottom right of the pop out.

Now we are going to create an inactive relationship for our invoice date. We will go through the same process, but instead of using the date of sale, we will use the invoice date. This will automatically create an inactive relationship between the two fields.

Now let’s go create our sales page. So far, everything on this page has been based on the date of sale relationship, but inside of my table I want to have the field Sales by Invoice.

I am going to create a new measure that sums the sales amount and uses the use relationship function.

Now I can plug this field into my table and all of the slicers will still work because they are filtering using the date dimension.

Conclusion

While the use relationship function is not very commonly used in a lot of reports, it as a lot of used once problems arise in your data model. If you would like to learn more about Power BI relationships, DAX, or date dimensions, I have linked some other EPM Strategy blogs below.

 

Links Related to This Blog:

Basic Relationship Building in Power BI

Row Specific Columns

Why Create a Date Table in Power BI

DAX Calculate Function

Comments