DAX For Relationships in Power BI | CloudFronts

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.


Share Story :

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close