none
Create a new member on dimension : MDX vs DAX ?

    Question

  • Dear All,

    Following my previous post, let's dig a bit. I have the following table :

    I want to do comparison between years.

    I got part of the solution thanks to Ruve1k [+1] :

    http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/766cb82c-4101-4895-898f-04088bcfe438

    the only problem is that I 'll to define as many "Evolution of ..." variables as I have measures (for example margin, margin rates etc  ....).

    In a cube, I would have used MDX in order to create a new member on my "peridodical dimension" as follow :

    with 
         Member [Order Date].[Time Hier].[Evolution]
         as 
         (
           ([Order Date].[Time Hier].[Year].[1998] - [Order Date].[Time Hier].[Year].&[1997])
           /
           [Order Date].[Time Hier].[Year].&[1997]
          ) ,     FORMAT_STRING = 'Percent'
           

    In a cube, this would have added to new member named  "Evolution" applied to any measures mentioned bellow.

    Such member will appear  on the right of "Current Year "with the following result :

    Is it possible to define a new member on a categy as i do in MDX to avoid to define many measures (maintenance & error risk ) ?

    BR to all,


    RenoH - Paris - France


    • Edited by RenoH Monday, April 16, 2012 7:20 AM
    Monday, April 16, 2012 7:18 AM

Answers

  • you cannot create new members in DAX

    you can only create new calculations

    there is only one option you may use:
    use a DateTool-Dimension and apply a calculation based on the currently selected item of this DateTool-Dimension
    this is also done in the Contos Sample Workbook:

    =IF( COUNTROWS( VALUES( DimPeriod[Period]))=1;
      IF( VALUES( DimPeriod[Period]) = "Current"; [Sales];
      IF( VALUES( DimPeriod[Period]) = "MTD"; [Sales](DATESMTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "QTD"; [Sales](DATESQTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "YTD"; [Sales](DATESYTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "LastYear"; [Sales](DATEADD(DimDate[Datekey];-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearMTD"; [Sales](DATEADD(DATESMTD(DimDate[Datekey]);-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearQTD"; [Sales](DATEADD(DATESQTD(DimDate[Datekey]);-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearYTD"; [Sales](DATEADD(DATESYTD(DimDate[Datekey]);-1;YEAR));
      BLANK()))))))));[Sales])
    hth,
    gerhard

    - www.pmOne.com -

    • Marked as answer by RenoH Tuesday, April 17, 2012 2:18 AM
    Monday, April 16, 2012 8:48 PM
    Answerer

All replies

  • you cannot create new members in DAX

    you can only create new calculations

    there is only one option you may use:
    use a DateTool-Dimension and apply a calculation based on the currently selected item of this DateTool-Dimension
    this is also done in the Contos Sample Workbook:

    =IF( COUNTROWS( VALUES( DimPeriod[Period]))=1;
      IF( VALUES( DimPeriod[Period]) = "Current"; [Sales];
      IF( VALUES( DimPeriod[Period]) = "MTD"; [Sales](DATESMTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "QTD"; [Sales](DATESQTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "YTD"; [Sales](DATESYTD(DimDate[Datekey]));
      IF( VALUES( DimPeriod[Period]) = "LastYear"; [Sales](DATEADD(DimDate[Datekey];-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearMTD"; [Sales](DATEADD(DATESMTD(DimDate[Datekey]);-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearQTD"; [Sales](DATEADD(DATESQTD(DimDate[Datekey]);-1;YEAR));
      IF( VALUES( DimPeriod[Period]) = "PriorYearYTD"; [Sales](DATEADD(DATESYTD(DimDate[Datekey]);-1;YEAR));
      BLANK()))))))));[Sales])
    hth,
    gerhard

    - www.pmOne.com -

    • Marked as answer by RenoH Tuesday, April 17, 2012 2:18 AM
    Monday, April 16, 2012 8:48 PM
    Answerer
  • RenoH,

    While not really a PowerPivot solution per se, you can add a session calculated member to your pivot table by using a tool like OLAP Pivot Table Extensions. Essentially, it allows you to add the MDX that you listed above to the beginning of the MDX query that your pivot table generates against the embedded PowerPivot cube. Note that nothing gets added to your model. It is simply the front end tool (Excel) that enables this functionality.

    You may also consider Excel's built in functionality. Right-click on any measure, then choose: Show Values as... > % Difference From... . Then set Base Field to Order Year, and Base Item to (previous). This will give you the percentage growth compared to the previous member. (What you call Evolution.)

    Tuesday, April 17, 2012 4:58 AM