NEED HELP ALLEXCEPT function

Ark1 

The reason the measure is delivering unexpected results is to do with the fields you are using in the visual vs. the table reference in the ALLEXCEPT function.

See the following example Both channel and item come from dimension tables. You can see the rendition of different ALLEXCEPT expressions (NB: 'Sales' is the fact table and Dim Item is a dimension table to the 'Sales' table)

1)

Sales ALLEXCEPT Item = CALCULATE([Sum of Sales], ALLEXCEPT(Sales, Sales[Item]))

2)

Sales ALLEXCEPT Dim Item = CALCULATE([Sum of Sales], ALLEXCEPT('DIM Item', 'DIM Item'[Item]))

3)

Sales Allexcept (DatDim) = CALCULATE([Sum of Sales], ALLEXCEPT(Sales, 'DIM Item'[Item]))

which gets you this:

As you can see, if you are using dimension tables as the filter contexts, you need to write the expression as ALLEXCEPT(FactTable, DimTable[DimField).

If, however, the filter context are fields from the fact table (so not from dimension tables), you need to write ALLEXCEPT(FactTable, FactTable[field)).

So basically you must always refer to the Fact Table as the table you wish to remove the filters from, and  use the field you are using as a filter context in the visual as the column you wish to keep the filters on. Whatever the case, you cannot use the dimension table as the table expression, since the ALLEXCEPT will remove the filters from that same  dimension table (when what you need is to remove all the other filters!). Make sense?

Edit: Another way of getting the results is (again depending on which field is used as a filter context in the visual)

is

Alternative to allexcept (Dim Item) = CALCULATE([Sum of Sales], 
                                FILTER(ALL(Sales), 
                                Sales[Item] = SELECTEDVALUE('DIM Item'[Item])))

Alternative to allexcept (Fact Item) = CALCULATE([Sum of Sales], 
                                FILTER(ALL(Sales), 
                                Sales[Item] = SELECTEDVALUE(Sales[Item])))