Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

AllExcept is messing up filter

Here is my scenario (simplified): 

 

I have a reference table: TableA

IDdescription
Athis is item A
Bthis is Item B
Cthis is Item C

 

 

And a detail table: TableB

Item IDCategoryQuantity
ABikes1
ABikes1
ACars1
BBikes1
BCars1
CCars1
CCars1

 

 

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

 

correct and incorrrect.PNG

1 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.

 

measure4.PNG

View solution in original post

3 REPLIES 3
gooranga1
Power Participant
Power Participant

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.

 

measure1.PNG

 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"

 

measure3.PNG

Anonymous
Not applicable

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

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.

 

measure4.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.