Hello,
I have a fact table called sales. In it there foreign keys to the following dimension tables;
- Region - this includes the region and selling office
- Product - this includes the product name
- Name - this includes the sales rep
I then have some visuals. Let's say a bar chart that groups everything by region (so sales by region)
I want it to sum everything, but only allow the region filter to work from the filter pane. (note, the reason for this is the filters are report level filters, so i want users to be able to use them on other pages, but not this page).
I wrote the following.
amount_dax1 = CALCULATE(SUM(SALES[amount]),ALL('product'),all('name'),ALLEXCEPT(region,region[Region]))
The ALL function is working as intended (the filters are ignored in the product or name dimension). My challenge is the region and selling office are in the same dimension table, but i can't tell it to allow the region filter but ignore the selling office. Note, for other reasons i can't split the region dimension table into 2 dimensions.
For some reason it looks like ALLEXCEPT doesn't acknowledge the filter pane. Is this true or is my syntax wrong? Is there another option?
Thank you!
