- DAX Functions Tutorial
- DAX Functions - Home
- DAX Functions - Introduction
- DAX Parameter Naming Conventions
- Description Structure
- DAX Functions - Aggregation
- DAX Functions - Filter
- DAX Functions - Time Intelligence
- DAX Functions - Date and Time
- DAX Functions - Information
- DAX Functions - Logical
- Math & Trigonometric Functions
- DAX Functions - Parent & Child
- DAX Functions - Statistical
- DAX Functions - Text
- DAX Functions - Other
- DAX Functions Useful Resources
- DAX Functions - Quick Guide
- DAX Functions - Useful Resources
- DAX Functions - Discussion
DAX Filter - CROSSFILTER function
Description
Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.
DAX CROSSFILTER function is new in Excel 2016.
Syntax
CROSSFILTER (<columnName1>, <columnName2>, <direction>)
Parameters
Sr.No. | Parameter & Description |
---|---|
1 |
columnName1 The name of a column, fully qualified, that usually represents the many side or data (fact) table side of the relationship to be used. If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression. |
2 |
columnName2 The name of a column, fully qualified, that usually represents the one side or lookup table side of the relationship to be used. If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression. |
3 |
direction The cross-filter direction to be used −
|
Return Value
DAX CROSSFILTER function does not return any value.
DAX CROSSFILTER function only sets the cross-filtering direction for the indicated relationship, for the duration of the query.
Remarks
In the case of a 1:1 relationship, there is no difference between one and both direction.
CROSSFILTER can be used only in functions that takes a filter as an argument. For example, CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.
CROSSFILTER uses the existing relationships in the model, identifying relationships by their ending point columns.
In CROSSFILTER, the cross-filtering setting of a relationship is not important. That is, whether the relationship is set to filter one, or both directions in the model does not affect the usage of the function. CROSSFILTER will override any existing crossfiltering setting.
An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.
If CALCULATE expressions are nested, and more than one CALCULATE expression contains a CROSSFILTER function, then the innermost CROSSFILTER is the one that prevails in case of a conflict or ambiguity.
Example
= CALCULATE (Sales[Distinct Count of Products], CROSSFILTER (Sales[Product],Products[Product],Both))