cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

AllExcept is messing up filter

Here is my scenario (simplified):

I have a reference table: TableA

 ID description A this is item A B this is Item B C this is Item C

And a detail table: TableB

 Item ID Category Quantity A Bikes 1 A Bikes 1 A Cars 1 B Bikes 1 B Cars 1 C Cars 1 C Cars 1

There is a one:many relationship between TableA->TableB on the item id.

Here is what I want:

• first filter Table B by Category = "Cars",
• but then add a measure that sums all of the quantities when Category = "Bikes"
• include item description from TableA

I tried the following measure, and then adding description but it seems like calculate (or allexcept) is messing up filter context when I try to drag in "description" from TableA.

Bikes Quantity = CALCULATE(SUM(Table2[Quantity]), ALLEXCEPT(Table2, Table2[Item ID]), Table2[Category]="Bikes")

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

Re: AllExcept is messing up filter

You can get your measure to work as well. You just need a few filters on the category. They do it in a slightly different way but you can get to what you want.

3 REPLIES 3
Senior Member

Re: AllExcept is messing up filter

you could try this

`Bikes Quantity 1 = CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes"))`

But you would need to include the Quantity column for it to show and it would be blank rather than 0 which isn't ideal.

you could do it with a slightly different formula

`Bikes Quantity 1 = if(ISBLANK( CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes"))),0, CALCULATE(SUM(TableB[Quantity]), filter(ALL(TableB),TableB[Item ID]=max(TableB[Item ID]) && TableB[Category]="Bikes")))`

Set category to be "First"

Frequent Visitor

Re: AllExcept is messing up filter

@gooranga1Thanks for the query, seems to be working! Any chance you could go into some detail as to how this is working (what's actually happening?)

Senior Member

Re: AllExcept is messing up filter

You can get your measure to work as well. You just need a few filters on the category. They do it in a slightly different way but you can get to what you want.