Power Pivot Principles: Introducing the CROSSFILTER Function

The CROSSFILTER function specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. 

The function does not return any value.  It may only be used in functions that define filter as an argument such as CALCULATE and CALCULATETABLE etc.  It sets the cross-filtering direction for the indicated relationship and uses existing relationships in the model, identifying relationships by their ending point columns.  It is a very useful and smart technique that applies the filter through the relationships in the many-to-one direction when necessary.

The CROSSFILTER function uses the following syntax to operate:

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 also cannot be an expression

• <direction> The cross-filter direction to be specified.  The value can be one of the following:
    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.

For an example, consider the data tables, FactSales, DimShop and DimProduct, shown below:

They have the following relationships:

The relationships shown with arrows indicate that DimProduct can filter FactSales and DimShop can also filter FactSales. If we want to count the distinct value of products sold (i.e. the number of different types of products actually sold) in each shop, we use a measure Distinct Count of Product to calculate the unique value of Product ID as shown below:

If we further create a PivotTable base on this measure and see if the PivotTable can filter the distinct count base the filter of Shop Name. The result would be:

The total count of Distinct Count of Product is five (5) for all shops, which is not correct. If we take a closer look at the data of Shop A, we can see that there are only two products, Apple and Avocado, which are sold in this shop, not five. The reason for this error is that DimShop can not filter the DimProduct on an indirect one-to-many relationship.

One available solution for this case is to use the CROSSFILTER function to specify a direction for the relationship to be used between the DimShop and DimProduct tables. We can create the measure Distinct Count in the FactSales table as shown below:

In this measure, we use CALCULATE to evaluate the expression we create before and use CROSSFILTER to establish the bi-direction between FactSales and DimProduct. Then when we add the measure to the PivotTable created before, the results would become:

The new measure correctly calculates the Product ID sold for reach shop. If we add Product Name as an additional selection criterion in the PivotTable, this would produce the following:

From the PivotTable, we can clearly identify that for Shop A, only Apple and Avocado are sold, and the distinct count of the product is correctly calculated.


That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.