Filter out multiple records based on a single record?

[norrisar]

I tried searching to see if this is covered already, but it is kind of difficult to look up as I'm not even sure how to word it. My question is basically if there is a way to filter out records based on a single data item. For each customer I have, there are multiple records of different types. My user is requesting I remove ALL records if at least one record falls under a certain type. I can't use the filter [DataItem] <> 'Type' because all that does is remove that specific record, leaving the rest for that customer.

I believe my data is using a relational package, but not entirely sure.

For example below, I am looking to remove ALL IDs '123' and '456' because at least one record of each is a type A.
Type          ID            Name
A              123           Taylor
B              123           Taylor
C              123           Taylor
B              789           Mike
D              789           Mike
C              369           Troy
A              456           Ryan
A              456           Ryan
D              456           Ryan


Desired Outcome:
Type          ID            Name
B              789           Mike
D              789           Mike
C              369           Troy

Any suggestions or ideas would be greatly appreciated! Thank you in advance.

[Lynn]

I believe my data is using a relational package, but not entirely sure.

If you only see query subjects and query items in your source pane rather than dimensions with members and levels then it is relational. Another way to check is to see how the data items are referenced. Right click on an item in the source pane and take a look a the properties.

Relational has three: [Namespace].[Query Subject].[Query Item]
Dimensional has four: [Namespace].[Dimension].[Hierarchy].[Level]

Assuming it is relational, then try creating a query calculation to count up the type A items by customer. Drag a query calculation into the query and use an expression similar to below. Call it something like CountTypeA.

Set the aggregation method to none. Drag the item into your layout to verify that it is returning 1 or 0 as you would expect. If so, drag it into the detail filter area and make the condition [CountTypeA] = 0 and set the application property for the filter to After Auto Aggregation.

Code Select Expand


count ( distinct
    case
      when [YourPackage].[YourQuerySubject].[Type] = 'A'
       then [YourPackage].[YourQuerySubject].[Type]
       else null
    end
for [CustomerID]
)


I don't always do things the easiest possible way so perhaps others might chime in with an alternate approach.

[norrisar]

If you only see query subjects and query items in your source pane rather than dimensions with members and levels then it is relational. Another way to check is to see how the data items are referenced. Right click on an item in the source pane and take a look a the properties.

Thanks for the clarification; my data IS in fact relational. I did the method you described, and it seemed to have worked. I'm sending a test copy to my user now to make sure that is what she is looking for. Thank you for your help!

If anyone else comes by this and has another way, I'm always open to learn other methods.