AllExcept is messing up filter

[Anonymous]

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

[gooranga1]

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"

[Anonymous]

@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?)

[gooranga1 · Accepted solution]

Hi @Anonymous

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.