My Journey with Power BI

My last post was about the CROSSFILTER function and I have decided to add more combinations to it by using the USERELATIONSHIP function. If you don't know the CROSSFILTER function, you can check my last post  Why And How To Use CROSSFILTER . Let's start with an example: On this modal, the relationship between Facts table and Orders table is inactive. I didn't make that relationship inactive, Power BI itself made it automatically. And why did it do it? Let's say I want to count no of Order ID by date. Order ID column is in Orders table, whereas Date column is in Calendar table. To be able to calculate that, I need to propagate the filter from Calendar table to Order table. And I have two paths to propagate that filter. The first one is directly from Calendar to Order, the second is from Calendar to Facts and then to Order table. So which path should it take? This creates ambiguity for Power BI and to solve that ambiguity, Power BI inactivates the second one automatically...

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 a...