ALLEXCEPT but keep a set filter as well

[lucazanzi]

Hello everyone,

This has probably been asked before with a different phrasing, so thank you and apologies in advance!

Let's say my data looks like this:

Item	Store	City	Stock
A	High Street	London	3
A	Low Street	Vancouver	7
A	Main Street	Boston	3
B	Jubilee Street	Toronto	5
B	Fun Street	Rome	7
C	Low Street	Vancouver	3
C	High Street	London	4

And my report shows the above data in various visuals (ex: Item count by City, number of stores by City, Distinct products by store, total stock by store/city, etc...).

I would like to create a Measure that:
1) SUMS the Stock
2) Item == A
3) Filters based on City if selected (ie: if you click on a City in a visual, the Measure is affected)
4) Does not filter based on anything else (ie: if you click on a Store in a visual, the Measure is not affected).

I suspect there's an ALLEXCEPT involved, but I have not been able to get it to work. Any hints? 
 

[v-yinliw-msft · Accepted solution]

Hi @lucazanzi,

You can try this method:

New a measure:

Total =
CALCULATE (
    SUM ( 'Table'[Stock] ),
    ALLEXCEPT ( 'Table', 'Table'[City] ),
    FILTER ( 'Table', 'Table'[Item] = "A" )
)

And the result is:

Hope these help you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

you may also try

Measure =

CALCULATE ( 

    SUM ( Data[Stock] ), 

    ALL ( Data), 

    VALUES(Data[City])

 )

[tamerj1]

Hi @lucazanzi 

yes you are right. You csn use

=
CALCULATE ( SUM ( Data[Stock] ), ALLEXCEPT ( Date, Data[City] ) )

Alternatively, you can replace ALLEXCEPT with REMOVEFILTERS ( ), VALUES ( Date[City] )