[solved] dimensional filter function for multiple dimensions

[charon]

Hi Everyone,

i have a question concerning dimensional functions (e.g. in a dmr modell).

Please imagine a crosstab, the rows are facts, the columns some prompt based filtered dimensions.

                  D_Time                     D_Region                 .....                     
Fact 1
Fact 2
Fact 3

Time e.g. is filtered by 2 prompts, one for month and one for year, so i can filter the column for a mont in a special year. IM using the function
"currentmeasure within set(filter([D_time].[year]; [D_Time].[year] = ?year?)
Works well so far...

Now i want to create a calculation, for example

(Fact 1 filterered for year 2012) - (fact 1 filtered for year 2011) / fact 1 (filtered for year 2012 AND Region)
=> So i want to use several filter (and dimensions) in one expression...is that possible with the normal filter function? Do i have to create a 'case-when' Statement like
case region
when usa then (Fact 1 filterered for year 2012) - (fact 1 filtered for year 2011) / fact 1 (filtered for year 2012 AND Region)

I hope there is (an easy) way to solve that challenge..,
any ideas are welcome, thx and cheerz :P

[Greg]

What you're looking for is the value at an intersection or a tuple. 

For example you could implement your calculation as:
tuple( [Fact1], [2012] ) - tuple( [Fact1], [2011] ) / tuple( [Fact1], [2012], [Region X] )

The arguments to the tuple function are members from different hierarchies.  You could prompt for the year member if you want.  E.g. [D_time].[year]->?year? and then use a relative time function to get the previous year member.

Hope that helps.

[charon]

Hi Greg,
ty for the fast reply :)

I have tried the tuple functions as well, but as far as i know / learned the tuple function just gives you (as you already mentioned)  an intersection.
When i say i need to create a calculatoin, i mean a new COLUMN for all measures in the rows...so i really need to find  a way to filter the set with more then 1 filter expression in order to achieve my goal.

The worst case would be to set the cellsproperty to "define content = Yes", then create singletons and use a tuple function for every single fact...but besides the fear of a bad report performance i also dont want to make this effort, because there are several (bout 25) facts...

Still, ty, and further ideas are welcome :)

[Greg]

Why can't you just add a Query Calculation as a column in the crosstab and then use currentMeasure in the tuple functions?

Maybe it would help if I could see a mockup of what you're looking for with sample data.

[charon]

hi,
sry for the late reply. it worked the way you guys suggested. thx a lot for helping out and have a nice day :)
cheerz