4

I am trying to combine two dimensions in an MDX query.

SELECT 
NON EMPTY
(
    [XXX].[YYY].children
) ON ROWS,
NON EMPTY
{
    [Measures].[Dimension1A],
    [Measures].[Dimension1B],
    [Measures].[Dimension2A],
    [Measures].[Dimension2B]
} ON COLUMNS
FROM [RePro]
WHERE
(
    [ABC].[ABC].[Val]
) CELL PROPERTIES VALUE

This produces results where all the [Measures] dimensions are in separate columns. How can I make it so that [Dimension1A] and [Dimension1B] are combined/merged into the same column, and likewise for [Dimension2A] and [Dimension2B]?

I've tried the solution here, but I get '#Error' in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimension". This is what I tried:

WITH SET [Combined] AS
{
    [Measures].[Dimension1A],
    [Measures].[Dimension1B]
}
MEMBER [Measures].[Dimension1] AS Aggregate([Combined])
SELECT 
NON EMPTY
(
    [XXX].[YYY].children
) ON ROWS,
UNION
{
    Except([Measures], [Combined]),
    [Measures].[Dimension1]
} ON COLUMNS
FROM [RePro]
WHERE
(
    [ABC].[ABC].[Val]
) CELL PROPERTIES VALUE

I have removed [Dimenion2A] and [Dimension2B] for brevity. Any ideas?

Thanks :)

3
  • Wow. The question is not answered for almost 10 years. Is that possible at all? Commented Feb 27, 2023 at 15:11
  • @SlavaMurygin Indeed. I'm still awaiting an answer. Been scratching my head the entire time. I've literally not moved on from this, or done any other coding. My boss is chasing me every day asking when I'm gonna get this MDX query finished!!!
    – Neo
    Commented Mar 1, 2023 at 15:55
  • Faced similar issue few months ago. stackoverflow.com/questions/74804689/… Solved it by extracting two separate data set and merging them within an app. Now trying to attack that issue again. Will share the solution if any. Commented Mar 2, 2023 at 13:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.