Ungrouping Column using DAX in PowerPivot or SSAS Tabular

| 1 Comment

To start this blog post, let me first explain the problem. Consider a scenario where you receive an excel sheet or data in the following format

Category SubCategory
Bikes Mountain Bikes
Road Bikes
Touring Bikes
Components Handlebars
Bottom Brackets
Brakes
Chains
Cranksets
Derailleurs
Forks
Headsets
Mountain Frames
Pedals
Road Frames
Saddles
Touring Frames
Wheels
Clothing Bib-Shorts
Caps
Gloves
Jerseys

In the above case, the data is already grouped by Category. If you try to load this data in PowerPivot Data Model & try to create a Power view Column Chart report on Category, the view would look similar to shown below

image

Most of the data is grouped in Blank Category since the Power Pivot Data Model doesn’t know or understand that data was already grouped by Category.

To ungroup the data back in PowerPivot Data Model, I would first add an Index column to the existing data which can be easily added when you are loading the data using Power Query. So the Power Pivot Data Model now looks as shown below

image

Next, we add Calculated Column viz MaxIndex to calculate & store the index value of last non blank value of the Category for each row. The DAX calculation is shown below

=MAXX(FILTER(FILTER(Table1,LEN(Table1[Category])>0),Table1[Index]<=EARLIER(Table1[Index])),Table1[Index])

The output of the above calculation looks like following

image

The above table is now like a Parent-Child Hierarchy where MaxIndex is parent id. So to ungroup the Category column, we can now use LOOKUPVALUE Function as shown below

=LOOKUPVALUE([Category],[Index],[MaxIndex])

The output of the above calculation is as shown below

image

We can now hide the Category, MaxIndex & Index Columns from Client tools to make it invisible from Power view. Now if you visualize the same data in Column chart in Power view, the result is what we are looking for

image

Hope this helps !!!

Parikshit Savjani
Sr. Premier Field Engineer

One Comment

  1. Hey great post. I hope it’s ok that I shared this on my Facebook, if not, no issues just tell me and
    I’ll delete it. Regardless keep up the great
    work.

Leave a Reply

Required fields are marked *.