3 Replies Latest reply on Jul 12, 2017 12:06 PM by Iris Wei

# special filter depends on other rows

Hello guys,

I have a question for special filter, please see below picture

invoice number   product code

How to display 0 line if invoice number has product code is 9?

I have tried filter by product code is 9, but only show the 9 line, how to filter to show whole rows if one invoice has product code 9 and 0? Of course my purpose is only show the 0 line.

Thanks

• ###### 1. Re: special filter depends on other rows

Create a calculated field with a Level of Detail calc;

{FIXED [Invoice Number]: COUNT(IF [Product Code] = 0 THEN [Product Code] END)} > 0

Put that on the filter shelf and set it to True

1 of 1 people found this helpful
• ###### 2. Re: special filter depends on other rows

Consider a calc like this:

{ FIXED [Invoice Number]: SUM( if [product code] = 9 then 1 else 0 END) }

this will be computed at the invoice number level, and its value will be on every row in the invoice, and if any row has 9 for product code, a 1 gets added.  If the SUM([that calc]) > 0 then you have at least one 9 in there.  And if your [product code] = 0 and [that calc] > 0 then you have the condition you are looking for.

1 of 1 people found this helpful
• ###### 3. Re: special filter depends on other rows

Yes, it works! Thanks Joe and Tom!