cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djwitkowski
Frequent Visitor

ALL Function Giving me Trouble

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

 

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

@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:

daxeralmighty_0-1629995863087.pngdaxeralmighty_1-1629995906908.png

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.

View solution in original post

3 REPLIES 3
djwitkowski
Frequent Visitor

@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

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
Solution Sage
Solution Sage

@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:

daxeralmighty_0-1629995863087.pngdaxeralmighty_1-1629995906908.png

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.