Forum Discussion
Context Filter is not working as expected
You need to transform your single-table model in a proper star schema.
Add a dimension for sets of related attributes you want to slice and filter for, and leave the fact table with numbers to aggregate.
Autoexist is applied to columns in the same table, not to columns of different tables. This is why a star schema would work just fine.
At the end, data modeling is quite easy: Star schema all the things.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Most Valuable ProfessionalYou need to transform your single-table model in a proper star schema.
Add a dimension for sets of related attributes you want to slice and filter for, and leave the fact table with numbers to aggregate.
Autoexist is applied to columns in the same table, not to columns of different tables. This is why a star schema would work just fine.
At the end, data modeling is quite easy: Star schema all the things.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Hi AlbertoFerrari . First of all, thanks for sharing you take here & writing in detail about Auto-exist in this article. It seems that not many developers are aware about this behaviour but sooner or later every PBI developer is bound to run into it. I read your article in detail and have a question:
In it, you have used example measures to explain the behaviour. One of the measures uses ALL() modifier. So my question is this - Should one expect the Auto-exist behaviour with other context modifiers as well ? Such as ALLSELECTED(), ALLEXCEPT() and so on. Looking forward to hear from you.
- AlbertoFerrari
Yes. Any modifier that removes a filter is going to be affected by this. Modifiers that add filters, on the other hand, might fight with arbitrarily shaped set. In both scenarios, some borderline cases are a real puzzle to solve.
I see. Definitely something to keep in mind while adding further tables to my Data Model. One more question though (last one, I promise 😅). In the example that you used in the article here, since there are only 9 rows in the underlying dataset, it becomes quite easy to look at the rows one is missing out due to Auto-exist.
However, in the report I'm working on, the Fact table has > 100,000 records (fetched in DQ mode). I am baffled about how to find out exactly which rows/datapoints are not being fetched due to Auto-exist. In your opinion, what would be the best way to figure this out ? Thanks in advance.
P.S.: I'm already using the approach using COUNTROWS() to find the difference in numbers of rows. It's just that, I also wish to find out which records from the Fact table are being left out.
- AlbertoFerrari
Hard to give you any advice here... the result of the query is aggregated, you do not see the individual rows anymore. I think it could be solved for a very specific query, but I do not see a simple (or even complex) solution to the more generic problem. The problem is that the hidden rows depend on the formulas used in the measure, which filter are removed and which are kept.