For Power BI data modeling, star schema is recommended by gurus like Marco Russo, Alberto Ferrari all the time. The other day, I have understood the importance of using star schema one more time. You know what they say: Experience is the best teacher. Better than Marco Russo and Alberto Ferrari. Definitely.
This model looks more like Dalton Brothers than snowflake schema but it's called snowflake schema. Not to be confusing, I removed other dimensions which are not relevant to our subject. As you see there are one to many relationship between all of them and the filtering direction goes from high granularity to low granularity like it's supposed to.
In the first table, the Sub-Category column coming from the Products Sub Category table and the Total Sales measure is the sum of the sales column. With All Sub-Category measure, I remove the filter on the Products Sub Category table by using the ALL function. No problem so far. The problem appears in the second table. My All Sub-Category measure, which removes the filter from the Products Sub Category table also removes the filter from the Category table. It doesn't filter the Category table either, despite the fact that I haven't added the Category table to ALL part of my measure. This is happening because of this: To filter the sales on the Orders table, the Category table filter should pass through the Products Sub Category table and the Products table to reach the sales table. The thing is, it can't pass the filter through the Products Sub Category table, because I removed the filter on that one with my measure. I'd like to emphasize that it's not a filter direction problem. I have tested all different filtering direction combinations in case I might be missing something. That's not it.
The measure can filter the Products Table as you see because it doesn't have to pass through the filter from a table where the filter is removed. The problem only arises when the filter has to pass through a table where the filter is removed.
How to solve this? Somehow, I should find a way to tell Power BI to remove the filter only from the Product Sub Category table, and after that, implement the filter on the Product Category table.
It can be done by adding the FILTER function, as the third part of the parameter. With Filter Category measure, I still keep the filter on the Products Sub Category Table but after that, I implement a filter on the Products Category table based on the Total Sales measure.
And the moral of this story is not to use the FILTER function after the ALL function. It's using star schema instead of snowflake design. Combining FILTER and ALL solves my problem, but when we look at the big picture, this may not be the best practice, because we may not notice this problem since it doesn't come with an error message or something. Other than fixing issues that may not be noticed, I prefer to focus on not giving any room for this kind of issue from the beginning. And it can be done by converting snowflake design to star schema. You might be worried about repeating data with star schema since with snowflake design we have only unique values, but it's not a problem for Power BI. Because the Vertipaq engine compresses the columns to only its unique rows. Additionally, star schema provides a better user experience than the snowflake design, because it's more difficult to grasp all different hierarchy levels and filtering according to that for users.
Comments