The AutoExist Devil in your Power BI Data Model

I have encountered a situation in a report in which a matrix shows an incorrect value while a newly created "debugging" matrix on the same page shows the correct one in a measure that is using REMOVEFILTERS().

Luckily, this kind of problem has been discussed on MS community forums and Stackoverflow. One of these sources identified the feature/bug that is causing it, which is called auto-exist. Please take a look at the SQLBI article and optionally read the comments as well! RedditLink, p3adaptiveLink

On YouTube I found this video in which the presenter shows the problem with an example then names it "autoexists devil". It is a really fitting name as this problem is very good at hiding from us and extremely annoying.

The other video I found is from SQLBI and it's about 45 minutes long so I went to the gym to watch it while using the rowing machine.

In case you don't want to click any of these links, the problem in a nutshell is that the engine that Power BI is using to calculate results is trying to optimize by checking the existing combinations of the slicing/filtering columns for example continents and countries. In certain cases when you use the ALL() or REMOVEFILTERS() to calculate a percentage, it fails to remove the existing filters after this optimization took place if two or more columns from the same table are used in slicers.
If those columns are from the fact table that increases the chances of encountering this problem, but it can happen with columns from dimension tables as well. This problem only occurs in Power BI since Excel sends MDX queries which language handles this optimization differently. Consequently, you may see different numbers from Analyze in Excel and Power BI.

Based on the information and mainly the comments in the SQLBI article, I have the following conclusions:

Preventive steps:
1. If your transaction table contains columns you would use in a slicer,
it is a good idea to create dimensions for them denormalizing your data model.
2. If you have a large dimension, you could split it to two smaller dimensions by topic
for example Bank location -related columns such as country and Bank Category such as commercial.
Reactive steps:
1. Create a hidden copy of the existing dimension table that you can use in your calculations splitting the columns used in slicers in your report between the two of them.
2. Create a hidden table with the columns you intend to use in the REMOVEFILTERS(), connect it to the dimension in a "snowflaking" manner.
3. Check if ALLSELECTED() is not producing the problem and see it that's good for the business scenario.
4. Write a blog post about it and ask others for advice

Leave a Reply

Your email address will not be published. Required fields are marked *