Power BI – Using inactive relationships in a measure.

In this post I will show a simple example of how to use a different relationship between two tables in a measure. The example I will use is a list of tasks with start and finish dates. The request is for how many tasks start per week and how many finish per week to be plotted on a column chart.

YouTube Version

Data Setup

The data for this report is a list of tasks in an Excel spreadsheet with an Task Id, Task Name, Start Date, and Finish Date columns. I have also added a calendar table with a Date column and a Start of Week column.

In the relationships view I drag Date from Calendar onto Start Date in Tasks and a 1 to many relationship appears as expected. That will allow me to count the tasks starting.

I also want to be able to count the finishing tasks, so I need a relationship to Finish Date from the calendar as well. So I drag the Date from Calendar onto Finish Date in Tasks. A relationship appears but this time it is dotted, which shows it is not the active relationship.

If you look in Manage relationships it will list both relationships but only one will be ticked as Active.

Creating the Measures

Now the relationships have been setup we can create the two measures to do the 2 different counts.

The first measure is the simplest and will use the active relationship between Calendar[Date] and Tasks[Start Date]. It is just a count of the Tasks Id column.

For the second measure to count the number of tasks finishing we need to use the other other relationship. We can do this by using the Calculate function and the UseRelationship function. The UseRelationship function allows you to use an inactive relationship by specifying the two related columns. It must be an existing relationship in the model.

Tasks Finishing = 
    CALCULATE(
        COUNT( Tasks[Task ID] ),
        USERELATIONSHIP( 'Calendar'[Date], Tasks[Finish Date] )
    )

We can now add these measures to a column chart to get a plot to compare starting to finishing tasks.

Conclusion

The USERELATIONSHIP function is a powerful addition to your creating measures tool-set. It does have some restrictions and these are detailed in the official documentation at https://docs.microsoft.com/en-us/dax/userelationship-function-dax