Skip to main content

Why And How To Use CROSSFILTER

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. 

Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...
Post a Comment
Read more

Customizing Themes With Advanced Elements in Power BI

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...
Post a Comment
Read more