MDX new measure question

Robin77 1 Reputation point
2022-09-29T12:03:18.287+00:00

I have a measure [Measures].[Value]
and dimensions:
[Company]
[Month]

I need to create a new measure that would NOT contain the following set of data:

Excluding Company A and months 1 and 2
Excluding Company B and months 2 and 3

I will create a new measure in Excel, there are Olap Tools and an editor for the new measure.

Can anybody help me to write it in MDX? A polite request for help

Thanks in advance

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2022-09-29T18:17:01.667+00:00

    You probably won't believe the amount of hassle these requirements actually cause.

    You'll have to create two sets, one of company.company.company and the other of month.month.month, making sure to check the 'recalculate...' checkbox. If you don't, many filtering operations on any of the dimensions will ruin your result.
    The measure in its most generic form will be
    aggregate ( existing ( company_set * month_set - (company.company.A, month.month.1) - .... - (company.company.B, month.month.3) ) , measures.value )

    And, you'll still have more fine-tuning to do if, for example, Value happens to be a calculated measure.

    Excel and MDX are a fun combination :)


  2. Robin77 1 Reputation point
    2022-10-04T09:13:43.757+00:00

    Interesting. Thank you for Your help, give some time to check it


  3. Robin77 1 Reputation point
    2022-10-05T09:32:14.773+00:00

    set expression is invalid
    I think so

    Syntax
    Aggregate(Set_Expression [ ,Numeric_Expression ])

    0 comments No comments