Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
djwitkowski
Helper I
Helper I

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
Helper I
Helper I

@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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.