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

USERELATIONSHIP ( <ColumnName1>, <ColumnName2> )
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.

USERELATIONSHIP cannot be used when it changes the default filter propagation from a table that has Row-level security (RLS) applied. Consider using TREATAS as a workaround in those cases.

» 7 related articles
» 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:

Related functions

Other related functions are:

Last update: Dec 4, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://docs.microsoft.com/en-us/dax/userelationship-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of USERELATIONSHIP? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.