ALLEXCEPT Confusion

[chongsm2867]

Hi,

I have an issue with ALLEXCEPT. I have a factinternetsales (fact) table with relationship to a few dimension tables (calendar, customer, product, promotion). Slicers were added from calendar (year), customer (Englishoccupation) and promotion (promotionkey) tables, setting up to test the behaviour of ALLEXCEPT and displaying the value through a card visual : 

Measure: 

AllExcept = CALCULATE(

    SUM(FactInternetSales[SalesAmount]),

    ALLEXCEPT(FactInternetSales, FactInternetSales[SalesTerritoryKey]))

Reference: Eg_1

ALLEXCEPT.pbix

From what I know (i could be wrong), ALLEXCEPT will respect the filters from dimension tables but remove any filters directly applied to the fact table. This means when I select different values from the dimension table slicers, the measure value will change accordingly. But this is not happening, the measure value remain static no matter what values were selected from the slicer. Am confused because I expect the measure value to change but this is not happening! 

Next, I have another measure but this time I included a filter function, as follow:

AllExcept with Filter = CALCULATE(

    SUM(FactInternetSales[SalesAmount]),

    ALLEXCEPT(FactInternetSales, FactInternetSales[SalesTerritoryKey]),

    FILTER(FactInternetSales, FactInternetSales[SalesAmount] > 100)

)

Reference: Eg_2

ALLEXCEPT.pbix

The opposite happens! The measure values changes in response to the slicer value.

So what's wrong with my formulas. Any advice would be appreciated.

Thanks!

[lbendlin · Accepted solution]

Ideally you should not filter fact tables. You filter dimension tables, and the data model does the rest of the work for you.   If you filter fact tables you risk getting wrong results if that filter interferes with a dimension filter.

ALLEXCEPT is a very blunt instrument (similar to ALL) and should be used sparingly.  REMOVEFILTERS is much more gentle.