DAX Queries in Excel 2013 : using ROLLUP to cover some UNION scenarios

DAX does not offer a UNION function or operator. There are a few scenarios where this would be useful.

The scenario

Consider the following table called Games.


This is a very simple table. Basically, this table represents a relation between players, which can be stated as “Player 1 played against Player 2”. For now, I will ignore the meaning of why one player is listed in one column or the other.

In order to analyze this data, we will need a Player dimension, representing the set of all players, regardless of whether they were labeled as Player 1 or Player 2.

If the Games table came from a SQL connection, then we might as well use the following query to get the desired result.

SELECT [Player 1] AS Player FROM Games
SELECT [Player 2] FROM Games

The table would look like this.


(Remember that UNION only returns distinct rows).

A DAX solution

I already mentioned Excel 2013 supports DAX queries,  and the resulting tables can be re-used in the data model. Let us take a look at what we need to achieve the desired result.

First thing we need to care about, is that our result set might have more entries than our Games table. We will need to generate at least one row per distinct value in player 1 and 1 row per distinct value in player 2.

Now, imagine you create a PivotTable based on the Games table and put [Player 1] and [Player 2] on rows. This is what you will get:


For every distinct value of [Player 1], Excel will create a group, list all the values for [Player 2] within this group, and will add a row for the sub-total.

Since we cannot use the body range of a pivot table, as a data source, we will do this with a DAX query.

SUMMARIZE and ROLLUP will allow us to mimic this behavior.

The following DAX statement

, [Player 1]
, ROLLUP(  [Player 2])
, “Player”
, IF (ISSUBTOTAL(Games[Player 2]),[Player 1] , [Player 2])

will return the following table:


Note that ROLLUP([Player 2]) instructs PowerPivot to generate a subtotal row for the current value of [Player 1]. We also create a calculated column named Player, that will just take the value of [Player 2] for “regular” rows, and the value of [Player 1] for sub-totals. Also, ISSUBTOTAL allows us to differentiate subtotal rows from rows where [Player 2] is blank.

Finally, we just want to get the unique values in the Player column. For that, we will use, once again, the SUMMARIZE function.

The final query

, [Player 1]
, ROLLUP(  [Player 2])
, “Player”
, IF (ISSUBTOTAL(Games[Player 2]),[Player 1] , [Player 2])
,  [Player]

DAX Queries in Excel 2013

The “old” drill-through trick that could be used in Office 2010 and PowerPivot v2 no longer works in Excel 2013. You can no longer edit the connection for the resulting table.

Luckily, this is no longer required.

If you want to include a DAX query based on the model in your workbook, you can proceed as follows:

  • Go to Data > Existing Connections (EDIT: from the regular Excel ribbon, not the PowerPivot window.)
  • Select a table
  • When asked how you want to view your data, select “Table”
  • To edit the DAX query, right click on the table, then choose Table > Edit DAX …

If, like me, you get the error message “query could not run or the data model could not be accessed. Query (row, position) cannot reference a base table in the EVALUATE query, because it is undetermined”, check whether your query is correct.

The following query, for example, would run smoothly for the appropriate table.

ROLLUP( Table[Category],
“Sales”, COUNTA( Table[Subcategory])


Note that in this mode, the EN-US locales seem to be in use.

Finally, you can reuse the table in your model, if you wish. I have not tested how this affects the updating of the model, though.

What’s new for Excel 2013 – Personal observations

The customer preview of Office 2013 is finally available, and there is a lot of new things to look at, be it for users or for developers.

Here are a few personal observations based on my first look at the product.

  • PowerPivot for Office 2013  now comes bundled with Excel, making Office On Demand, the evolution of the Click-To-Run technology, much more viable.
  • The PowerPoint add-in is no longer required to build simple data models and pivot tables based on several data tables. Measures, KPIs and some other features will require the add-in to be activated.
  • The message “To use multiple table in your analysis, a new PivotTable needs to be created based on the Data Model.” hints at the fact that old-school pivot tables are not dead yet and still are the default PivotTable type.
  • New table objects in your workbook will automatically be added to your data model.
  • You can create a pivot table based on a simple range and then transform the pivot table into a “PowerPivot model”. The initial range will not be transformed into a table object. Further ranges can be added through the Connections manager. (What for?)
  • The data model can now be accessed from VBA. The corresponding object is called Model (not DataModel), despite what the MSDN documentation mentions about it.
  • The PowerPivot add-in will not recognize tables that were added directly to the data model from Excel as linked tables. However, a new connection will be created in Excel, so that refactoring can be done from the Connections manager. A corresponding object called WorksheetDataConnection is available in VBA.
  • You can create two worksheet data connections for the same range. Duplicating ranges for scenarios where the same data must be used twice (for example, when a dimension must play different roles) is no longer required.
  • The function FILTERXML allows you to query an XML document with XPath. The WEBSERVICE function returns an XML text from a web service. ENCODEURL is a helper function to encode a string into a valid URL. All three functions are available in VBA through the WorksheetFunction object.
  • The function FILTERXML returns an array. You can use it in array formulas, or in conjunction with the INDEX function, for example.
  • New functions like SHEET, and SHEETS are available, although it is still unclear to me in which scenarios they will be useful, since no specific function takes a sheet index as a parameter.
  • ISFORMULA and FORMULATEXT are also new in Excel 2013.
  • Examples in the help system are provided as embedded Excel Web App workbooks. This may solve some translation issues that have occurred in the past for non-English Office versions.
  • Internet Explorer is used to navigate the VBA help. You can now alt-tab between the VBA environment and the documentation.
%d bloggers like this: