topic Re: ALLEXCEPT but keep a set filter as well in DAX Commands and Tips

ALLEXCEPT but keep a set filter as well

lucazanzi — Tue, 22 Nov 2022 17:32:52 GMT

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?

Re: ALLEXCEPT but keep a set filter as well

tamerj1 — Tue, 22 Nov 2022 18:41:26 GMT

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] )

Re: ALLEXCEPT but keep a set filter as well

FreemanZ — Tue, 22 Nov 2022 21:51:43 GMT

you may also try

Measure =

CALCULATE (

SUM ( Data[Stock] ),

ALL ( Data),

VALUES(Data[City])

)

Re: ALLEXCEPT but keep a set filter as well

v-yinliw-msft — Wed, 23 Nov 2022 06:24:13 GMT

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.