ALL DAX function isn't working

[Shashanka_Rao]

Hi all,

I want to create 2 bar graphs. One showing the regional split of sales by shipping method in legends and must respond to the selections in Sub-Category slicer.

And, the 2nd bar graph shows regional split of sales by shiping method in legends and must not respond to the selections in Sub-Category slicer.

The measure for the first bar graph is a simple sum of Sales.

The measure for the second bar graph is as below: 

regional sales all = CALCULATE(SUM(Orders[Sales]),ALL(Orders[Sub-Category]))

The idea is to ignore any selections in the Sub-Category slicer using the ALL() DAX function.

Both the bar graphs works as expected, until one of the selected Sub-Category from the slicers doesn't have all the shipping methods.

In that case, the corresponding shipping methods for the second graph changes instead of staying constant how it's expected to.

Please refer the images below. Images 1 is BAU. Images 2 and 3 show the second bar graph working fine for selected Sub-Categories Accessories and Binders.

But, Image 4 shows selected Sub-Category Bookcases. It is missing the 'Same Day' delivery for the first bar graph, But also the same is missing for the second bar graph. I want the second bar graph to stay as they are in teh images 1,2 and 3.

Can someone please help?

Thanks in advance.

Shash

Working BAUSelected Subcategory working okaySelected Subcategory working okayEncircled missing shipping type

[RossEdwards]

If you change the ALL function to the whole table, does this resolve your issue?

i.e. "ALL(Orders)" instead of "ALL(Orders[Sub-Category])"

I'm wondering if you have some filtering on other columns that need to be removed from the context.

[Shashanka_Rao]

Hi Ross, Thanks for your response. I tried your sugestions, but it didn't seem to work. The aforementioned issue persists. There are no known filters as you can see in the attached image.

link to Dataset is HEREif you wish to have a go. I'm trying to recreate a report that has been done on Tableau.

It was pretty straight forward on Tableau.

Any help is appreciated.

[lbendlin]

is your Sub-Category column sorted by another column?

[Shashanka_Rao]

No, it is used just as it is in the dataset. BTW, this is all just a single table.