PowerPivot BlogRoll – 27th January 2013



PowerPivot activity announced on Twitter for the week ending 27th January 2013.


 


Chandoo.org
Introduction to DAX Formulas & Measures for PowerPivotbit.ly/126dmx9


Kasper de Jonge PowerPivot Blog
Filtering results to only show top x results in Power View chart – bit.ly/UzkC0n


PowerPivotPro
Modeling Viral and Marketing Growth, Part 3 of 3 – bit.ly/114srjB


Chris Webb’s BI Blog
Building Relative Date Reports in PowerPivotbit.ly/XOEXw8


Stefan Johansson
Create Time Dimension- bit.ly/126aWyJ


PowerPivotPro
Modeling Viral and Marketing Growth, Part Two – bit.ly/XOEBpl


Brent Greenwood’s BI blog
A Time dimension specifically for PowerPivot & AS Tabular – bit.ly/Wut3dK


MindSharp
Demystifying PowerPivot in SharePoint and Excel -http://bit.ly/VkwlNq

PowerPivot BlogRoll – 20th January 2013



PowerPivot activity announced on Twitter for the week ending 20th January 2013.


 


PowerPivotPro
Modeling Viral Growth vs. Traditional Advertising in PowerPivotbit.ly/10EOrS3


Javier Guillén Pervasive Business Intelligence
Grouping by an Aggregation in DAX – bit.ly/SeC22c


PowerPivotPro
Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! – bit.ly/W6YrMa


Javier Guillén Pervasive Business Intelligence
Leveraging DAX query tables for staging Data Transformations – bit.ly/13Awule


Some Random Thoughts
Row Selection Using Slicers in PowerPivot – Part 1 – bit.ly/108bp2H


Chris Webb’s BI Blog
A Different Approach To Last-Ever Non-Empty in DAX – bit.ly/Y82Owx


Excel Do, Dynamic Does
CUBEs For Dessert – Cube formulae with MDX – bit.ly/13uGmNf


SQLBI – Marco Russo
PowerPivot Comatibility across versions – bit.ly/UKJ9MM


SQLBI
Linkback Tables in PowerPivot for Excel 2013 -bit.ly/UBhmAi

DAX, The Dark Knight



Excel Formulas Are easy, So Why Is DAX So Hard?


Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,


 


“I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your hands with very little learning, which is awesome.  But…  What I don’t get is DAX.  The light just isn’t going on for me.  If you want to mobilize the Excel Pro army to really make this take off, DAX needs to become accessible for us.”

 


This rang a bell with me, as about six weeks ago I taught a 2 day PowerPivot course with Chris Webb to a class of 15 technology savvy people. These are smart people, but it was clear when covering the more advanced aspects of DAX that they were straining to grasp some of the concepts. On my journey home I was pondering about this. I have stated before that I believe that Microsoft over-egg the simplicity of PowerPivot, suggesting that any user can pick up PowerPivot and create fantastic BI immediately. I think that, whilst easy to get started, to really get the true potential out of PowerPivot you have to have some understanding of Data Modelling.


Have Microsoft got it wrong with DAX as well when they took a deliberate decision to make DAX Excel-like in its syntax? In many ways, this is understandable, their experience of MDX, which really is hard, could weigh heavily on their outlook. But, it has been a double-edged sword in my view. Excel users people are quite ready to give DAX a go because it looks as easy as Excel formulas, but conversely, when they get to advanced DAX (CALCULATE, FILTER, ALL, etc.), they can easily get dis-heartened because it is not as easy as Excel formulas.


So I thought I would look at why DAX is so hard for an Excel user. I came up with 7 topics that I decided to look at, namely:


  • Data Typed
  • Integer Dates
  • Formula Formatting
  • Evaluation Context
  • Extendible
  • Debugging Aids
  • Ease of use

 


Data Typed


Integer Dates


Formula Formatting


Extendible


Debugging Aids


DAX is great if your formula works, but when it doesn’t you need good tools to help you debug it. Unfortunately. Microsoft haven’t provided any debugging tools whatsoever, so debugging is hard, incredibly hard. It should be part of our course, but with no tools to talk about there is nothing to suggest yet.


Just to complicate matters, the data usually consists of thousands, or even hundreds of thousands or millions, of rows, and reducing it to a reduced set is not staright-forward.


Marco Russo, Darren Gosbell, Angry Koala, and Paul te Braak have created a nice Excel addin called DAX Studio which is as good as it gets. it is a useful tool for building DAX formulae, as described by Javier Guillen in his post Using DAX studio to create PowerPivot measures, and this technique can be used to rebuild the formula from scratch checking as you go, but it is not reall a debugging tool, and was not meant as such.


Compare this to Excel. In Excel, the helpful debugging facilities are:


  • Evaluate Formula
  • Watch Window
  • F9

 


The Evaluate Formula facility lets you evaluate a formula in the activecell, stepping through it as each stage of the formula seeing what the result at that stage is, until it is fully resolved. It allows allows stepping in and out of the cell value if the expression being evaluated is a single cell.


The Watch Window allows you to add a cell or a range of cells to be watched, thw window showing the current value, reflecting any changes.


F9 allows you to select any expression within the formula bar and see what it resolves to. The expression can be a single cell, a function, or a set of functions, as long as it is an expression that can be resolved in its own right.


So, three facilties to assist in debugging the formula should make Excel formula debugging easy? In reality, I never use Evaluate Formula or the Watch Window as they do not provide enough value in my view. F9 is very useful, in that it is possible to evaluate any part of the formula. This is in contrast to Evaluate Formula, which evaluates the whole formula which means that the TRUE path is only evaluated, whereas with F9 you can select any part and have it evaluated regardless of whether the actual data would force the formula to follow that path or not.


Okay, Excel has a few tools, which is a few more than DAX, but they are hardly what you would call sophisticated. It still mainly relies on experience, intuition, and insight to be able to debug the more complex Excel formulae. Debugging DAX is diffocult, but debugging complex Excel formulae is not the easiest task either.


Evaluation Context


Evaluation context is probably the


Evaluation context is the aspect of DAX that people struggle with most when they start with PowerPivot. Understanding filter context and row context, when each comes into play, how you can override aspects of the filter context, all of this can seem mind boggling at first.


Ease of use

PowerPivot BlogRoll – 13th January 2013



PowerPivot activity announced on Twitter for the week ending 13th January 2013.


 


The New Internal Data Model in Excel 2013 – bit.ly/VFumXvk


New Customers Per Day Generalized to “New Customers per Month,” etc. – bit.ly/RP7EeK


PowerPivot User Experience Survey – t.co/aGlhdPu7


New Customers per Day – bit.ly/VJtD7j


Getting the active products between a date range using DAX – bit.ly/VDT7AH


[Update] What is using all that memory on my Analysis server instance? – bit.ly/T51Hsc


Parameterising PowerPivot Connection Strings in Excel 2013 – bit.ly/XKBOTH


Technitrain 2013 Course Schedule: SSAS, PowerPivot, MDX, SSIS, TSQL and SQL Server Engine Training in London – bit.ly/U2mVra


Group Multiple Rows to Single Delimited Row in PowerPivotbit.ly/XiDTlO

CUBEs For Dessert



Introduction


A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in Cooking With Cubes.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.


I recently had a similar need, but with the further challenge of presenting two levels in a hierarchy (actually, this was related tables in a snowflake schema). I needed to show all of the items in the first level, and for each of these items, all of the associated items at the second level including those items that have no sales value. The AdventureWorks database has a similar setup with products, where each sale is for a particular product, each product is of specific product sub-category, and each product sub-category is of a specific product category. Within this model, we would be showing sub-categories of Mountain Bikes and Road Bikes against the category of bikes, but not against any other category.


 


I will demonstrate my solution using the same AdventureWorks data. Whilst this will be similar to the previous post in showing how to manage the presentation using Excel formulae, it is extended to multiple data levels and some more complex formatting. Additionally, and the main point of this post, I show a technique to display the related values in that hierarchy, not simple with straight CUBE formulae, by adding some MDX trickery in the CUBE formulae.


Figure 1 shows the final results of all of the formulae and formatting.



Figure 1 – Final Results


Preparing With Good Ingredients


First I need to prepare the PowerPivot model so that I have have product name, product sub-category name, and product category name all on the Product table. To achieve this, I added two calculated columns


[ProductSubcategoryName]
=RELATED(ProductSubcategory[ProductSubcategoryName]
[ProductCategoryName]
=RELATED(ProductCategory[ProductCategoryName]
Formula 1 – PowerPivot Model Calculated Columns


Mixing It Up


The data is ready, so let’s build the required data. I need the following:


  1. a text definition of the cube
  2. a set of all categories
  3. a count if the items in the category set
  4. a list of all of the items in the year set, from the slicer or the dimension if no slicer selections
  5. a count if the items in the year set
  6. a list of all of the items in the category set (in columns)
  7. for each listed category, a set of all associated sub-category items
  8. for each sub-category set, a count of all items in that set
  9. a running total of the sub-category set counts
  10. for each sub-category set, a list of items (in rows)
  11. a reference cell for the sales measure

Taking these in the order given, showing cell(s), defined name, definition:


1. Cube Definition


This is simple text for the connection argument to the cube functions.
J1:[_model]PowerPivot Data
I am using Excel 2010 for this example, Excel 2013 would use the value ThisWorkbookdataModel.
In the previous example, I named this cell _cube, here I use _model.


2. Category Set


This is the set of all categories in the model. This is being taken from the calculated column for product category name in the Products table.
J2:[_setCategory]=CUBESET(_model,”[Product].[ProductCategoryName].Members”,”Categories”)


3. Category Set Count


A simple count of the items in the category set, used in later dynamic formulae.
J3:[_setCategoryCount]=CUBESETCOUNT(_setCategory)-2
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.


4. Year Set


This is the set of years in the model, constrained by the slicer.
J4:[_setYear]
=IF(CUBERANKEDMEMBER(_model,Slicer_FiscalYear,1)<>”All”,
CUBESET(_model,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_model,”[Date].[FiscalYear].Children”,”Dimension Years”))

As in the previous example, to show all years with no slicer selection (not show All), I check the slicer to see the first set member is All, getting the set from the Date dimsnsion if so, otherwise get the set from the slicer.


6. Year Set Count


A simple count of the items in the year set.
J3:[_setYearCount]=CUBESETCOUNT(_setYear)


6. List of Category Set Items


A list of the items in the category set, for use in building the sub-category sets.
K2:N2:[_listCategory]=IFERROR(CUBERANKEDMEMBER(_model,$J$2,COLUMN()-COLUMN($J$2)+2),””)
The name definition uses a dynamic formula
=OFFSET(_setCategory,0,1,1,_setCategoryCount)
This is a simple dynamic OFFSET formula to build a list of the categories.


7. Sets of Subcategory Items


Sets of sub-category items, one set per category.
K3:N3:[_setsSubcategory]=IFERROR(CUBESET(_model,”EXISTS([Product].[ProductSubcategoryName].Members,”&K$2&”)”,”Products:”&K$2),””)
This is where the MDX magic kicks in. The EXISTS function … Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified …, so we can use it within a CUBESET function to get all of the product subcategory names for the category above, in _listCategory.
Using this technique, instead of building a set of all subcategories, we build a set of the subcategories for a particular category; in our case the category passed to the CUBESET function from the cells in _listCategory.
I prefix the text shown with the text Products: for readability
The name definition uses a dynamic formula
=OFFSET(_setCategory,1,1,1,_setCategoryCount)


8. List of Subcategory Set Counts


A list of counts for the sub-category sets, for use in the layout.
K4:N4:[_setsSubcategoryCounts]=INDEX(CUBESETCOUNT(_setsSubcategories),COLUMN()-COLUMN(_setCategory))
The name definition uses a dynamic formula
=OFFSET(_setCategory,2,1,1,_setCategoryCount)


9. List of Subcategory Set Count Totals


A list of running totals for the sub-category set counts, for use in the layout.
K5:O5:[_setsSubcategoryTotals]=SUM($K$4:K4)-K$4+1
The name definition uses a dynamic formula
=OFFSET(_setCategory,3,1,1,_setCategoryCount+1)


10. Lists of Sub-category Sets Items


Lists of each set sub-category items, within the relevant category.
K6:N30:[_listSubcategoryies]=IFERROR(CUBERANKEDMEMBER(_model,K$3,ROW()-ROW(_setCategory)-2),””)
This is using the same technique to list the set items as in the previous blog.
The name definition uses a dynamic formula
=OFFSET(_setCategory,4,1,MAX(_setsSubcategoryCounts)-1,_setCategoryCount))
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.


11. Sales Measure


Finally, we have a cell defining the Sales measure
J7:[_sales]=CUBEMEMBER(_model,”[Measures].[Sum of SalesAmount]”,”Sales”)
This is to save repeated definition within the value formulae.


Figure 2 and Figure 3 below show all of the helper cells populated, and the nanmes defined in the NameManager.



Figure 2 – Populated Helper Cells




Figure 3 – Defined Names


It’s All About The Presentation


All that needs to be done now is to lay it out, allowing for a variable number of categories and a differing variable number of sub-categories within each category.


In my layout, I am repeating each category a number of times equal to to the number of subcategores for that category, but only showing the first instance. The years are shown as column headings, using the year slicer to filter the columns. The values are displayed referencing the category, sub-category, and year headings.


The formulae are as follows:


Category items


A7:An:=IFERROR(INDEX(_listCategory,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1)),””)
This matches the ordinal row number within the list of categories against the running totals for sub-category set counts, so as to determine which of the categories we are listing.

 


Sub-category items


B7:Bn:=IFERROR(IF(A7<>A6,””,INDEX(_listSubcategories,COUNTIF($A$7:A7,A7)-1,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1))),””)
This uses the same matching technique to determine which sub-category set to list.

 


Filtered Years


D6:H6:=IFERROR(CUBERANKEDMEMBER(_model,_setYear,COLUMN()-COLUMN(_start)-2),””)
Although the slicer controls the selected years, the year set in _setYear outsorts the data, the filtering is done there.

 


Sales Totals


C7:C50:=IFERROR(CUBEVALUE(_model,_sales,IF($A7<>$A6,$A7,$A7:$B7),Slicer_FiscalYear),””)
This determines the sum of the values for the current row, be that just a category row, or a category and sub-category row, whilst respecting the slicer selection. The IF in the formula caters for that possibility of a category row or a category and sub-category row.

 


Sales Values


D7:H50:=IFERROR(CUBEVALUE(_model,_sales,D$6,IF($A7<>$A6,$A7,$A7:$B7)),””)
Similar to the sales totals, but using the year heading instead of the slicer, beacuse the year heading has already take the slicewr into account.

 


Conditional formatting is added to the results to improve the viusual effect and readability.


Product Category Labels


The list of product categories is repeated on every row, changing at the appropriate point of course. This value is needed for the value calculation, but I feel the look is improved if the repeated items are suppressed, so I use conditional formatting to set the font colour to white on repeated items. This uses a simple formula of

A7:A50:=A7=A6


Product Category Totals


I list the product category one more time that its associated sub-categories. This is so that I can show category totals as well as category and sub-category totals. These need highlighting so that the totals stand out. To achieve this, I have made them bold with a blue font, borders above and below. The conditional formatting formula for this is

A7:H50:=AND($A7<>””,$A7<>$A6,ROW($A7)-ROW(_start)-1<=MAX(_setsSubcategoryTotals),COLUMN(A6)-COLUMN(_start)-3<=_setYearCount)


But Is It All Too Rich?


One question that may occur to anyone reading this who is skilled with pivot tables is, why bother? Couldn’t the same effect be achieved by formatting a pivot table showing the product category and sub-category and years with the format set to Show Tabular Form and Repeat All Item Labels? This would give a similar look, but it would only show product categories and sub-categories that have a value, it would not show those that are not selling. Agreed, there is a pivot option to Show items with no data on rows which does address this, but the problem here is that it will show all product sub-categories under EVERY product category, my method only shows the relevant sub-categories.


In Summary


In many ways this is similar to the previous post on CUBE formulae, with an extra level of data and more complex formatting. But what I hope that it shows is that by utilising some fairly straightforward MDX the formulae can do far more complex operations, and extend the data displayed. And who knows how far this can be taken as one’s MDX skills increase?


It is possible that many of the formulas could be replaced with DAX measures in the model, maybe that is a post for another time.


You can download the workbook here


Acknowledgements


Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.


Chris Webb has a series of excellent blog posts on MDX For PowerPivot Users, which are worth reading if this technique interests you.

PowerPivot BlogRoll – 6th January 2013



PowerPivot activity announced on Twitter for the week ending 6th January 2013.


 


Time To Name This Year’s Book! – bit.ly/TyZ5nX


Database Pros in Demand – bit.ly/UXN78m


[Commercial Webinar] Deep Dive into Business Intelligence in Excel 2013’s PowerPivot and Power View – bit.ly/115ls8J


Creating Linked Tables in PowerPivot for Excel – bit.ly/TyZ5nX


Excel 2013, Power View, Top 10 “long tail” and how DAX helpsbit.ly/YJTMkd

PowerPivot BlogRoll – 30th December 2012



PowerPivot activity announced on Twitter for the week ending 30th December 2012.


 


Templated Excel workbooks with PowerPivot and Macro’s – bit.ly/12VdWwP


Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel – bit.ly/UAX22u


PowerPivot – Importing Data from Different Data Sources into PowerPivot for Excel – bit.ly/131Qi0v


End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI – bit.ly/VpwWxI


Refreshing PowerPivot Data in SharePoint 2013 – bit.ly/131Q3Ti


[Video] Using PowerPivot with DataMarket Section of Windows Azure Marketplace – bit.ly/12Vd23e