Relationship Function Of Power BI

INTRODUCTION

 
Power BI supports one more useful category of DAX function, and that is Relationship function. Relationship functions are used for managing and utilizing relationships between tables.
 
Below is the list of some relationship functions and their description.
 

RELATIONSHIP FUNCTIONS AND THEIR DESCRIPTION

 

CROSSFILTER

 
Specifies cross filtering direction 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
 
CROSSFILTER(LeftColumnName, RightColumnName, CrossFilterType)
 
LeftColumnName
 
The column name of existing table, using standard DAX syntax, that usually represents the left 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.
 
RightColumnName
 
The column name of existing table, using standard DAX syntax, that usually represents the right 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.
 
CrossFilterType
 
The cross-filter direction to be used. Must be one of the following,
  • No cross-filtering occurs along this relationship
  • one - Filters on the one or lookup side of the side of the relationship filter the many side.
  • both -Filters on either side filter the other
  • none - No cross-filtering occurs along this relationship
Remark
 
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
 
RELATED
 
Returns a related value from another table.
 
Syntax- - RELATED(ColumnName)
 
ColumnName–The column that contains the value you want to retrieve.
 
RELATEDTABLE
 
Returns the related tables filtered so that it only includes the related rows.
 
Syntax –RELATEDTABLE(Table)
 
Table - The name of an existing table using standard DAX syntax. It cannot be an expression.
 
Remark - This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
 

USERELATIONSHIP

 
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)
 
ColumnName1
 
The column of an existing table, using standard DAX syntax, 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 column of an existing table, using standard DAX syntax, 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.