Page 1 of 1
MDX - How to identify empty subset through MDX
Posted: Mon Feb 06, 2023 6:30 pm
by 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?
Re: MDX - How to identify empty subset through MDX
Posted: Mon Feb 06, 2023 7:09 pm
by gtonkin
I am thinking that you may have found a use-case for TM1TupleSize!
Re: MDX - How to identify empty subset through MDX
Posted: Mon Feb 06, 2023 10:35 pm
by 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.
Re: MDX - How to identify empty subset through MDX
Posted: Tue Feb 07, 2023 1:43 pm
by 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
Re: MDX - How to identify empty subset through MDX
Posted: Tue Feb 07, 2023 7:40 pm
by Wim Gielis
You’re welcome, glad you have a solution.