The USERELATIONSHIP function specifies the relationship between two tables to use in a calculation.
USERELATIONSHIP(
columnName1,
columnName2
)
- columnName1: Fully qualified name of an existing column. It cannot be an expression. It usually represents the "many" part in a "one-to-many" relationship, although if the function's arguments are in the wrong position, they will be automatically swapped before executing the function.
- columnName2: Fully qualified name of an existing column. Nor can it be an expression. It usually represents the "one" part in a "one-to-many" relationship, although, as mentioned, if the arguments of the function are in the wrong position, they will be interchanged before executing the function.
The USERELATIONSHIP function does not return any value.
The USERELATIONSHIP function can only be used as an argument in those functions that expect a filter, such as:
- CALCULATE
- CALCULATABLE
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- OPENINGBALANCEMONTH
- OPENINGBALANCEQUARTER
- OPENINGBALANCEYEAR
- TOTALMTD
- TOTALQTD
- TOTALYTD
Columns whose names are added as arguments must be the beginning and end of an existing relationship between tables, regardless of whether it is active or not.
If multiple nested CALCULATE functions including more than one USERELATIONSHIP function are being used, in the case of conflict or ambiguity the deeper USERELATIONSHIP function will be used.
Up to 10 USERELATIONSHIP functions can be nested, even though the expression in which they are included may be deeper.
In this example we start from a sales table that includes information on the sale date and the delivery date of the purchased product, as well as information on the unit price, the quantity of product sold and the total sale price (resulting from multiplying the unit price by the quantity):
Additionally, we import a second table into Power BI as a calendar (Calendario). We establish a relationship between both tables using the fields Fecha de venta (sales date, in the Ventas table) and Fecha (date, in the Calendario table). Next, we create a second relationship (which, by default, is inactive) between the fields Fecha de entrega (delivery date, in the Sales table) and Fecha (in the Calendar table):
We now wish to calculate the sales per month. With this objective in mind, we created the following measure that adds sales in the current context:
Ventas = SUM(Ventas[Precio de venta])
As there is no reference to the relationship to be used, the active relationship (the one between the Fecha de venta and Fecha fields) will be used. We can take this measure together with the Month Name field to a column chart to see the totals sales per month:
As mentioned, sales are being added per month considering the sale dates. But suppose we want to know the distribution by months of sales considering the delivery date. For this we create a second measure that implies the second relationship created, the existing (and inactive) between the Fecha de entrega and Fecha fields:
Ventas por fecha de entrega =
CALCULATE(
[Ventas],
USERELATIONSHIP(
Ventas[Fecha de entrega],
Calendario[Fecha]
)
)
Note how the [Ventas] measure is being calculated using a new relationship between tables as a filter: the existing one between the two columns indicated as arguments. If we take this new measure together with the Month Name field to a column chart, the result is the following:
