Power BI: Totals Not Working? Mind Your Cross Filter Directions

If your totals or other measures for related tables don't seem to be working properly in your Power BI report, it might have something to do with the cross filter direction(s) in your data model. Here's what my situation was and how I fixed it:

I had a simple table visualization that was supposed to be pulling in various summary measures from another related table. Some of these were just basic totals. Now when I applied filters to this table, for some reason the totals from the related tables weren't also recognizing these filters. It was just giving me a total for the entire related table instead of also taking into account the filter. I'm pretty new to DAX and went in circles agonizing over whether I had even created those measures properly in the first place.

My problem had a quick solution: modify the cross filter directions for the relationships between the affected tables. Look at the relationship lines in your data model and you will see a little arrow on them. By default this will just show one arrow, which means it is set to a unidirectional cross filter. Double-click this relationship line to edit it, and in the 'Cross filter direction' drop-down box, select 'Both'. Now you will see two arrows on the relationship line instead of just the one. And now you should notice filters being applied properly in both directions, if this is what you needed.

To avoid this problem in the future, make a habit of intentionally setting your cross filter directions when you create relationships in Power BI.


Post a Comment