Daily DAX : Day 314 USERELATIONSHIP

USERELATIONSHIP DAX Function

Description

The USERELATIONSHIP function in Power BI DAX (Data Analysis Expressions) is used to specify which relationship to use between two tables when multiple relationships exist. It activates an inactive relationship for a specific calculation, overriding the default active relationship.

Syntax

USERELATIONSHIP(<column1>, <column2>)

• <column1>: The column in the first table of the relationship.
• <column2>: The column in the second table of the relationship.

Use Case

USERELATIONSHIP is useful when a data model has multiple relationships between two tables, but only one can be active at a time. For example, in a sales model, you might have a Date table related to a Sales table by both Order Date (active) and Ship Date (inactive). Use USERELATIONSHIP to calculate metrics based on the inactive Ship Date relationship.

Example

Suppose you have:

• Sales table with columns: OrderID, OrderDate, ShipDate, Amount.
• Date table with column: Date.
• Relationships:
  • Active: Sales[OrderDate] to Date[Date]
  • Inactive: Sales[ShipDate] to Date[Date]

To calculate total sales amount by Ship Date:

TotalSalesByShipDate = 
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Date[Date])
)

This measure uses the inactive Ship Date relationship for the calculation.

Notes

• USERELATIONSHIP must be used within a CALCULATE or CALCULATETABLE function.
• Only one relationship can be active at a time for a calculation.
• Ensure the specified relationship exists in the data model.

Common Scenario

Business Need: A company wants to analyze sales by both order date and ship date. The default analysis uses OrderDate, but a specific report requires metrics based on ShipDate. USERELATIONSHIP enables this by activating the inactive ShipDate relationship for that report.