So after spending the last four months developing my own custom Fabric web app juggling between C#, .NET Core, Razor, JavaScript, Asynchronous programming and REST APIs, I’m now gradually shifting my focus back to regular blogging on topics related to Microsoft Fabric.
To get started I for some unknown reason have decided to blog on few topics in DAX. It's been about nine months since my last post on DAX, which explored context transition in DAX.(You can find out that blog here)
In this blog we will closely delve into details of two powerful DAX functions CROSSFILTER and RELATEDTABLE.
Can they be used interchangeably ? Lets explore and find out.
But before that, lets first refresh our understanding of CROSSFILTER and RELATEDTABLE functions.
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 from Product → 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 from Product → Sales is unidirectional.
Filter flow from Sales → 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 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]
)
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")
Both the outputs are 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 !!!
