IIF Mdx in IBM PAW not compiling

[vishalpoddar90]

Hello Everyone,

I am writing an IIF statement in mdx but it is getting failed to compile.

Scenario is based on the selection of version, different measure members should be shown.

Currently tried mdxs

1. IIF( [Scenario].CurrentMember = "Elem1", [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )
2. IIF( [Scenario].CurrentMember.Name = "Elem1", [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )
3. IIF( [Scenario].CurrentMember.Name = [Scenario].[Elem1], [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )

I used Filter too but still its not working. I tried mentioning the hierarchy with dimension name and that is also not working. I also tried writing this in Architect but I had no luck there too.

It would be great if anyone can help me here.

Thanks in advance

[declanr]

Code: Select all

{StrToMember ( "[Dim_Measure].[" +
IIF ( [Scenario].CurrentMember.Name = "Elem1",
   "MeasureA",
   "MeasureB"
)
+ "]")}

Generally speaking, IIF should be inside of other functions that will return either a SET or MEMBER.

Think of it as the results of the IIF are TEXT/String and the engine doesn’t know what to do with it - so even if it has the same name as a set or member - you need to explicitly tell it to treat it as such. Here we do that with StrToMember.

Also worth noting that currentmember will return a Member Unique Name - so if you do a lookup of it you would need to check for [Scenario].[MyMember] but if you use the .Name suffix you can just look for the name itself.

[gtonkin]

Just to add to what Declan posted, you could extend to the following if needed:

Code: Select all

{StrToMember ( "[Dim_Measure].[" +
CASE [Scenario].CurrentMember.Name
	WHEN "Elem1" then "MeasureA"
	WHEN "....." then "Measure..."
	ELSE "MeasureB"
END
)
+ "]")}

Also, to reiterate what Declan mentioned too as it is critical to both IIF and Case. These return either a Numeric or a String - members or sets cannot be returned. You need to build these using the StrToMember.

[vishalpoddar90]

@devclanr @gtonkin
Thanks for the quick reply.

These solutions worked. Now in actual usecase, I need to return multiple measures. As you mentioned it cannot return members and sets, does it mean I have to write multiple StrToMember statements or is there a better way to do it. I have around 50 measures and based on selection of scenario, I need set of members to be displayed.

[vishalpoddar90]

@declanr @gtonkin

Thanks for the solution. I really appreciate it.
After a little search I came across STRTOSET function and it worked like a charm.

[gtonkin]

Glad you sorted it out - have a look at this Reference Guide, may save you some time searching in the future.