I am trying to create a table that is based on EITHER of two dimension columns containing the same value. If at least one record at the more detailed level (Acct Type) contains the value, I want all the records at the higher level (Acct #) to be included in the report.
Attached is my example of Acct records. If either the OFFICE column or ACCT TYPE columns contains US, I want all of the Acct# records to be included in the report.
So if any of the following scenarios is true, I want to see all records within that account:
In my example I have highlighted every record that I expect in the results.
I have not been able to figure out how to include all the records when the OFFICE is not US but at least one of the Acct Types within an account is US. For example in Acct# 835 which has an OFFICE of Europe, I want to see both records that contain the Acct Types of Spain and US so that the total account value is 7.81 (2.31+5.50)