In Power BI, there is a DAX function called USERELATIONSHIP. This is particularly useful when you need multiple relationships between tables. Here we will go through an example of how this works.
Consider a typical example, where you have an Orders table with different dates such as the Order Date (i.e. the date the order was created) and the Ship Date (i.e. the date the order was shipped). You would like to run reports on data based on each of these dates at the same time.
The tables looks like below, and you also have a standard Dates table:
Now, we will build a relationship so we can use the Orders table with the Dates table.
Click on Manage Relationships:
This will open the window:
Click New, and select the two dates to form the relationship, then click OK with the defaults below:
You will now see:
Now let’s drag some visualizations across to view the data. We will add a Calendar Month slicer so we can filter by Month to see how our sales amount is doing, and we will drag across the Amount field, which is a sum of the amount. We also have some other tables to display the records of Order Date, Ship Date and Amount:
So we can see, the total amount is 900. If we click on February, we can see the total amount is 150:
Now, this amount is based on the relationship we defined above. Power BI is assuming when we select a date from the slicer, that our visualizations will use this date for their filtering. What if we want to see how our sales are doing by using the ship date?
The first thing we will do, is click to add a new relationship. We will select the Ship Date this time, and the date as we did last time. Notice this time, if we try check the relationship as Active, we get a message “Another active relationship exists” :
Uncheck the relationship active box and click OK. You will now see two relationships, one active and one not active:
We can now use this non-active relationship to build our queries. Let’s create a new measure to display the total sales amount, using the shipping date.
On our Orders table, click to add a new measure:
In the measure window, add the code:
Amount Using Ship Date = CALCULATE ( SUM ( Orders[Amount] ), USERELATIONSHIP ( Orders[Ship Date], Dates[Date] ) )
Now, drag out new field onto the form and filter by the month of February. In this month the amount by ship date is $250, different from the amount by order date:
Now, we can see above that the measure works well, but our table charts are still running off the order date relationship.
One way to fix this is to make a new copy of the orders table. In the query editor, right click the data source and click Duplicate:
Rename to OrderByShipDate, and Close and Apply:
Now in Manage Relationships, add a Many to One relationship between the OrderByShipDate table and the Dates table using the Ship Date key:
Your relationships should now look like:
Now let’s add this new table to our report. Add the fields Ship Date and Amount in a table view. You can now see the Ship Date lines displayed: