We have three planning levels and want to be able to filter based upon an attribute of one to show all three.
L1 - Client
L2 - Department
L3 - Product
The attribute we want to filter by is Territory, which is determined based upon the L2 Department Level.
I think that this is a dimensionality issue and there is not a solution, however wanted to see if someone else has encountered as similar situation and created a resolution.
Example:
Client Department Product Territory
ABC Corp Central, West
Dept1 Central
Prod1 Central
Dept2 West
Prod1 West
Client/Department/Product would be using the L3 List. A client can have multiple departments so is not limited to a single Territory, a Department can have multiple Products, but Products gets the Territory from the Department. We cannot use the Summary method of First or Last due to Clients can have multiple Departments - it would result in missing at least half of the instances depending on which is chosen (more if a client had activity in more than two Departments with different Territories).
We have managed to use x[TEXTLIST:y] to get the values from L3 Product to accumulate at L1 Client. However cannot get the L2 Department to have a value, nor have a single line item that would have these values for all three planning Levels, so that a user could filter on that single line item.
In this example - the user would filter Territory by Central and be able to see ABC Corp, Dept1 and Prod1 that had Central.