Similar to the USERELATIONSHIP() function that can invoke an inactive relationship for a specific DAX measure, the CROSSFILTER() function can be used to implement a specific cross-filtering behavior (single, bidirectional, none) for a specific measure. The cross-filtering behavior specified in the measure overrides the default cross-filtering behavior defined for the relationship.
In the following report visual, an Employee Count measure only references the Employee dimension table and therefore is not impacted by the filter selections of the Sales Territory Country slicer due to the single direction relationship between Employee and Reseller Sales:
The Employee Count (CF) measure, however, does adjust to reflect the Sales Territory Country selections as well as any other dimension table filter selections that impact the Reseller Sales fact table, such as the Date, Product, and Promotion dimension tables. In this example, the Reseller Sales fact table is first filtered to the set of Reseller Sales rows associated with the Germany and United Kingdom sales territory countries. This filtered set of Reseller Sales rows is then used to filter the Employee table resulting in three distinct employee key values. The value of 3 represents the three salespeople associated with the Reseller Sales of Germany and United Kingdom.
If the intent is to only count the employees associated with Reseller Sales and to respect filter selections on related dimensions, the DAX measure can be written as follows:
CALCULATE(DISTINCTCOUNT(Employee[Employee Alternate Key]),'Reseller Sales')
See the Dimension metrics section of Chapter 4, Developing DAX Measures and Security Roles for more details.
The syntax for CROSSFILTER() is also very similar to USERELATIONSHIP(), as shown by the following code block:
Employee Count = DISTINCTCOUNT(Employee[Employee Alternate Key])
Employee Count (CF) = CALCULATE(DISTINCTCOUNT('Employee'[Employee Alternate Key]),
CROSSFILTER('Reseller Sales'[Employee Key],'Employee'[Employee Key],Both))
The Employee Alternate Key column represents the business key or natural key of the employee. The Employee Key column uniquely identifies each row of the Employee table and is used in the relationship with Reseller Sales. Given the slowly changing dimension process, which adds a new employee row when certain attributes of an employee change, it's necessary to reference the Employee Alternate Key column in the DISTINCTCOUNT() measures to only count each employee once.
The third parameter to CROSSFILTER() can be set to OneWay, Both, or None. Given the potential for unexpected or undesired results when applying bidirectional cross-filtering relationships to models with multiple fact tables, it's generally recommended to selectively enable bidirectional cross-filtering per measure, such as in the preceding example.