USERELATIONSHIP DAX Function (Relationships management)
Specifies an existing relationship to be used in the evaluation of a DAX expression. The relationship is defined by naming, as arguments, the two columns that serve as endpoints.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| ColumnName1 |
Foreign (or primary) key of the relationship. |
|
| ColumnName2 |
Primary (or foreign) key of the relationship. |
Return values
The function returns no value; the function only enables the indicated relationship for the duration of the calculation.
Remarks
USERELATIONSHIP can only be used in functions that take a filter predicate as an argument, for example: CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.
USERELATIONSHIP uses existing relationships in the model, identifying relationships by their ending point columns.
In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.
An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.
If CALCULATE expressions are nested, and more than one CALCULATE expression contains a USERELATIONSHIP function, then the innermost USERELATIONSHIP is the one that prevails in case of a conflict or ambiguity.
» 1 related function
Examples
-- USERELATIONSHIP activates a disabled relationship, deactivating
-- possible conflicting relationships.
--
-- Useful when the model contains inactive relationships to handle
-- role-playing dimensions.
DEFINE
MEASURE Sales[Delivery Amount] =
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
"Sales Amount", [Sales Amount],
"Delivery Amount", [Delivery Amount]
)
| Calendar Year | Sales Amount | Delivery Amount |
|---|---|---|
| 2007-01-01 | 11,309,946.12 | 11,034,860.43 |
| 2008-01-01 | 9,927,582.99 | 9,901,407.94 |
| 2009-01-01 | 9,353,814.87 | 9,442,286.09 |
| 2010-01-01 | (Blank) | 212,789.51 |
Related articles
Learn more about USERELATIONSHIP in the following articles:
-
Using USERELATIONSHIP in DAX
This article shows how to use the USERELATIONSHIP function in DAX to change the active relationship in a CALCULATE function. » Read more
-
USERELATIONSHIP in Calculated Columns
In a Power Pivot or Tabular model with inactive relationships, one can rely on the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but one might consider alternative syntax… » Read more
-
Relationships in Power BI and Tabular models
This article describes the types of relationships available in Power BI and Analysis Services, clarifying the differences in cardinality and filter propagation of physical relationships. » Read more
Related functions
Other related functions are:
Last update: Nov 28, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/userelationship-function-dax