Dynamic DAX Query Tables in Excel 2013

PivotTables are all well and good, but sometimes when you’re building reports you just want a plain old list of things. Excel tables are perfect for this, and in Excel 2013 you can bind a table to the results of a static DAX query against the Excel Data Model. Unfortunately it’s not possible to make this query dynamic without a bit of VBA – so in this post I’ll show you how to do it.

Before I start, though, you may be thinking “What’s the point of this?”. After all, if you have too much data for the native Excel table functionality to handle, you can always use the Excel Data Model and make a PivotTable look just like a table, and when you do that you can use filters, slicers and so on to control what gets displayed. This is certainly a valid approach but the big disadvantage of a PivotTable is that it doesn’t always give you the best possible performance because of the way it generates its MDX, and because DAX queries are anyway faster than MDX queries for this kind of detail-level reporting. For large tables with lots of columns then a hand-rolled DAX query might give you significantly better performance than a PivotTable, as well as more control over the filtering logic.

Let’s look at a worked example…

Step 1: Import some data into a table

For my example, I have imported the DimDate table from the Adventure Works DW database in SQL Server into a table in Excel.


The key thing to remember at this point is to make sure you check the box to add the data to the Excel Data Model:


Step 2: Define a DAX query for this table

Kasper shows here how to use a static DAX query to populate a table in Excel, so I won’t repeat what he says. All I’ve done in my example is to change the table to use the following DAX query:

evaluate DimDate

…which returns the whole contents of the DimDate table, so in fact at this point the table looks exactly the same as it did before I made this change.



Step 3: Add some UI to allow the user to filter the data

Now I want the user to be able to filter this table in two ways:

1. By using a slicer to control which days of the week are displayed

2. By entering a value into a cell, and filtering the table so only the rows where the day number of the month is greater than that value

Here’s what this looks like:


I’ve also added a ‘Run Report’ button onto the worksheet for the user to press when they want to refresh the data in the query

Step 4: Use VBA to dynamically generate the query used by the table

The challenge is now to take the selection in the slicer and the value entered for the day number of month filter and use that to construct a DAX query.

Here’s an example of what one of these DAX queries might look like:

, DimDate[DayNumberOfMonth]>21
&& (DimDate[EnglishDayNameOfWeek]=”Monday” || DimDate[EnglishDayNameOfWeek]=”Saturday”))
order by DimDate[DateKey]

Here I’m filtering the DimDate table so that the only rows displayed are where day number of month is greater than 21, and day name of week is either Monday or Saturday. If you’re interested in learning more about writing DAX queries, check out the series of blog posts I wrote on this topic here.

Paul te Braak has a great post here on how to work out what has been selected in a slicer using VBA, and I need to acknowledge the fact I’ve borrowed some of his code! Here’s my VBA routine, called by the button on the worksheet, to build and run the query:

Sub RunReport()
    Dim SC As SlicerCache
    Dim SI As SlicerItem
    Dim SelectedList As String
    Dim DayNumberOfMonthFilter As String
    Dim DAXQuery As String
    Dim DemoWorksheet As Worksheet
    Dim DAXTable As TableObject
    Set DemoWorksheet = Application.Worksheets("TableDemo")
    'Find the value of the cell containing the Day Number Of Month filter value
    DayNumberOfMonthFilter = DemoWorksheet.Range("DayNumberOfMonthFilter").Value
    'Find what is selected in the slicer Slicer_EnglishDayNameOfWeek
    Set SC = ActiveWorkbook.SlicerCaches("Slicer_EnglishDayNameOfWeek")
    SelectedList = ""
    'Loop through each item in the slicer and if it is selected
    'add it to a string that will be used in the filter condition
    For Each SI In SC.SlicerCacheLevels(1).SlicerItems
        If SI.Selected Then
            If SelectedList <> "" Then
                SelectedList = SelectedList & " || "
            End If
            SelectedList = SelectedList & "DimDate[EnglishDayNameOfWeek]=""" & SI.Caption & """"
        End If
    'Construct the DAX query
    DAXQuery = "evaluate Filter(DimDate, DimDate[DayNumberOfMonth]>" & DayNumberOfMonthFilter
    DAXQuery = DAXQuery & " && (" & SelectedList & ")) order by DimDate[DateKey]"
    'Bind the table to the DAX query
    Set DAXTable = DemoWorksheet.ListObjects("Table_DimDate").TableObject
    With DAXTable.WorkbookConnection.OLEDBConnection
        .CommandText = Array(DAXQuery)
        .CommandType = xlCmdDAX
    End With
    'Run the query
End Sub


And so there we go, a dynamic DAX table report in Excel 2013. If you’d like to download my example and check it out in detail, you can get hold of it here.

15 responses

  1. When I tried to recreate this example, I received a SlicerCacheLevels object-defined-error when I ran the code….wonder what my be going on there. Thanks in advance

    • Do you have multiple slicers in your workbook? Check out Paul te Braak’s post on slicers and VBA that I link to, that will give you an idea of what SlicerCacheLevels are.

  2. Pingback: Look’s like Star Schema’s back on the menu! | Gobán Saor

  3. Pingback: Create a dynamic table from powerpivot data in Excel 2013 without vba « Erik Svensen

  4. Going back in time here to Feb 2013 blog. Did not have PowerPivot on my radar at that time!

    Trying to get the Dynamic Query Tables in Excel 2013 to work.
    No VBA experience … am getting Run Time Error 1004 …Application-defined or object-defined error on the last line of the VBA code in RunReport …


    Would like to use the technique when creating Dashboards … so I would appreciate any help in getting it working.

    When I tested initially I did not have any PivotTable in the Workbook and so had to use the Excel Slicer definition … this caused a problem in the “For Each SI In SC.SlicerCacheLevels(1).SlicerItems” line of the VBA Code. When I created a “PowerPivot” slicer for the Slicer_EnglishDayNameofWeek object … I got to the last line in the VBA Code as above,

    Would appreciate any advice or guidance you can provide….

      • No. You are sharp!

        Thanks I have identified the problem … my AdventureWorks database was taken from Access and there were field name discrepancies. Both static and dynamic appear to be working ok now.

        In the meantime I watched your recent Power Query presentation … certainly blows my mind. Looking forward to your book … I hope it will cover the basics vie the UI as well as the more advanced M.

        Thank you.

    • I don’t have an example but it would be pretty much the same, just that you would use an MDX query rather than a DAX query. However it would be even easier to use named sets in a PivotTable to get the same result.

  5. Pingback: DAX Query Tabellen in Excel 2019 - PowerBI Pro

  6. It seems like in case of DAX requests via VBA there’s a 255 character limitation, meaning that if constructed in VBA DAX query is longer than 255 characters the VBA gives an error “type mismatch”. Is there any workaround to resolve this? I work in Excel 2016.

  7. I found the answer. In your code above instead of
    .CommandText = Array(DAXQuery)
    .CommandText = DAXQuery
    because Array() has a limitation of 255 characters for each element.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: