Guest Post by Idan Cohen From Excelando

Rob already blogged about charts with dynamic measures –Using Named Sets for “Asymmetric” pivots, where you can choose the measures to be displayed with a slicer.

But what about dynamic axis?

This clever technique was found by one of my analysts , Gal Vekselman, when a client challenged us.
What is it useful for you ask ??!

For example, I want a chart to display sales by quarter and then change it to sales by month with a press of a button?
Or another useful scenario where I want to see sales by category,  but when choosing a category on the slicer,  the sub categories for this category will be displayed in the chart,  and when choosing a sub category the chart will display the underlying products.  Sounds cool, huh? Well,  it is even cooler.

And the way to do it? Named sets!

Dynamic Monthly Chart Dynamic QuarterlyChart

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

A Practical example

Download Example Workbook

You are working for a bicycle firm. The sales manager asks you to create two Sales views:

A. Dynamic report of sales amount, Order quantity and average price. Where the charts will display the data monthly or quarterly by selection.

B. A drill down analysis by Category, Subcategory and product’s name. this analysis will be displayed in a chart according the following methodology:

a. Three slicer of Category, Subcategory and product name will be presented.

b. When all category values selected the chart will present the first hierarchy.

c. When only one category value is selected the chart will present all the subcategory values.

d. When only one subcategory value is selected is selected the chart will present all products’ name.

e. The chart has to present the data in month or quarter according to selection.

In the old days (before this MDX cool trick) we used to:

i. Create multiple PivotTables.

ii. Create Dynamic Ranges with OFFSET function.

iii. Debug errors.

iv. Sometimes use the CHOOSE function.

v. And more, and more and more.

You get the picture.

Now we simply need to create two simple measures and use them in two simple MDX sentences.

The how to

First, create a disconnected slicer for the period selection.

Period Selection Slicer

Second, build two calculated fields (Measures) using DAX:

A. Measure that returns the selected period.

[PeriodSelection] =

      IF(
        HASONEFILTER(tbl_PeriodSelection[Period Selection])
,VALUES(tbl_PeriodSelection[Period Selection])
,”Month”
)

(PowerPivotPro Note: Learn about the HASONEFILTER function and more)

B. Measure that returns the wanted hierarchy to present in the chart.

[ProductHierarchy] =

  IF(
HASONEFILTER(DimProductCategory[EnglishProductCategoryName]),
IF(
HASONEFILTER(DimProductSubCategory[EnglishProductSubcategoryName])
,3,2
)
,1)

Third, you have to create a relevant hierarchy – in this scenario you need to create two hierarchies:

A. Period hierarchy – Quarter -> Month (this will be created from the DimDate table)

B. Product Hierarchy – EnglishProductCategoryName -> EnglishProductSubcategoryName -> EnglishProductName (this will be created from the ProductMaster table)

Fourth, go to the Manage Sets editor

Manage Sets

In the editor click on the “New” button and select the “Create set using MDX” option

Create Set Using MDX

Now, the MDX editor will open:

A. To create the “PeriodSet” insert the IIF function from the “All” folder

MDX Functions

This will present the following syntax in the MDX editor

MDX IIF Statement

Replace the «Logical Expression» with the expression:

[Measures].[PeriodSelection]=”Month”

In the TRUE value (the first «object») you need to select the Month members’ folder of the Quarter Month hierarchy level and press the insert button

Member's Selection

In the FALSE value (the second «object») you need to select the Quarter members’ folder of the Quarter Month hierarchy level and press the insert button

set Members

IIF([Measures].[PeriodSelection]=”Month”, [DimDate].[Quarter Month].[Year Month], [DimDate].[Quarter Month].[Year Quarter])

Managed Set Options
Before submitting pay attention that the check box are selected as shown

B. To create the “Products Sets” insert the IIF function from the “All” folder and
Repeat the above process but this time the MDX syntax is:

IIF([Measures].[ProductHierarchy]=3, [ProductMaster].[Product Hierarchy].[EnglishProductName], IIF([Measures].[ProductHierarchy]=2, [ProductMaster].[Product Hierarchy].[EnglishProductSubcategoryName], [ProductMaster].[Product Hierarchy].[EnglishProductCategoryName]))

Now the sets will appear in the DimDate and ProductMaster tables.

Period Set

For the first chart put the period set in the Row field and the Sales in the Values. (Don’t forget to connect a period selection slicer)

Pivot Layout

For the second chart put the period set in the Row field, the Products Sets in the column field and the Sales in the Values field. (Don’t forget to connect a period selection slicer)

Pivot Layout

The result !

The Period Chart:

Dynamic Monthly Chart Dynamic Quarterly Chart

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

The Product Hierarchy Chart:

Product hierarchy Chart
No filters, Displaying Product Category Level

Product hierarchy Chart 2
Multiple Categories chosen, Still displaying Product Category Level

Product hierarchy Chart Selected Category
Single Category Selected – Displaying All Subcategories For this Category

Product hierarchy Chart Selected SubCategory
A Single Sub Category  Is selected on slicer – All products on this Sub category are displayed

Oh! and note that the period axis is also dynamic using the first trick !

Download Example Workbook