Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In a pivot table I've a dimension field (YEAR_MONTH) and a measure given by an expression with a parameter (MyDim ($1)), I'd like to Pass as parameter a numeric value (MONTH_ID) related to the dimension field, I tought this cold be a solution
$(vMyDim( $(=aggr(only(YEAR_MONTH_ID),YEAR_MONTH))))
but it doesn't work (only works when a single value is selce in YEAR_MONTH)
At The moment I've found a terrible workaround :
if(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)=1,$(vMyDim(1)), if(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)=2,$(vMyDim(2)),0))
Have you any suggestions?
those month year and month_id are connected
is so why don't you just use the field month_id in your expression
The $ expansions occur before the cubes dimensions are expanded for the pivot table, so you will not be able to use a $ expansion that depends on the dimension values.
the field is YEAR_MONTH_ID not MONTH_ID (sorry for the mistake)
I've changed the workaround now is more simple but still ugly
if(YEAR_MONTH_ID=1,$(vMyDim(1)),
if(YEAR_MONTH_ID=2,$(vMyDim(2)),
0))
this expression only works with a single value selected in YEAR_MONTH, for multiple values in YEAR_MONTH the pivot table has no rows
$(vMyDim( $(=aggr(only(YEAR_MONTH_ID),YEAR_MONTH))))
this expression only returns rows in pivot table but the value of the measure is always the same
$(vMyDim(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)))