Deriving/Swapping MUNs between dimensions

[mickyo73]

Hi All,

I'm working with an SSAS OLAP cube and am attempting to use the value entered in a value prompt to filter a a different dimension. I believe in order to do this I need to use the substitute function with a prompt macro to do so.

I'm having trouble getting the syntax right. The MUN of the dimension the value prompt is based on looks like:

[Project Cube].[Period].[Financial Year].[Fin Month]->:[M8].[[Period]].[Financial Year]].[Fin Month]].&[201512]]]

and the MUN of the dimension I'm attempting to pass this as a filter to looks like:

[Project Cube].[Project Master].[Call Closed Month].[Call Closed Month]->:[M8].[[Project Master]].[Call Closed Month]].&[Jun 2015]]]

This is what I've come up with so far:
#substitute('Period','Project Master',
substitute('Financial Year','Call Closed Month',
substitute('Fin Month','Call Closed Month',
substitute('Period','Project Master',
substitute('Financial Year','Call Closed Month',
substitute('Fin Month','Call Closed Month',
prompt('pPeriod' , 'MUN'))))))) #

My issue is I end up with 2 lots of the [Call Closed Month]] on the right hand side of the MUN where I only need this once. I haven't been able to work out how to only have this once.

Any pearls of wisdom that could be passed my way would be very much appreciated.

Thanks
Mickyo73