Using DAX studio to create PowerPivot measures
August 16, 2012 3 Comments
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:
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:
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:
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”:
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:
Now we are ready to add the second condition in DAX Studio, counting orders for Product ID “B” in Stored ID “2”:
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:
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.
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:
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:
If we visualize now the final result in a pivot table, we are able to confirm the results from our prior DAX query:
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/