Page 1 of 1
MDX for elements that exist in both subsets
Posted: Mon Oct 05, 2020 12:28 pm
by MarenC
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
Re: MDX for elements that exist in both subsets
Posted: Mon Oct 05, 2020 12:51 pm
by tomok
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]})
)
}
Re: MDX for elements that exist in both subsets
Posted: Mon Oct 05, 2020 1:01 pm
by MarenC
Hi Tomok,
you had one too many parenthesis in that but otherwise worked perfectly, thanks!
Maren
Re: MDX for elements that exist in both subsets
Posted: Mon Oct 05, 2020 1:21 pm
by tomok
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.
Re: MDX for elements that exist in both subsets
Posted: Mon Oct 05, 2020 9:26 pm
by Wim Gielis
Actually, why not having less of these, and avoiding syntax headaches ? This works too:
Code: Select all
INTERSECT(
[DimensionName].[SubsetName1],
[DimensionName].[SubsetName2] )