ALL Function Giving me Trouble

[djwitkowski]

Hello,

I am using the ALL function to track Total # of shipments, regardless of paid status, in a measure called 'Total Loads.' 

• On the 'All Data' tab, I can use the slicer on 'Paid Status' and the Total Loads value remains constant (as expected)
• On the 'Filtered Data' tab, where I have only 1 week's worth of data selected, when I use the slicer on 'Paid Status', the value for total loads changes when I select 'Not Paid', which I don't want to happen and I can't figure out why.

Any help is appreciated!

I have attached a link to my file with sample data:

https://1drv.ms/u/s!AmOKQTJU9UwIogEPl-MSopd7khJ-?e=dF7v42

[djwitkowski]

@daxer-almighty Thank you! I did some research and was able to solve the issue. I created another table called 'Paid Status' with one field and only 2 records: 'Paid' and 'Unpaid'

Using that new field to slice the data now lets the ALL function work as I intended it to.

This article was also very helpful:

Understanding DAX Auto-Exist - SQLBI

[Anonymous]

Of course it works now because the auto-exist does not kick in. If you have a proper star schema, you're much less likely to face the issue of auto-exist (even though you're not completely safe).

[daxer-almighty · Accepted solution]

@djwitkowski 

What you're experiencing is known as the curse of the "auto-exist" feature. Don't be fooled: it's not a feature, it's a bug that Microsoft passes over in silence in the hope that people will just put up with this ugly and dangerous "feature." No, that's not a joke.

Have you noticed that if you set your date slicer to the full range:

the figure DOES NOT CHANGE? Well, that's because you don't have any filters on ship date. As soon as you put them on any column in your table, the formula starts to act weird (and randomly into the bargain). The only solution to this type of BUG is to create a proper star schema, which you should ALWAYS do. I mean always with no exception unless you want to create models that will be buggy and which you will not even be able to recognize as such.

Please learn how to create correct models in Power BI. Then you'll be (almost) free of such ugly bugs.

I could explain in details why this happens... but I don't think there's a point in it. You can google for "auto-exist in DAX" and you'll certainly find the right articles that describe the problem in minute detail.