DAX For Relationships in Power BI

Hi everyone in this blog we will see the different DAX that are used to define or use the relationship between two tables.

In Power BI there are two type of relationships

                1. One to One  (1:1)

                2. One to Many   (1:*)

Now lets look at the DAX functions that we can use with these relationships.

USERELATIONSHIP-

 Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

Syntax 

USERELATIONSHIP(<columnName1>,<columnName2>)

Where,

columnName1        

The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the many side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression.

columnName2        

The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the one side or lookup side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression.

Key Point

The function returns no value; the function only enables the indicated relationship for the duration of the calculation.

Example

= CALCULATE(SUM(ISales[SalesAmount]), USERELATIONSHIP(Sales[ShippingDate], DateTime[Date]))

Limitations
           USERELATIONSHIP can only be used in functions that take a filter as an argument.

USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included.

RELATED –

Returns a related value from another table.

Syntax

RELATED(<column>)

Where,

column        – The column that contains the values you want to retrieve.

Key Point

A single value that is related to the current row.

Example

FILTER( ‘Sales_USD’, RELATED(‘Territory'[TerritoryCountry])<>”United States”)

RELATEDTABLE

Evaluates a table expression in a context modified by the given filters.

Where,

tableName – The name of an existing table using standard DAX syntax. It cannot be an expression.

Key Point

A table of values.

Example

= SUMX( RELATEDTABLE(‘Sales_USD’) 

     , [Amount_USD]) 

Limitation

• The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify.
• This function is a shortcut for CALCULATETABLE function with no logical expression.
• This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules.

Hope this helps.