MDX - How to identify empty subset through MDX

[lav4you]

Hi All,

I would like perform a dynamic operation on a subset such that,

IF FILTER MDX Expression returns empty subset then use different subset else use the same.

Code: Select all

IIF( 
	ISEMTPY(   FILTER(TM1SUBSETALL([Employee]) , [Employee].[State]) = "Active"  ) , 
	[Employee].MEMBER,
	FILTER(TM1SUBSETALL([Employee]) , [Employee].[State]) = "Active"
)

How to achieve this through MDX?? Any thought?

[gtonkin]

I am thinking that you may have found a use-case for TM1TupleSize!

[Wim Gielis]

Hello,

Here is an example that might guide you in the direction of a solution.

Code: Select all

StrToSet( Iif( Count( Filter( TM1SubsetAll([Company]), [Company].CurrentMember.Properties("Active") = "Y" )) > 0, 
"Filter( TM1SubsetAll( [Company]), [Company].CurrentMember.Properties(""Active"") = ""Y"" )", 
"TM1SubsetAll([Company])" ))

If there are Active companies, filter those out.
If there are No Active companies, return all companies.

The function StrToSet is not my favorite. Working with text to generate an MDX query is second best in my opinion.

[lav4you]

Thanks gtonkin.

Thanks Wim, I have done exactly that IIF and Count.

I got this idea from your website with amazing pool of MDX examples.

Thanks for that..


Regards,
Lav

[Wim Gielis]

You’re welcome, glad you have a solution.