Power BI – Using Inactive Relationships in a Measure
In this post I will show a simple example of how to use an inactive 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
More Power BI Posts
- Conditional Formatting Update
- Data Refresh Date
- Using Inactive Relationships in a Measure
- DAX CrossFilter Function
- COALESCE Function to Remove Blanks
- Personalize Visuals
- Gradient Legends
- Endorse a Dataset as Promoted or Certified
- Q&A Synonyms Update
- Import Text Using Examples
- Paginated Report Resources
- Refreshing Datasets Automatically with Power BI Dataflows
- Charticulator
- Dataverse Connector – July 2022 Update
- Dataverse Choice Columns
- Switch Dataverse Tenancy
- Connecting to Google Analytics
- Take Over a Dataset
- Export Data from Power BI Visuals
- Embed a Paginated Report
- Using SQL on Dataverse for Power BI
- Power Platform Solution and Power BI Series
- Creating a Custom Smart Narrative
- Power Automate Button in a Power BI Report
Power BI Series
