MDX Issues with 2 row dimensions in Active form

[sg9489]

Hi Folks,

Query: Unable to load MDX dimension if dependent on another MDX dimension in the same sheet.

Details:
Attached is the active form of Cube 2 with 2 dimensions in the title, 2 in rows and 1 in column.
Dimensions in rows are:
KPI
CAtegory

Row dimension KPI is calculated as MDX depending on the values of title dimensions(Centre & Period).
A Lookup cube has the information of the dependency of the above measures.
MDX: "{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(KPI)})},0)},
[CUBE2.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"])='Y')}"

Above MDX is working fine and updates as the parameters are changed/

Row dimension Category will also be calculated as MDX depending on the values of title dimensions(Centre & Period) and KPI.
A Lookup cube has the information of the dependency of the above measures.
MDX:"{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(KPI)})},0)},
[CAT.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"], [KPI].["&A15&"])='Y')}"

This MDX does not provide any elements, if the KPI is dynamically addressed, but works if it is hard coded.
I also tried using named range for the KPI lookup, but that does not work either.

Please advise if I am doing anything wrong and suggest solution.

[Mark RMBC]

Hi,

It would be nice to know which dimensions are in your 2 .lookup cubes.

Just looking at your category mdx statement (the second one), there is no reference to the category dimension in the mdx?
It would appear to be pointing at the KPI dimension?

Incidentally: I do not think what you are trying to do is possible with mdx anyway, as I presume your category mdx is within the TM1RPTROW and you expect the mdx will pick up the value of a15, a16, a17 etc and return multiple mdx expressions from the single TM1RPTROW.

Regards,
mark

[sg9489]

Hi Mark,
Thanks for the reply.

There are 2 lookup cubes:
1. CUBE2.LOOKUP
Dims: KPI, CENTRE, PERIOD
2. CAT.LOOKUP
Dims: CATEGORY, KPI, CENTRE, PERIOD

My bad, I have mentioned it wrong, but it is as below in the actual TM1 environment:
MDX:"{FILTER({TM1FILTERBYLEVEL({HIERARCHIZE({TM1SUBSETALL(CATEGORY)})},0)},
[CAT.LOOKUP].([CENTRE].["&B10&"], [PERIOD].["&B11&"], [KPI].["&A15&"])='Y')}"

Is there any workaround for this one?

[Mark RMBC]

Hi,

I can't think of an mdx workaround.

If these forms are NOT being consumed in TM1 web you could insert a column to DBRW to the category lookup cube and apply custom validation (Unfortunately custom validation does not appear to work in TM1web) on whether the DBRW returns Y, so only allow none zero where DBRW is Y. This would also depend on how many categories you had, because you would be basically returning every category for each KPI.

If they are being consumed in TM1web then apply some sort of conditional formatting.

The only other thing that springs to mind is concatenating KPI and Category in a dimension and using that.

Maybe others have a better suggestion?

regards,
Mark