Using DAX studio to create PowerPivot measures

The newly released DAX studio is an excellent tool to develop and troubleshoot DAX calculations as it provides a way to visualize tables as they are internally represented during the computation.

The more advanced your DAX calculations, the more you will need to use intermediate ‘filtered tables’ in order to arrive to a specific output, and this tool allows you to better understand how your filtering actions are being applied across the tables and rows that become active during the evaluation of the expression.

Allow me to give an example. Take the following data:

image

The first table on the left registers orders by store, product and vendor.  The two additional tables are lookups.

Once loaded into PowerPivot, this is how the data model looks like:

image

We want to find out how many vendors sold Product ID “A” in Store ID “1” AND Product ID “B” in Store ID “2”. Typically, you would develop & troubleshoot this PowerPivot measure by using pivot tables as your vehicle to test each step of the calculation, leveraging functions like COUNTROWS to understand how many rows are actually being filtered by a given context.   Not a terrible approach, but you are left wondering if there is a better way, a way to take a peek at the in-memory tables and see how how they look like given the application of a certain amount of filters.

This is what DAX Studio can do for us.  Connecting it to the sample data model above, we can start with a simple expression to get a distinct list of StoreIDs:

image

We are using DAX as a query language here so we must start with the EVALUATE clause.

As a next step, let’s write a slightly more complex DAX query to calculate a column that will count how many times each vendor ordered Product ID “A” in Store ID “1”:

image

As you can see, column [Store1_ProdA] shows which vendors in the list had orders with the required condition.  We can double check this is indeed correct by going back to the PowerPivot window and applying filters in the grid view; We are able to verify the vendors flagged above accurately represent the data indeed:

image

Now we are ready to add the second condition in DAX Studio, counting orders for Product ID “B” in Stored ID “2”:

image

Remember, once we are done developing the calculation this table will not be shown to the user.  However, being able to look at it allows us to better understand how PowerPivot will represent the data on the fly as it is computing an output for any given context.

As what we want to do is count the vendors in which these two conditions apply, we can add a FILTER function to wrap our table:

image

And we have our result.  We must now convert this DAX query expression into a DAX measure.  For that, we must understand that DAX measures return scalar values, whereas DAX Studio requires tables to display on the grid.  To arrive to a scalar value from here, we can simply wrap the expression with COUNTROWS. 

Before we switch back to the Excel to do this, though, lets visualize the resulting scalar value in DAX Studio.  Remember, DAX Studio needs a table to evaluate, so we must wrap our COUNTROWS expression with the ROW function. This part won’t be needed in the measure, we are using it here only to visualize the single count value within the grid window of DAX Studio.

image

If we want to verify the expression is properly allocating context when adding another lookup element, we can add a SUMMARIZE function to the query.  With this, we are able to verify the count is assigned correctly to the one day in which the conditional expression proved to be true:

image

We are now ready to move the expression into a DAX measure.  Here, we will only be taking the part that starts with COUNTROWS, as the rest was needed just for testing.  This is the final expression as it looks in the DAX measure window invoked from the PowerPivot field list:

image

If we visualize now the final result in a pivot table, we are able to confirm the results from our prior DAX query:

image

In summary, DAX Studio is a great tool for developing (and troubleshooting) calculations that require the use of intermediate tables, which is generally the case for advanced DAX development. In the end, DAX is mainly about filtering (row, query and filter context) and this tool allows us to see those filtered tables as we work our way through a computation.  

Many thanks to Marco Russo, Darren Gosbell, Paul te Braak and Grant Paisley for their effort in developing this awesome tool, available for download here: http://daxstudio.codeplex.com/

About these ads

4 Responses to Using DAX studio to create PowerPivot measures

  1. Marco Russo says:

    Great example.
    What I usually do is to create a file containing DAX queries where in each query I define exactly the same measure I will punt in PowerPivot/Tabular within a DEFINE MEASURE condition. In this way, there is no ambiguity about what should be copied in the measure; moreover, the DAX syntax also contains the name of the measure, which is particularly useful when a single query (or a single file) contains definitions for multiple measures.

  2. Pingback: DAX Studio Review | Ms SQL Girl

  3. Siraj Samsudeen says:

    Thanks Javier for this detailed example on where DAX Studio can be used. I downloaded the tool and wanted to understand how best to use it and your example gave me a good idea.

  4. Pingback: DAX Studio – Mächtiges Add-In für Power Pivot | MS Excel | Power Pivot | DAX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: