CROSSFILTER and RELATEDTABLE in DAX

[SachinNandanwar]

The aritcle was originally published on : www.azureguru.net

Link to the original article : link 

CROSSFILTER

CROSSFILTER is a very versatile function used to either control or remove the direction of a filter across tables in a relationship. In other words, it helps to override the default filter direction of a relationship.

A typical relationship use one to many notations where the filter flows from “one” side to “many” side.

For example Product → Sales. But there can be scenarios where you would want the filter to flow from “many” side to “one”Sales → Product.

This is where CROSSFILTER comes in very handy. Of Course you could set the relationship to bidirectional in the model, but that comes with its own risks like circular dependencies. So it's generally safer to avoid setting up bidirectional relationships and instead use CROSSFILTER as and when truly required.

SQLBI has a wonderful and informative blog on the pitfalls related to Bidirectional relationships which you can find it  here.

To demonstrate the efficacy of CROSSFILTER usage in DAX we will use the Contoso data model and test them on DAX.do

As you can see above, we have predefined relationship of 1:M and unidirectional flow filter between Customer → Sales and Product → Sales.
 

But what if we want the filter to flow from Product to Customer ? For example, to fetch the customer count for each product, What would the DAX query be like ? 

You probably might use something like this 

[CustomerCount] = DISTINCTCOUNT(Customer[CustomerKey])

Lets use the above measure and execute it on  DAX.do

DEFINE MEASURE Customer[CustomerCount] = DISTINCTCOUNT(Customer[CustomerKey])

EVALUATE
SUMMARIZECOLUMNS (
    Product[ProductKey],
    'Product'[Brand],
    'Product'[Product Name],
    "Sales Amount", [Sales Amount],
    "Customer Count", [CustomerCount]
    )

As you can see, the total customer count is repeated across each product. This occurs because of aforementioned reasons where the filter applied on Product does not propagate to Customer (Product → Customer)  which makes Customer unfiltered ignoring the filter context on Product. 

This is where CROSSFILTER comes in handy. Using CROSSFILTER we can now make the filter traverse all the way from Product to Customer ( Product → Customer).

The syntax for CROSSFILTER is 

CROSSFILTER(<columnName1>, <columnName2>, <direction>)

The possible values for the <direction> argument can be NONE/BOTH/ONEWAY. There are two other possible values, OneWay_LeftFiltersRight and OneWay_RightFiltersLeft which are seldomly used.
 

Going back to our earlier example, lets change the DAX query to use CROSSFILTER 

DEFINE
MEASURE Customer[CustomerCount] =
    CALCULATE (
        COUNTROWS(Customer),
        CROSSFILTER(Sales[CustomerKey],'Customer'[CustomerKey],BOTH)
    )
EVALUATE
   SUMMARIZECOLUMNS(
   Product[ProductKey],
   'Product'[Brand],
   'Product'[Product Name], 
   "Customer Count", [CustomerCount],
   "Sales Amount", [Sales Amount]    
) ORDER BY (Product[ProductKey])

Execute the above query and we get the desired output. 

So what happened here ?

 

Remember, in the model the filter flows only unidirectional, i.e. from Product → Sales and Customer→ Sales.So when we try to get a count of customers who bought certain product, the filter flows from Product to Sales and stops there and does not propagate to Customer which leads to effectively counting all customers from the customer table.

 When we modify the measure and use CROSSFILTER, we effectively say to the formula engine to propagate the filter from all the way to fromProduct → Customer to return unique customer count for each product based on the filter context on Product introduced by the CALCULATE function in our measure.
 

You might ask, are we not just “expanding” our filter flow from Product → Sales → Customer .
 

Yes we are but remember that Sales is on the many side of relationship and Customer and Product are on one side. So effectively, by default the filter flow fromProduct → Sales is unidirectional.

 Filter flow fromSales → Customer works ONLY when we use CROSSFILTER that temporarily enables the flow during measure evaluation. CROSSFILTER enables the formula engine to fetch data from the many side of the relationship. 

RELATEDTABLE

RELATEDTABLE works on the many side of relationship .In other words it fetches the data from the table that is on the many side of the relationship.

Below is the syntax for RELATEDTABLE 

RELATEDTABLE(<tableName>)

In our example, lets say we want to fetch the total sales count for a given customer. In such use cases RELATEDTABLE are useful. 

EVALUATE
SELECTCOLUMNS (Customer,
"Customer Key" , Customer[Customer Code],
"Customer Name" , Customer[Name],
"Total Sales", (COUNTROWS(RELATEDTABLE(Sales))))

RELATEDTABLE returns a sub set of the matching data from the many side of the relationship. In our DAX query ,we used COUNTROWS to count the number of rows in filter context from the many side of relationship.

Lets take another example wherein we want to find some detailed metrics. For example find the total quantity purchased by a given customer.

By using RELATEDTABLE with SUMX, we can iterate over related rows and aggregate values from the many side of the relationship. 

DEFINE
MEASURE Customer[SalesQuantity] =
    SUMX(
       RELATEDTABLE ( Sales ),
       CALCULATE(SUM(Sales[Quantity]))
    )

EVALUATE
SUMMARIZECOLUMNS(
    Customer[CustomerKey],
    "Sales Quantity", [SalesQuantity]
)

Can we use RELATEDTABLE instead of CROSSFILTER ?

Now to the most important aspect of the article.

Is it possible to use RELATEDTABLE as a substitute over CROSSFILTER ? Lets find out.

We know that there exist 1:M relationship between Product → Sales and also between Customer → Sales and that the filter flow is unidirectional. So what if we could run a iterator on each CustomerKey in the Customer table that returns a count of matching rows from the related table i.e. Sales table.

We then run the iterator under a measure and then under the filter context of Product, return the count of each customer for a product. Something like this: 

DEFINE
    MEASURE Customer[CustomerCount] =
        COUNTX (
            VALUES ( Customer[CustomerKey] ),
            COUNTROWS ( RELATEDTABLE ( Sales ) )
        )

EVALUATE
SUMMARIZECOLUMNS (
    Product[ProductKey],
    'Product'[Brand],
    'Product'[Product Name],
    "Customer Count", [CustomerCount],
    "Sales Amount", [Sales Amount]   
)
ORDER BY ( 'Product'[ProductKey] )

and we get the exact matching results as the one we got while using CROSSFILTER.

So our assumption was indeed correct.

For brevity, lets filter both the result set on Brand “Contoso” and some random product name to validate the query output.

I have filtered the query on ProductName “Contoso Telephoto Conversion Lens X400 Silver“

Using CROSSFILTER : 

DEFINE
MEASURE Customer[CustomerCount] =
    CALCULATE (
        COUNTROWS(Customer),
        CROSSFILTER(Sales[CustomerKey],'Customer'[CustomerKey],BOTH)
    )

EVALUATE
FILTER (
   SUMMARIZECOLUMNS(
   Product[ProductKey],
   'Product'[Brand],
   'Product'[Product Name], 
   "Customer Count", [CustomerCount],
   "Sales Amount", [Sales Amount]    
) ,'Product'[Brand]= "Contoso" && 'Product'[Product Name] = "Contoso Telephoto Conversion Lens X400 Silver")

Using RELATEDTABLE : 

DEFINE
    MEASURE Customer[CustomerCount] =
        COUNTX (
            VALUES ( Customer[CustomerKey] ),
            COUNTROWS ( RELATEDTABLE ( Sales ) )
        )

EVALUATE
FILTER (
SUMMARIZECOLUMNS (
    Product[ProductKey],
    'Product'[Brand],
    'Product'[Product Name],
    "Customer Count", [CustomerCount],
    "Sales Amount", [Sales Amount]   
)
,'Product'[Brand]= "Contoso" && 'Product'[Product Name] = "Contoso Telephoto Conversion Lens X400 Silver")

So both the outputs are indeed matching.

You can try different combinations of filters to validate the outputs.

Conclusion

In this article I have explored into the details of CROSSFILTER and RELATEDTABLE functions and highlighting the scenarios where each of these functions can be useful. I also examined whether these functions can be used interchangeably in certain cases.

From the performance standpoint, I would assume that CROSSFILTER definatly has an edge over RELATEDTABLE given that we are iterating over the rows that can introduce overhead over large datasets when using the RELATEDTABLE function, though I haven’t tested the performance metrics.

But surely recommend using CROSSFILTER as it is more easy to understand and might perform well over large sets of data.

Thanks for reading !!!