MDX for elements that exist in both subsets

Post Reply
MarenC
Posts: 123
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

MDX for elements that exist in both subsets

Post by MarenC » Mon Oct 05, 2020 12:28 pm

Hi,

I have 2 mdx based subsets, one to find a variance between the current and next period and another which asks if an employee has an end date.

I now want to combine the 2 to see where the variance is possibly due to an end date.

I can combine the mdx statements into one with an and condition, which is fine, except it makes the mdx string very very long.

So I was wondering if it is possible to write mdx for where the elements exist in both subsets.

Maren

tomok
MVP
Posts: 2758
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX for elements that exist in both subsets

Post by tomok » Mon Oct 05, 2020 12:51 pm

MarenC wrote:
Mon Oct 05, 2020 12:28 pm
Hi,

I have 2 mdx based subsets, one to find a variance between the current and next period and another which asks if an employee has an end date.

I now want to combine the 2 to see where the variance is possibly due to an end date.

I can combine the mdx statements into one with an and condition, which is fine, except it makes the mdx string very very long.

So I was wondering if it is possible to write mdx for where the elements exist in both subsets.

Maren
You can reference the name of an existing subset in an MDX query with the [DimName].[SubsetName] syntax so you should be able to do an INTERSECT between the two subsets.

Code: Select all

{
INTERSECT(
    {[DimensionName].[SubsetName1]},
    {[DimensionName].[SubsetName2]})
    )
}
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

MarenC
Posts: 123
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX for elements that exist in both subsets

Post by MarenC » Mon Oct 05, 2020 1:01 pm

Hi Tomok,

you had one too many parenthesis in that but otherwise worked perfectly, thanks!

Maren

tomok
MVP
Posts: 2758
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX for elements that exist in both subsets

Post by tomok » Mon Oct 05, 2020 1:21 pm

MarenC wrote:
Mon Oct 05, 2020 1:01 pm
Hi Tomok,

you had one too many parenthesis in that but otherwise worked perfectly, thanks!

Maren
I accept no responsibility for syntax errors. :)
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2552
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX for elements that exist in both subsets

Post by Wim Gielis » Mon Oct 05, 2020 9:26 pm

Actually, why not having less of these, and avoiding syntax headaches ? This works too:

Code: Select all

INTERSECT(
    [DimensionName].[SubsetName1],
    [DimensionName].[SubsetName2] )
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply