Is there a way to use DimensionOverride formula with multiple selections?
I was hoping you could help with a challenge I am facing using the EPM for Excel. So within our Model we have the Entity and Employee dimensions. Each Employee member has a property that ties to Entity. Currently in one report when a user selects the Entity, we have a =EPMDimensionOverride( formula to only select Employees who have that Entity as a property. It works fine for one Entity, but is it possible to do something similar if multiple Entities are selected? I would want it to look something like the image below, where the Employee Dim is nested inside the Entity Dim.
Any advice is appreciated.
Akos Beres replied
If this is a report, you have a few options depending on your data
- select all entities in your second column in your row axis and hide\remove zero values
- if there is only one employee\entity combination, put the employee into the row axis, put entity to the page axis and hard code it to All entities. Turn on Local member recognition in Sheet options, add a row next to employee and insert a local member that would pull the entity like in the example below (where cost centers are on the rows and we are pulling in profit centers assigned to them as a local member) Since there is only a single record associated with each employee\entity,the report will show the correct results. This solution would not work if there were multiple entity records associated with a single employee)
Hope this helps!