Skip to main content

Power BI

New

Auto-exist - change Autoexist behavior in Power BI to be more intuitive

Vote (6) Share
Jakub Dušek's profile image

Jakub Dušek on 25 Jul 2023 13:00:09

I have recently run into problems with Power BI Auto Exist feature - you can see example here: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/ALL-does-not-remove-filter/m-p/3345395 where the ALL() did not work intuitively and as expected


article explaining the behavior is here: https://www.sqlbi.com/articles/understanding-dax-auto-exist/


I find this auto exist behavior very confusing, because it sometimes returns incorrect results of calculation.

You can run into problems when you do not use a proper Star Schema.


I believe that Power BI is indented to be used by both educated data professionals and regular business users with no knowledge of what a "star schema" is.

Even with the latest introduction of MS Fabric, Power BI steers even closer to citizen developers usage.


I often need to create just a small, quick Power BI report, with only 1 flat table, I do not wont to create a proper Star Schema, because the model is just a few hundreds of rows, it is jus an unnecessary work to create a dim table for every column I might wan to filter on.


another reason for this auto exist behavior change request is that in Excel Data Model the behavior is different. in Excel you get the correct expected result that is different from the Power BI Auto-exist result, with the same measure code.


there should be a parity between all products that use DAX formulas, same DAX code should return the same expected result in both products.


I understand the query optimization reason behind Auto-Exist, but it also creates a lot of problems.

Not every Power BI report has millions of rows to benefit from it. There are significantly more small reports that only encounters the mentioned problems due to Auto Exist.


I believe Power BI and DAX behavior should be intuitive, usable by both data professionals and regular business users. Building reliable reports should be possible without extensive knowledge of Star Schema, reports and measures should work intuitively on just one data table.


Please change Auto Exist behavior in DAX so that it does not produce these unintuitive incorrect results

Comments (2)
Jakub Dušek's profile image Profile Picture

Jakub Dušek on 17 Oct 2024 15:36:05

RE: Auto-exist - change Autoexist behavior in Power BI to be more intuitive

this seems to be completed with the recent Power BI October update of Value filter behavior (preview)

Jakub Dušek's profile image Profile Picture

Lawrence Dwight on 31 Oct 2023 13:17:26

RE: Auto-exist - change Autoexist behavior in Power BI to be more intuitive

For citizen (and even Pro) developers this is a problem...