PowerPivot, DAX and Semi-additive measures

PowerPivot, Project Gemini, SQL Analysis Services Add comments

Over the week-end I was doing some analysis on SQL Server disk usage, and wanted to be able to display current disk usage by database. Up to this point, I’d mainly been doing a SUM over  my measures. Well, that would make no sense in this scenario – I’d end up with a meaningless number just like I would if I took a SUM of an Inventory value.

I found myself dealing with a classic semi-additive measure. These are easy to deal with using SQL Server Analysis Services, but a tiny bit trickier in PowerPivot.

I tried various DAX-related ideas, and some actually kind of worked, but Marius Dumitru, a co-worker at Microsoft, suggested the best solution.

I happen to have a date dimension which we could lean on for this challenge – I just needed to find the last “Disk Use” value that had been recorded. Since all of my readings are associated with a date, slam dunk!

Here’s what I did:

Created a calculated measure which SUMs the measure in my table (I named it “Sum Disk Size”):

     = SUM(‘Fact_DatabaseMetrics’[Disk Size])

Created a second calculated measure which filters the first, looking for the value related to the last associated date in my date dimension (via DAX’s LastNonBlank function) . I didn’t know you could filter a measure so easily!

        = ‘Fact_DatabaseMetrics’[Sum Disk Size](LastNonBlank(‘Dim_Date’[DatePK], ‘Fact_Database’[Sum Disk Size])

The second measure is what I added to my PivotTable.

One Response to “PowerPivot, DAX and Semi-additive measures”

  1. Vincent Says:

    Thanks for the tip! However, was wondering whether you find a way to fix the Grand Total line as well? Seems like these DAX expressions result in a Grand Total showing latest disk size across all databases. So, for instance, if you had gaps in disk use dates such that say database A had use dates up until 10/31/09 and database B had use dates up until 12/31/09, then only database B’s disk use at 12/31/09 would be shown in the grand total.

Leave a Reply