TM1 Forum

Posted: **Thu Feb 11, 2021 11:20 am**

Hello Everyone,

I am trying to create an MDX statement using IIF Condition but my solution seem not working in Expression MDX tester in TM1. I would love some help on this.

Basicly, I am testing :

If ( type = A , show elemnts of type A , If ( Type=B , show elemnt of Type B , else show C )

I have hierarchy like this :
A-> B -> C or B->C or just B

This is the logic behind my MDX. So I created Like this :

Code: Select all

 IIF(   [z_test].[Type]="A",  {FILTER(DESCENDANTS([z_test].[Total]),[z_test].[Type] = "A")} ,
         IIF(      [z_test].[Type]="B",  {FILTER(DESCENDANTS([z_test].[Total]),[z_test].[Type] = "B")},
                     {FILTER(DESCENDANTS([z_test].[Total]),[z_test].[Type] = "C")}
             )
   )

When I try this it throws me this error :
https://ibb.co/grHCz3p

What I want to do is show up elements of Type A if they existenet f they dont show up elemnt of type B if they exist else show up C.

I tried using this MDX:

Code: Select all

{FILTER(DESCENDANTS([z_test].[Total]),[z_test].[Type] = "A"  or [z_test].[Type] = "B" [z_test].[Type] = "C"  )}

But it ended up showing me elemnt A and all its children and non children , I want if I have :
A1 -> B1 -> C1 and B2->C2 and C3 ==> I want to show up A1 , B2 , C3 .

Hope it make sense. And I would love to help me understand what I messed up in my MDX.

Thank you in adavance

Posted: **Thu Feb 11, 2021 1:43 pm**

Hi,

I will be honest I couldn't quite tell what you are looking for, as your post confused me a little.

However if I focus on this line:

What I want to do is show up elements of Type A if they existenet f they dont show up elemnt of type B if they exist else show up

In which case something like the following may work:

Code: Select all

StrToSet (
	"{Filter(Descendants([z_test].[Total),"
	+
	IIF (
		COUNT ( {Filter(Descendants([z_test].[Total), [z_test].[Type] = 'A')}				
		) > 0,
		"[z_test].[Type] = 'A'",
		IIF (
		COUNT ( {Filter(Descendants([z_test].[Total]), [z_test].[Type] = 'B')}				
		) > 0,
		"[z_test].[Type] = 'B'",
		"[z_test].[Type] = 'C'"
	))
	+
	")}"
)

regards,

Mark

TM1 Forum

IIF statement in MDX

IIF statement in MDX

Re: IIF statement in MDX