ALL returns different values for categorical & range type slicer

[theDarkPrince]

Hi everyone. This issue has got me stuck for at least a few days now. Any help is appreciated.

If you checked the PBIX file attached, you'll notice that the results being displayed on 2 pages (Bucket filter, slider filter) are different even though the underlying data, DAX measure definitions are exactly same. Only difference being, when using bucket slicer, it returns TRUE value(expected value) but not in case of the slider created using Marks column.

We have explored the ideas of Auto-Exist (explained here) and think that that is what's happening here. However, we're having trouble proving that theory. Also, we examined the underlying DAX query using Performance Analyzer/ DAX Studio. It uses TREATAS() in case of bucket slicer and KEEPFILTERS() when using slider.

It'd be immensely helpful to get to the solution. Is it possible to somehow view the intermediate/[summary]/underlying data after applying slicers, using DAX to check exactly which records are being considered ?

Here is the link to download both file PBIX & Data file (XLSX).

https://drive.google.com/drive/folders/1LxiLIk_k6j1ick0nfbu4ivyDCe57Ukjb?usp=sharing

Please let us know if more information/details are needed.

@Greg_Deckler @amitchandak @Jihwan_Kim @Ashish_Mathur @AlbertoFerrari 

[AlbertoFerrari]

As you figured out, the problem IS auto-exists.

Long story short: when you filter the bucket, the filter is on "<50", and auto-exist does not create problems, because you have the value "<50" in the Physics subject. When you place the filter on the marks, it becomes a filter on values of the column (that is, all the values less than 50) and auto-exist becomes an issue. Under Physics, it will find only (23, 45). The value of 32 that is in Maths is not found. When you remove the filter on the Subject, you remain with a filter on (23, 45) that does not find 32. The same, with the filter on the bucket, produces a filter for "<50", which is a shared value between Maths and Physics.

I know... not super-clear. It is very hard to be clear in just a few lines.

Anyway, I liked the example a lot. With your permission, I am going to record a video about this specific example, as it shows very well some of the best (worst?) DAX shenanigans 🙂

[theDarkPrince]

• Hi @AlbertoFerrari , thanks for taking the time out to go through the file. When I read your response, this is what happened :
• when you filter the bucket, the filter is on "<50", and auto-exist does not create problems, because you have the value "<50" in the Physics subject. -- with you so far . .
• When you place the filter on the marks, it becomes a filter on values of the column (that is, all the values less than 50) and auto-exist becomes an issue. Under Physics, it will find only (23, 45). -- with you so far . . 
• The value of 32 that is in Maths is not found. When you remove the filter on the Subject, you remain with a filter on (23, 45) that does not find 32. -- lost you here!

When you say remove the filter on the subject, you mean to say when we use ALL('Source Data'[Subject]) modifier, right ? But then, since it's leaving out subject records like that, one can say that ALL() is not behaving as one would expect.

Like you said, it's not super-clear. 😅

About using the example for a video, in spirit of community, by all means. 😊 Looking forward to it.

[AlbertoFerrari]

Here it is: Auto-exist on clusters or numbers - Unplugged #22 - YouTube

Have fun.

Alberto

[FrankAT]

Hi @theDarkPrince ,

take a look at the attached PBIX file.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)