what is going wrong with my AllExcept()?

[arunbyc]

what is going wrong with my code using AllExcept()

a minute ago

I have three tables with the following columns in my data model:

Store lookup [store_id]

Product_lookup [Product_Id]

and the fact table:

Sales by store:  [Product], [Store_id], [quantity] and [unit_price]

I have two measures with the following code:

Sales = sumx('Sales by Store','Sales by Store'[quantity_sold]*'Sales by Store'[unit_price])

sales_by_City = calculate([Sales],allexcept('Store Lookup','Store Lookup'[store_city]))

(The purpose of the code is to understand AllExcept,. and *not* to simply get sales by store)

My visual has two slicers, one for product and the other for Store Id, in addition to sales by Product and store. 

I thought this code would do the following:

1) It would always ignore the slicer for the Product 

2) Similar to the way fALL() would ignore all of the filter contexts and show Total Sales against all products/All stores, I expected AllExcept(New York) would show the combined Total of NY sales against all products for store location of NY, and similarly combined total of all Long Island City sales when the store location is Long Island.City

Not only is my visual not ignoring the Product filter context, but it is showing the NY sales for *each product* separately and not the combined total for all products in NY. (or Long Island, depending on the store location)

Is my understanding of how AllExcpt() should work wrong or is my code wrong?

[OwenAuger · Accepted solution]

Hi @arunbyc 

When ALLEXCEPT is used as a modifier within CALCULATE, it removes filters on all columns of the expanded table provided in the first argument, except for columns/tables listed in the 2nd argument onwards.

There is no way to use ALLEXCEPT to remove filters from columns of all tables except for specified columns.

So the modifier

ALLEXCEPT ( 'Store Lookup', 'Store Lookup'[store_city] )

1. Removes filters that may exist on all columns of the expanded  'Store Lookup' table (which I assume is just 'Store Lookup' itself if it is a typical dimension and is not on the many-side of any many:one relationships) except retains filters on 'Store Lookup'[store_city].
2. Has no effect on filters on columns of any other table, including 'product'

For that reason, the results you are getting appear consistent with how ALLEXCEPT should work.

Here are some examples of alternative measures that I think would give results close to what you were expecting:

A measure that would ignore filters from all tables but retain any filters on 'Store Lookup'[store_city] would be:

sales_by_City =
CALCULATE (
    [Sales],
    REMOVEFILTERS (), -- or ALL ()
    FILTERS ( 'Store Lookup'[store_city] ) -- retain filters on this specific column
)

A variation that would do the same but retain visible values of 'Store Lookup'[store_city] would be:

sales_by_City =
CALCULATE (
    [Sales],
    REMOVEFILTERS (), -- or ALL ()
    VALUES ( 'Store Lookup'[store_city] ) -- retain visible values of this specific column
)

Hopefully that helps explain the behaviour you're seeing. Please post back if needed 🙂

[arunbyc]

So it seems I got it all wrog. Thanks for clarifying. So, just to get the concept right, shou;ld my code be:

salesBycity =

calculate(

               [sales],

               allexcept(salesByStore, salesbystore[store_id]

               )

on the many side?

[OwenAuger]

Yes, you certainly could use salesByStore as the first argument (assuming that is your fact table), and then specify tables or columns for filters to retain in the second argument.

This would have the effect of removing all filters on columns of salesByStore and related tables related (on the 1-side), except the tables/columns listed.

For the 2nd+ arguments, I would suggest using columns of the 'Store Lookup' table (or the entire 'Store Lookup table) in this case, rather than columns of salesByStore, assuming filters would generally be applied on 'Store Lookup'.

e.g. this to retain just filters on the store_id column

salesBycity =
CALCULATE ( [sales], ALLEXCEPT ( salesByStore, 'Store Lookup'[store_id] ) )

or this to retain any filter from 'Store Lookup':

salesBycity =
CALCULATE ( [sales], ALLEXCEPT ( salesByStore, 'Store Lookup' ) )