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.