Skip to main content

Handling Inactive Relationships

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.

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

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

Calculating Running Total with Quick Measure in DAX

It's been a while since quick measures were introduced and it might be tricky to decide whether to use them or not. What I have noticed recently is calculating running total with quick measure might not provide the best user experience because it simply doesn't filter explicitly, despite ALLSELECTED function is part of the measure.     This is the quick measure to calculate the running total and it can be seen that ALLSELECTED function is part of the equation. As a result, I got the impression that when I explicitly filter, the measure will retain all the explicit filters but that’s not what happened. This is how it looks like before I use any explicit filter.   Everything looks perfect right. What until I filter 2017 and 2018.     As you see two numbers don't match because quick measure adds up sales of 2016 despite I didn't select 2016 on year slicer and despite quick measure uses ALLSELECTED function. This is the result we were supposed to get if ALL function...
Post a Comment
Read more