Filter with IIF and Count MDX

[Hector2005]

Hello Everybody,
hope you are doing great.

Suppose I have a cube which is called [Subsidiary Year Mapping] and relates Year dimension and Subsidiary dimension.
What I want to make a dynamic subset to check if the current year doesn't have any subsidiaries, then return something, otherwise return these subsidiaries.

Why I need to do that, because if there are not any values returned by the subset, I get an error.

I attached my code that has an error for your reference.

Code: Select all

{Filter({TM1SubsetAll(Subsidiary)}
, 

IIF(
Count(

{Filter({TM1SubsetAll(Subsidiary)}
, [Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES")}
) > 0
, 
[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES"

, [Subsidiary].[Code] = "FakeElement"
)

)}

Best Regards,
Hector.

[declanr]

First thing to consider when using IIF is what type of result we will get from the THEN and ELSE. I'm sure you've looked at the MDX Primer for this (as I did a while ago when having a similar requirement) and found the example relating to the IIF being used to decide whether to filter by the top 5 or 10 records.
In that case it was a numeric example; in this case we want to output some text... so we need to enclose the results in quotes.

If you have used StrToMember before you will know that when you use logic to build up a statement e.g. the classic most of us have used:

Code: Select all

StrToMember ( "[}Clients].[" + UserName + "]" )

We need to help the system understand that everything we are putting together is supposed to be processed as a single statement; this is us helping to tell the system the order in which it needs to work things out.

The problem is you want to build a set instead of just a member; so you can't use StrToMember... and that is where StrToSet comes in to play. This one works the exact same way as StrToMember but with the purpose of returning a set instead; I don't think its really documented in a TM1 context anywhere but you can get the gist from general MDX documents anyway and then work out how to apply it in TM1.

Long story short I think the below code should get you the result that you are looking for:

Code: Select all

StrToSet (
	"{Filter ( {[Subsidiary].members},"
	+
	IIF (
		COUNT ( {Filter ( {[Subsidiary].members}, 
					[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES"
				)}
		) > 0,
		"[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = """ + "YES""",
		"[Subsidiary].[Code] = """ + "FakeElement"""
	)
	+
	")}"
)

[Hector2005]

First thing to consider when using IIF is what type of result we will get from the THEN and ELSE. I'm sure you've looked at the MDX Primer for this (as I did a while ago when having a similar requirement) and found the example relating to the IIF being used to decide whether to filter by the top 5 or 10 records.
In that case it was a numeric example; in this case we want to output some text... so we need to enclose the results in quotes.

If you have used StrToMember before you will know that when you use logic to build up a statement e.g. the classic most of us have used:

Code: Select all

StrToMember ( "[}Clients].[" + UserName + "]" )

We need to help the system understand that everything we are putting together is supposed to be processed as a single statement; this is us helping to tell the system the order in which it needs to work things out.

The problem is you want to build a set instead of just a member; so you can't use StrToMember... and that is where StrToSet comes in to play. This one works the exact same way as StrToMember but with the purpose of returning a set instead; I don't think its really documented in a TM1 context anywhere but you can get the gist from general MDX documents anyway and then work out how to apply it in TM1.

Long story short I think the below code should get you the result that you are looking for:

Code: Select all

StrToSet (
	"{Filter ( {[Subsidiary].members},"
	+
	IIF (
		COUNT ( {Filter ( {[Subsidiary].members}, 
					[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = "YES"
				)}
		) > 0,
		"[Subsidiary Year Mapping].([Year].CurrentMember, [Subsidiary Year Mapping Measure].[Select]) = """ + "YES""",
		"[Subsidiary].[Code] = """ + "FakeElement"""
	)
	+
	")}"
)

Dear Declanr,
Hope my message finds you strong as usual . First of all, Can't find any word of appreciation for what you did.

Hahaha Your answer is correct 100%, I did what you said literally. Also the MDX works as expected.

Again thank you so much. Could I ask about good reference in MDX with TM1?

Best Regards,
Hector.

[declanr]

At the moment the MDX primer is a great starting point.
Once you’ve got your head around that and the basics I find you have an idea of what you are looking for googling tends to take you to the standard Microsoft MDX docs which can then be a pointer in the right direction - for example I guessed that StrToSet would exist based on having used StrToMember following the primer.

I also think that it’s good to get a grasp of what the member unique name is; so although in the subset editor I see the result of “Element” in reality the MUN (Member unique name) is [Dimension].[Element]
And since the introduction of alternative hierarchies the MUN is more aligned with its true origins since you can have [Dimension].[HierachyA].[Element] being different to [Dimension].[HierarchyB].[Element].

Cognos Analytics (Cognos BI) also used MDX in a “purer” form that we are now more akin to in Planning Analytics - I suggest enabling the “view MDX” option in workspace views. When you have that the Cognos documents can also come in handy.