Role Playing Dimensions – DAX vs MDX (Part 1)

A couple of weeks ago I was doing some work on an internal reporting cube. One of the measures required represents an ‘order backlog’, that is orders that have been received but haven’t yet been provisioned in our systems.

The Problem

The fact table looks something like this:


A row will appear in the fact table after the order has been closed, with the provisioned date being set to NULL until it has been provisioned. The provisioned date is updated once the order has progressed in the system.

There are a few measures that I want to report on:

  • Won (£) – very simple SUM of the Amount column
  • Provisioned (£) – the amount that was provisioned in a given month
  • Accumulated Backlog (£) – The total amount, for each month, of orders not yet provisioned

For example, in January 2013:

  • Won (£): 550
  • Provisioned (£): 150
  • Backlog Change (£): 400 (Won – Provisioned)
  • Accumulated Backlog (£): 1,250 (400 + 850 from December)

The problem comes when wanting to show the Accumulated Backlog amount by the ClosedDate.

The Solution DAX and PowerPivot

Accomplishing the end result with PowerPivot turned out to be very straightforward and in my opinion much easier to implement than the MDX solution.

The first thing I did was load my tables into PowerPivot, create the relationships between them and I have also created a Calendar hierarchy in my Date table.


Take note that there are two relationships between the Sales table and the Date table, the active relationship is on the ClosedDate column in Sales, inactive relationship is on ProvisionedDate.

The reason for this is because I want all my reports to be based on the ClosedDate of a sales order.

Calculating Won (£) is very simple, as it is based on the ClosedDate all we need is a simple SUM:

Won (£) := SUM(Sales[Amount])

To calculate Provisioned (£) involves using the CALCULATE and USERELATIONSHIP functions, this formula below, tells PowerPivot to calculate the [Won (£)] field using the relationship between Sales and Date based on the ProvisionedDate

Provisioned (£) :=
[Won (£)],
USERELATIONSHIP ( ‘Date'[Date], Sales[ProvisionedDate] ),
NOT ( ISBLANK ( Sales[ProvisionedDate] ) )

Instead of using [Won (£)] in the above formula I could have very easily used SUM(Sales[Amount]), but I thought that using [Won (£)] reads better, because the provisioned amount is the amount of “Won” orders that had been provisioned.

To calculate Backlog Change (£) is then simply:

Backlog Change (£):=[Won (£)]-[Provisioned (£)]

And finally the Accumulated Backlog (£), this formula will calculate the running SUM of the [Backlog Change (£)] field for all dates, in the given context.

Accumulated Backlog (£) :=
[Backlog Change (£)],
ALL ( ‘Date’ ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )

The resulting pivot table is exactly what I was expecting


With the basics implemented, I could go on to add some other interesting calculations, for example: age of oldest unprovisioned order or average time to provision order.

In the next post I will show how I implemented the above using SQL Server Analysis Services and MDX.


Scraping the Web with Excel 2013 – PowerBI Competition Entry

The Microsoft PowerBI Competition is now in full swing with the voting open to the public for the next week. (Check  out my entry).

As you can see below I just made my submission in time.

I like to cut it fine!

I like to cut it fine!

When I came to building my demo (check it out) I had a few different data sets in mind, but there were two main points that I wanted to highlight from my entry –

  1. How Power Query makes scraping webpages and extracting data into Excel really easy
  2. How Power View can be used to build visually stunning and interactive reports.

I ended up going with the National UFO Reporting Center ( They have very helpfully made their database available in plain old HTML tables, which makes it a rather trivial to retrieve using Power Query.

The biggest takeaway that I had from doing this is that doing screencasts is difficult! I have a new found appreciation for the Pluralsight authors who do an amazing job of lining up the timing of doing something on screen and narrating it.