skip to Main Content

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

This Post Has 52 Comments
  1. wow
    So far a set was something to hide some rows and columns for me. I was not even aware that you can use formulas within sets. The last part with the automatic change to detail level if only one item is selected looks awesome. I need to check how much from that can be used also in the old PowerPivot 2010 version that we still use.

    1. can sombody please try if this could work with the first PowerPivot version ? I cant get it running.

      If I use a simplified selection measure like selection=1 or =”A” the set is working and changes to the new layout when I manually change that value in the selection measure.

      As soon as I start to use a disconnected slicer to change the value of my selection measure the set does not accept that any longer as an input to the logical expression and I get the error

      “Cannot change a set that handles dimensionality” .

  2. Brilliant – I’ve just followed the example and can do the same in 2010. Never used the create hierarchy in the tables before – wondered what it could do!

    1. Although mine appears to work in 2010, I’m seeing strange behaviour. After a data refresh with the Powerpivot cube, the Excel Data refresh shows the error message:
      “Current session is no longer valid due to structural changes in the database”. However, if you click Ok and continue to refresh it all works?

      Googleing this returns a few hits relating to MDX and Powerpivot. Any ideas?

  3. You can do this sort of thing using cube formulas , named ranges and disconnected table slicer, which I think is what is meant by the ‘old way’. Here is how I have done this:

    Basically, you just use an IF statement to determine which cubeset to use in cubevalue formulas.

    Example, the dimension to use in for the cubevalue formulas:

    (A1 is the cuberankedmember() of the slicer to select manager.)

    IFERROR(IF($A$1=”All”,CUBERANKEDMEMBER(“ThisWorkbookDataModel”,CUBESET(“ThisWorkbookDataModel”,”[DIM_Manager].[Manager_Key].Children”,”Managers”),ROW(A1)),CUBERANKEDMEMBER(“ThisWorkbookDataModel”,CUBESET(“ThisWorkbookDataModel”,”([Sales].[Sales Manager].&[“&$A$1&”],[Sales].[Sales Region].Children)”,”meep”),ROW(A1))),””)

    Cubevalue formulas to pull in value:

    =IFERROR(IF($A$1=”All”,CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Sales]”,”[DIM_Manager].[Manager_Key].&[“&$C4&”]”),CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Sales]”,”[DIM_Manager].[Manager_Key].&[“&$A$1&”]”,”[DIM_Region].[Region_Key].&[“&C4&”]”)),””)

    Then make the table a named range for clean charting:

    OFFSET(Sheet4!$D$3,0,0,COUNT(Sheet4!$D$3:$D$11),1)

    The above produces a drill down that shows Sales by Manager if all managers in the slicer are selected. If just one manager is selected, then it shows that manager’s sales by region.

    1. Yes, this is what we meant as ‘old way’.

      The new way is more robust, to my opinion,
      Also, how would you link these dynamic ranges to charts?
      I think you will need some vba for that…

      1. Hi Idan,

        You can link to the dynamic range for chart display without using vba via ‘select data’ as below:

        Example_Slicer_Category_Report.xlsx!Dynamic

        where ‘Dynamic’ is the named range name.

        If you have time, I would love to hear more about why using named sets is more robust than what I listed. I think what you’ve put together here is quite innovative and I’m already thinking of other ways to use it. I’m not sure in this case how it is more robust than using the ‘old way’, but would love to hear some ideas – if you have time.

        Thank you!

        Valya

        1. hi Valya,
          The problem with cubeset that you have to know exactly how many managers are working and if you hire additional one you have to drag the range of the table.
          Another problem is when you will choose two option in the slicer you will get only the first cubevalue without the comparison to my opinion. Am I right? any way we used to use this method also and it worked just fine.

          1. Hello –

            I think I found a way around this, but neither here nor there. I do like your way and I’ll give it a shot. Thanks for the article!

            Thanks,
            Valya

        2. This is obviously just the beginning. This technique allows you to do some crazy stuff…
          For example , Choose which and how many measures to be displayed on a pivot/chart based on slicer selection. Sorting by measures and some other cool stuff.
          Maybe we’ll write another post of advanced uses of this..

          1. Yes please do a follow up if possible. I’m definitely going to be diving into this. Thank you again for sharing this innovation.

        3. The method you mentioned about dynamic range does not work if you link the entire chart (it is being run over with the pysical address, It does work if you link each series separately to a dynamic named range, but for this you will need to know the number of series in your chart in advance

  4. I agree totally with the entusiasm!
    Been using Customer hierachy instead of Product hierarchy and it’s working very good!

    Do you see any problems with trying to combine Time-Product-Customer, one more set?

  5. Hi, I was following all the steps, looks OK until the last step when I change the slicer from Month to Quarter. I got an error “Cannot handle a set that change dimensionality”.

    When I change slicer, I saw the measure “Period selection” also changed, which means good.

    I am using powerpivot v11 on excel 2010. Does it also work in this version?

  6. Hi Idan, this is a fantastic technique – thanks for sharing.

    I was trying to reproduce the example in Excel 2013 and I ran into the following error when I try to add the Product-based Set (called ProductSetTest in my example):

    A field cannot be added to the PivotTable report or as a slicer connected to the PivotTable at the same time as a set based on that field. With the “ProductSetTest” set in the Pivot Table you cannot add the “Product Hierarchy” field

    I see that the DimProduct table is duplicated in the example one as Product Master as well. My set is based on Product Master. If I add a slicer from DimProduct table, it works – is this why you have duplicated the DimProduct table?

  7. mmmm… DimProduct is not duplicated…
    I know there is a bug in Excel 2013 when you trying to change the query of the first table created in the model. Could that be the case ? Is the table duplicated after you changed it and refreshed ?

    1. Hi Idan, I don’t know about the bug in Excel 2013. I have redownloaded the sample file and I see that you have 2 tables for product in your file – one is DimProduct and another is Product Master. The Product Hierarchy is built on the table Product Master and the slicers are from Dim Product – correct me if I am wrong as Excel 2013 does not display the table of the slicer, only the column is displayed. If I try to insert a slicer from the Product Master table, then I am getting the same error. Is that a real limitation that you can’t add a field that is already in a hierarchy as a slicer field to the pivot table which uses that hierarchy?

        1. Thanks Idan for clarifying.

          But having to duplicate the table is OK in a demo scenario, but will not be a viable alternative for most real-life situations. Also, this limitation of not being able to place the slicer on the field that is used in a hierarchy really defeats the purpose and takes away the utility of this brilliant idea. What are your thoughts regarding this and is there any other way to achieve the dynamic drill-down without running into this limitation?

          1. This post was about revealing the technique, And setting up the imagination of it’s uses.
            The product hierarchy is one example of the dynamic set uses, In other examples (like the quarter/month) this limitation does not exists (And btw – there are other uses which we might show in future posts).

            Off course there are some limitations , But you cant be picky if you want to be innovative.

            Specifically speaking , i don’t think that adding another product dimension table cost so much especially if you only pull the relevant columns (as SSAS Tabular is column based).
            Let’s say you pull : Product Code,ProductName,SubCategory and Category.

            BTW – This solution works great on 5 different solutions we created for 5 different happy clients

          2. Hi Idan,

            Sorry if i have come across as criticizing your technique. That was not my intention at all. I am infinitely grateful that you took the time to share this wonderful technique and I have been thinking about how to use it despite its limitations. My comment was to the Excel 2013 team for building in such a limitation – if that limitation is not there, it would have been a wonderful world :). As you rightly said, let us use our imagination to see how we can make best use of the feature without being picky! Thanks once again for sharing the tip and keep sharing.

  8. This is, without question, truly excellent. Thank you so much for sharing, Idan and all those involved. Quite brilliant!

  9. I downloaded the example workbook, but when I try using it, Excel keeps crashing. It usually happens when I click on the slicers and filter a few things then click the button on the slicer to clear all filters (e.g. I clicked clothing, then shorts, then cleared the filter from Subcategory). I am using 64-bit Excel 2013

    I was really excited when I saw this post, but it crashes Excel every time I use the slicer, I don’t see it ever being useful. I can’t stop clients from clicking the “clear filters” button on the slicer.

    1. As i said before, This solution is working on clients’ machines, Not one.

      We did not experience any crashes…

      Could it be that your are using Office 2013 professional plus and not Office Pro Plus 365 ?
      I know that this version i less stable and i did not tested our solution on it

      1. That would be correct. I am using Office 2013 professional plus. Strange, the first tab (Set Example Row) doesn’t seem to have any problems. It is the second tab (Set Example Column) that experiences the crashing issue.

  10. I this example there are two choices in the periodselect table, month or quarter so the if(hasonevalue( … ,”Month” works where true returns Month and False returns Quarter. What if a third or fourth choice were possible, e.g. Weekly or Daily?

  11. Hi Idan,

    The article is great, and I therefore tried to implement it directly in the dashboard I am working on.

    I created a hierarchy, that was using days and month (which in this case is more relevant) a Little bit like Bobjman describes above. The periodset was created as well and the measure in the disconnected table was created.

    I tested the connection, which Works fine and when I insert the periodset in the pivottable it shows the days, as I have asked for. But when I want to change to the month – nothing happoens.

    Is there any possible checks that can be done to see where my application fails?

  12. Hi this is a great post and what I am trying to achieve with powerpivot. I am just learning right now. How can I see the underlying source data in your workbook? Thanks!

  13. Great solution!

    How about switching between different dimensional hierarchies? E.g. what if you wanted a slicer to select between Product Category or Customer?

    My attempt results in the following error: “Cannot handle a set that changes dimensionality.”

    1. Robert: It seems that dynamic sets only allow you to switch between levels of the same hierarchy. In order to do what you want, check this post. My initial solution based on selecting one of two relationships is not as clean and more cumbersome, but may provide a workaround nevertheless.
      https://social.technet.microsoft.com/Forums/en-US/8de9b3b3-7ac9-4900-a0cf-e5b5605e5a15/select-dimension-by-slicer?forum=sqlkjpowerpivotforexcel

      Also, it is possible to simplify the set definition with the Descendants MDX function:
      DESCENDANTS([ProductMaster].[Product Hierarchy].[All],[Measures].[ProductHierarchy])

      Idan: Thanks for this great post.

  14. First – superb tutorial, a life saver 🙂
    My charts are working perfectly as you promised.

    My only drawback at the moment, is to do with the data in the pivot table behind the chart.
    I seem to have lost subtotals and grand totals.
    In Pivot Field and Pivot Table settings, I can see that all the ticks are in the usual places to allow sub/totals…. but they just won’t display in this new dynamic setup.

    Is this a known thing ? Anything I can do to get them back ?
    Thanks 🙂

    1. Simon, I’ve found exactly the same thing and I can’t seem to get them to appear for love nor money. Did you find a solution in the end?

      1. I managed to get it all working using visualtotals (there are many other posts out there that reference this but it took me an age to get it to actually work). My first set was the hierarchy and then I had the IIF statements that referenced the levels within it. Worked straight away. Enjoy!

  15. Hi,

    Love this! I do have a question though:

    I am running into trouble when using this technique with a Prior Year YTD calculation:

    Prior Year YTD Amount:=CALCULATE([YTD Amount], ‘Calendar'[Financial Year] = VALUES(‘Calendar'[Financial Year]) – 1 )

    Any ideas why that might be?

    Thanks
    Rich

  16. While choosing another option in slicer I have encountered an error: “cannot handle a set that changes dimensionality”. The example from file works perfectly though and I can’t find any difference. Don’t you know why this error might happen?

  17. Really sleek – clever solution has made me question my over use off cube formulae in excel; it is all down to clever data model design such as this one, thank you so much for posting this and providing the workbook really helpful.
    It’s one that I will look at emulating for a similar scenario with geographical members – 🙂
    Please post more on mdx and sets, this is a step up (at least for me)
    I just wish that there was more documentation for all of this, although it is helpful having an mdx function wizard of sorts – still experimenting.
    Also to add it’s helpful to understand mdx query fundamentals. -helps visualise in excel (for those of us who are not as gifted )

    Many thanks once again, really appreciated.
    I will look out for more.

  18. Providing absolute or % values is still not possible ? User selects absolute or % values in another slicer – that still seems to require VBA, can this be done without VBA?
    last thing, please help.
    (using VBA is fiddly and really not an elegant solution, just wish there was another way – (i.e. linked to source for measures)
    Please see last post I was asking for further documentation to read on all of this.
    BUT thank you so much.

Leave a Comment or Question