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. But when we let Power BI decide which path to take, it might be the wrong one so it's better to check them before creating any measures.
In this example, I want to see the number of items sold by ship date. To be able to do that I need to sum the quantity column which is in the Fact table. Then I need to filter from Orders to Facts by using ship date column. And I also need to change the filtering direction to get the right result.
This is the wrong result that we expect without writing the right measure. First I'd like to show the measure, then I'm going to explain what exactly it does.
What exactly happened there. First of all, I needed to use the CALCULATE function because the USERELATIONSHIP function can only be used in functions that take a filter as an argument, like CALCULATE. To activate my inactive relationship I used it, and then, as the second filter of CALCULATE function, I have changed the filtering direction from single to both, so that I can filter by ship date. And all of these happened virtually. Nothing has changed in my modal.
The thing to remember while using these functions is choosing the right columns for relationships and connections. It creates an error if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.
Comments