Using USERELATIONSHIP to Activate Inactive Relationships

In this video, Angelica Choo Quan, a trainer at Pragmatic Works, demonstrates how to leverage the USERELATIONSHIP function in Power BI to activate inactive relationships between tables in your data model. This technique allows users to analyze data based on multiple relationships that are not active by default.

Understanding Inactive Relationships

In Power BI, you can have multiple relationships between tables. However, only one relationship can be active at any given time. Inactive relationships are useful but cannot be automatically leveraged in reports unless you explicitly activate them. To analyze data based on these inactive relationships, Power BI users must use a specific DAX function: USERELATIONSHIP.

Steps to Activate Inactive Relationships Using USERELATIONSHIP

Angelica takes us through the following steps to use USERELATIONSHIP to activate an inactive relationship:

1. Prepare Your Data Model: Start by creating multiple relationships between your fact table and dimension tables. For instance, in the video, a sales orders table has an active relationship with a date table (based on the order date), but an inactive relationship exists with the ship date.
2. Create a New Relationship: In the example, Angelica demonstrates creating a new relationship between the date table and the sales orders table using the ship date. This relationship is not active by default.
3. Write the DAX Measure: To activate the inactive relationship, users need to create a new measure using the DAX functions CALCULATE and USERELATIONSHIP. This measure will evaluate data based on the inactive relationship. The DAX formula is structured as follows:

    quantity by ship date = 
    CALCULATE(
      SUM('Sales Orders'[Order Quantity]),
      USERELATIONSHIP('Date'[Date], 'Sales Orders'[Ship Date])
    )
  

1. Create a Visual: Once the measure is created, you can add it to a Power BI visual (like a matrix) to analyze sales order quantities based on the ship date, instead of the default order date.

Using USERELATIONSHIP in Visuals

Once the measure is created, Angelica adds it to a matrix visual. She demonstrates how to drill down by specific dates (from year to day) to see how the order quantity varies based on the ship date. This allows users to compare sales data by both the order date and the ship date, providing deeper insights.

Important Notes

While USERELATIONSHIP is a powerful tool for working with multiple relationships, there are some important limitations to keep in mind:

• USERELATIONSHIP cannot be used when row-level security (RLS) is defined for the table in which the measure is included.
• The inactive relationship must be properly set up in the data model before it can be activated with USERELATIONSHIP.

Conclusion

By using USERELATIONSHIP, Power BI users can take advantage of inactive relationships, allowing them to create more complex reports and visualizations. This technique expands the analytical capabilities of Power BI, making it possible to analyze data through multiple perspectives with ease.

For further learning on Power BI, DAX, Power Apps, and other Microsoft technologies, check out Pragmatic Works' on-demand training courses, linked below.

Don't forget to like and subscribe to Pragmatic Works for more tutorials and expert insights on Power BI and other tools!

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.