DAXDAX Trainer

DAX Concept

Leveraging USERELATIONSHIP for Multiple Inactive Date Relationships

To learn how to use the USERELATIONSHIP function to activate inactive relationships in DAX, allowing for flexible date-based calculations on a single date dimension.

IntermediateUSERELATIONSHIPCALCULATECOUNTROWS

Concept Breakdown

In Power BI, when you have multiple relationships between two tables (e.g., a 'Staff' table and a 'Calendar' table, linked by 'DateBorn' and 'DateJoined'), only one can be active at a time. The active relationship is used by default in calculations. Inactive relationships are represented by dashed lines in the model view. The USERELATIONSHIP function allows you to temporarily activate a specific inactive relationship within the filter context of a CALCULATE function. This enables you to perform calculations based on different date perspectives (like birth dates versus join dates) using the same central calendar table, avoiding the need for duplicate date tables in your model.

What You Will Learn

  1. Understand when to use USERELATIONSHIP, CALCULATE, COUNTROWS in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Excel file containing 'Staff' and 'Dates' worksheets.

  2. Open Power BI Desktop and load both 'Staff' and 'Dates' worksheets from the Excel file into your report.

  3. In the Model view, rename the 'Dates' table to 'Calendar'.

  4. Mark the 'Calendar' table as a Date table (Table tools tab > Mark as Date table > select 'DateKey' as the Date column).

  5. Create two relationships between your tables in the Model view:

  6. a. Drag 'Calendar'[DateKey] to 'Staff'[DateBorn]. (Power BI will likely make this inactive by default if another relationship is active).

  7. b. Drag 'Calendar'[DateKey] to 'Staff'[DateJoined]. (Ensure this relationship is also inactive if the previous one became active, or vice-versa. USERELATIONSHIP will explicitly activate the one you need.)

  8. Create a new measure named 'Number of Joiners' and paste the provided DAX formula.

  9. Create a new measure named 'Number Born' and paste the provided DAX formula.

  10. Add a Table visual to your report page.

  11. Drag 'Calendar'[Year] to the Table visual.

  12. Drag both 'Number of Joiners' and 'Number Born' measures to the Table visual.

  13. Observe how the measures display the correct counts for joiners and births per year, demonstrating the selective activation of relationships via USERELATIONSHIP.

Starter DAX

Number of Joiners = COUNTROWS('Staff')

Expected Outcome

A table visual showing years (e.g., 2000, 2001, etc.) in the first column, with corresponding counts of staff members who joined and staff members who were born in each year in subsequent columns, accurately reflecting the selective activation of relationships.

Checking your sign-in status...