MDX - dynamic subset to filter by two other dimensions

[jamesbennett]

This issue has come up before in other threads on this forum, but I'm having difficulty implementing it. I think it's a straightforward syntax error with my MDX but can't work out how to rectify it.

I have a row dimension, Course, which I want to filter by the values of two other title dimensions, Level and Mode. Course has element attributes which match the values of Level and Mode.

I put together this bit of MDX that works fine when selecting any combination of Level or Mode:

Code: Select all

{FILTER(
	{FILTER(
		{TM1SubsetAll([Course])},
		[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name
		)
	},
	[Course].[Mode]=[GLOBAL Mode].CurrentMember.Name
	)
}

However, Level and Mode also have consolidations that I might want to select to show All Levels or All Modes. In this case I do not want to filter by attribute of the Course dimension, as it would give me no values. I have tried adapting the MDX to account for this and have come up with the following:

Code: Select all

{UNION(
	IIF(
		[GLOBAL Level].CurrentMember.Name<>"All Levels",
		{FILTER(
			{TM1SubsetAll([Course])},
			[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name
			)
		},
		{TM1SubsetAll([Course])}),
	IIF(
		[GLOBAL Mode].CurrentMember.Name<>"All Modes",
		{FILTER(
			{TM1SubsetAll([Course])},
			[Course].[Mode]=[GLOBAL Mode].CurrentMember.Name
			)
		},
		{TM1SubsetAll([Course])})
	)
}

However this gives me a syntax error "at or near character 7".

Is it my UNION or IIF that is wrong? I have checked the logic and can't see anything obviously amiss. What have I missed?

[jamesbennett]

Bump. Any insights?

[PlanningDev]

Im not 100% certain, but I tested a few examples and I believe the issue is that IIF wants to return either a numeric value or a string value.

It appears that it is supposed to be able to return a set however in this case you are evaluating a string and so it seems to want a string value to be returned.

If you can return a string value from the IIF statement and then use something like StrToSet you can then perform your set expression outside the IIF which works.

I can't test your exact code but something like this should work

Code: Select all

{UNION(
	StrToSet(
		IIF(
			[GLOBAL Level].CurrentMember.Name<>"All Levels",
			"{FILTER(
				{TM1SubsetAll([Course])},
				[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name
				)
			}",
			"{TM1SubsetAll([Course])}")
		),
	StrToSet(
		IIF(
			[GLOBAL Mode].CurrentMember.Name<>"All Modes",
			"{FILTER(
				{TM1SubsetAll([Course])}",
				[Course].[Mode]=[GLOBAL Mode].CurrentMember.Name
				)
			}",
			"{TM1SubsetAll([Course])}")
		)
	)
}

[jamesbennett]

Thanks for your reply PlanningDev. Using your code with the StrToSet seems to work well - however the UNION still doesn't seem to work. It doesn't matter in which order I put Level or Mode - the Course dimension only filters by the first one and ignores the second one. Testing each block of code individually does what it should - but putting them together inside a UNION does not. Any ideas as to why?

[Mark RMBC]

Hi,

I haven't quite worked out what Global Level or Mode is, a subset, a single element in a dimension of one element?

I don't think the UNION is the issue personally. Have you tried doing something like just to check it returns something:

Code: Select all

{UNION(
	StrToSet(
		IIF(
			[GLOBAL Level].CurrentMember.Name<>"All Levels",
			"{FILTER(
				{TM1SubsetAll([Course])},
				[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name
				)
			}",
			"{TM1SubsetAll([Course])}")
		),
	{[Course].[Any old element]}
	)
}

You could try the following:

Code: Select all

{UNION(
	StrToSet(
		IIF(
			Instr(1, "All Levels", [GLOBAL Level].CurrentMember.Name) = 0,
			"{FILTER(
				{TM1SubsetAll([Course])},
				[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name
				)
			}",
			"{TM1SubsetAll([Course])}")
		),
	StrToSet(
		IIF(
			Instr(1,"All Modes", [GLOBAL Mode].CurrentMember.Name) = 0,
			"{FILTER(
				{TM1SubsetAll([Course])}",
				[Course].[Mode]=[GLOBAL Mode].CurrentMember.Name
				)
			}",
			"{TM1SubsetAll([Course])}")
		)
	)
}

cheers, Mark

[jamesbennett]

Hi Mark,

Apologies - Global Level and Global Mode are both title dimensions in this cube, the values of which correspond to attributes in the Course dimension. E.g. Global Mode has two elements (FT and PT) grouped under a consolidation All Modes. Each element in the Course dimension has a Mode attribute equalling FT or PT, but obviously not All Modes - hence why I want to exclude these consolidations from the filter.

Your suspicion that it's not the Union at fault might well be correct - I've just tried unioning the Level filter with a specific element (that would not be included in the first set) and it returns it correctly.

Your suggestion with the Instr function unfortunately returns the same as PlanningDev's original syntax with StrToSet. It works for either Level or Mode (whichever is placed first in the Union) but not both together.

---------

Edit: I realise I've got this wrong. Mark's syntax above is working fine, just not giving me what I want. If I select Mode=PT and Level=LevelA, it returns all courses with mode=PT (regardless of level) plus all courses with level=LevelA (regardless of mode). Whereas I want it to return just those courses with Mode=PT and Level=LevelA.

[Mark RMBC]

Hi,

I am slightly confused, which may well be me!

What does the cube have anything to do with this, that is what I am struggling with.

When you say Global Level and Global Mode are both title dimensions in this cube, which cube?

And in reality Global Level and Global Mode are both just dimensions are they not?

I also don't understand what you mean by If I select Mode=PT and Level=LevelA? Where is that in your MDX? There is no reference to a parameter for PT or A in your MDX?

Having said that what happens if you replace Union with Intersect?

cheers, Mark

[PlanningDev]

Edit: I realise I've got this wrong. Mark's syntax above is working fine, just not giving me what I want. If I select Mode=PT and Level=LevelA, it returns all courses with mode=PT (regardless of level) plus all courses with level=LevelA (regardless of mode). Whereas I want it to return just those courses with Mode=PT and Level=LevelA.

So the MDX is not giving a syntax error, rather its not returning what you expected? If thats true then looking at your MDX, I believe you would not need a UNION. You would want to put both conditions into the same FILTER statement using AND. Then you would get elements that have BOTH Mode=PT AND level=LevelA.

[Mark RMBC]

Hi PlanningDev,

Maybe you can explain where in the MDX Mode=PT AND level=LevelA is referenced because it is confusing me!

[PlanningDev]

Hi PlanningDev,

Maybe you can explain where in the MDX Mode=PT AND level=LevelA is referenced because it is confusing me!

Im guessing but I believe its the two filters. Programme Type = Global Level and then Mode = Global Mode. Mode = PT is just his example of the Mode attribute of dimension Course equals the current member name of the selected element from GLOBAL Mode dimension (which is a title dimension). So essentially if you selected PT from the title dimension Gobal Mode then it should filter the Course dimension for only elements that have a Mode attribute that equals PT.

Code: Select all

{UNION(
	StrToSet(
		IIF(
			Instr(1, "All Levels", [GLOBAL Level].CurrentMember.Name) = 0,
			"{FILTER(
				{TM1SubsetAll([Course])},

[Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name

Code: Select all

				)
			}",
			"{TM1SubsetAll([Course])}")
		),
	StrToSet(
		IIF(
			Instr(1,"All Modes", [GLOBAL Mode].CurrentMember.Name) = 0,
			"{FILTER(
				{TM1SubsetAll([Course])}",

[Course].[Mode]=[GLOBAL Mode].CurrentMember.Name

Code: Select all

				)
			}",
			"{TM1SubsetAll([Course])}")
		)
	)
}

[Mark RMBC]

Ha! Thanks PlanningDev, I get it.

I think you are correct about the and statements, but I tried the following and it seemed to work:

Code: Select all

StrToSet(
	IIF(Instr(1, [GLOBAL Level].CurrentMember.Name, 'All Levels', 1) >0 or Instr(1, [GLOBAL Mode].CurrentMember.Name, 'All Modes',1) >0,
		"{TM1SubsetAll([Course])}",
		"FILTER({TM1SubsetAll([Course])}, [Course].[Programme Type]=[GLOBAL Level].CurrentMember.Name and [Course].[Mode]=[GLOBAL Mode].CurrentMember.Name)"))

Rather embarrassingly in my instr in the previous comment I got the parameters the wrong way around! All Levels should come after the currentmember (this is obviously corrected in the code above, I also added the last parameter, 1)

Regards, Mark

[jamesbennett]

PlanningDev and Mark,

Sorry for the delay in replying, but wanted to say thanks - I've made a couple of tweaks to your syntax but got it working exactly as intended now - couldn't have got there without you both!

[Mark RMBC]

Hi James,

no worries, tbh without planningdev I wouldn't have got there either!

Just for my sanity, what were the tweaks you made?

regards,
mark