Create a new member on dimension : MDX vs DAX ?

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

• 2012年4月16日 7:18

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] :

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

• 編集済み 2012年4月16日 7:20
•

### すべての返信

• 2012年4月16日 20:48

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 -

• 回答としてマーク 2012年4月17日 2:18
•
• 2012年4月17日 4:58

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.)