A relationship between two tables can be unidirectional or bidirectional. When it is unidirectional it filters from one side to many side, not the other way around. If we need to filter from both sides, we can make the relationship bidirectional but it might come with a heavy cost. Having too many bidirectional cross-filter in a data model creates ambiguity between relationships and it might give us wrong results in the end. If this sounds Chinese to you, look at the modal below:
On this modal there two paths to reach from Calendar Table to Location Table. When there are multiple paths between tables, Power BI might find the right way to propagate filter or it can't, thus give us wrong results with our calculations without even complaining about relationships. That's why bidirectional cross-filter relationships can be dangerous and we should stay away from them. But how? What if we need to filter from both tables which have unidirectional filters. In this situation, we can actually create a virtual bidirectional cross-filter relationship by using DAX. The function we need for that is called CROSSFILTER.
Let's say we want to see the number of orders which is on the Orders Table based on Country which is on the Location Table. Right now it's not possible to get the right number because the filter doesn't propagate from one table to another. Because relationships go in the direction of arrows. In this situation, we have two options. First one, we can set relationship bidirectional between Orders and Fact Sales tables, so that it can reach to Orders Table. But as you see in the first example, when we have a complex modal, it might create issues after some time. The other option is using CROSSFILTER function in DAX to create a virtual relationship. Microsoft's definition for the function is: "Specifies the cross-filtering direction to be used in a
calculation for a relationship that exists between two columns." And the syntax is:
CROSSFILTER(<columnName1>, <columnName2>,
<direction>)
No of Orders measure doesn't give us the right result as we expected because the direction of the relationship doesn't allow that. On the other hand, the measure with CROSSFILTER function gives us the right result by creating a virtual bidirectional cross filter. I didn't change anything on the modal, it stays like in the picture. Cool right? If you are thinking about using this function, keep in mind that the third parameter on the syntax represents cross filter direction and it can be set as one, both, or none. Other than that, it can be used only with functions like CALCULATE, CALCULATETABLE, etc. which take a filter as an argument.
Comments