Ignore multiple filters

[Anonymous]

Hello,

I am trying to count all the stores in a speficic market.  However, when i drill down to Brand, for example, the total number of stores changes refelcting all the stores that have ordered that brand.  I want to be able to ignore stores specific to that brand and look at the overall stores in the market. 

I can use the All function, which fixes the problem.  However, then when I drill down futher to package, the number will change.  

Is there a way I can use multiple "All" filter option to ignore filters on two different columns/tables.

Thanks,

[amitchandak]

@Anonymous , refer all expect, remove filters

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://docs.microsoft.com/en-us/dax/removefilters-function-dax

[nandukrishnavs]

@Anonymous 

Using ALL() you can specify the table or columns one by one. 

https://www.sqlbi.com/blog/marco/2010/04/05/all-allexcept-and-values-in-dax/

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

I tried that, but it won't still work:

Original

CALCULATE(sum(Sales[VOLUME]),filter(all(Package),[Vol (Raw Cases)]>0),'Business Segment'[Segment]="XYZ",Customer[Key Account]="ABC")

The filter option will not allow me to add another ALL

[v-lili6-msft]

hi  @Anonymous 

You could add other ALL function like below:

CALCULATE(sum(Sales[VOLUME]),filter(all(Package),[Vol (Raw Cases)]>0)) , filter(all(Business Segment),'Business Segment'[Segment]="XYZ"), filter(all(Business Segment),Customer[Key Account]="ABC"))

If you still have the problem, please share your sample pbix file for us have a test.

Regards,

Lin

[Anonymous]

Thank you for this.  So I am using the all formula and it seems to be working.  However, the problem arises when I drill through.  Let's say I am counting 100 stores for every package.  If i drill down to the next level, which in this case is brand, the total number of stores is based on the brand/package combo.  

I want to be able to drill through package to the brand but I don't want my count of total stores to be reset.

Thank you in advance.

Best,

Ali

[v-lili6-msft]

hi  @Anonymous 

You may use ALLEXCEPT in the formula, see this document

https://www.tutorialspoint.com/dax_functions/dax_allexcept_function.htm

https://excelkingdom.blogspot.com/2018/08/how-to-use-all-and-allexcept-functions.html

If you still have problem, please share your sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Thank you for this.  Could the issue with the all filter not be working because I'm using differen't tables for the drilldown? The package is in one table, and the flavor/brand is in another table.

Thanks,

Ali