Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Dynamic DAX Query Tables in Excel 2013

with 9 comments

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.

image

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:

image

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.

image

image

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:

image

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:

evaluate
Filter(
DimDate
, 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
    Next
    '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
    ActiveWorkbook.Connections("ModelConnection_DimDate").Refresh
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.

Written by Chris Webb

February 15, 2013 at 10:24 pm

9 Responses

Subscribe to comments with RSS.

  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

    gregkramer1

    February 16, 2013 at 6:31 pm

    • 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.

      Chris Webb

      February 17, 2013 at 9:53 pm

  2. [...] there’s more!  You also use DAX Queries to generate tabular output from a plain Data Model, see Chris Webb’s example. Again, a [...]

  3. [...] by Chris Web’s blog post about Dynamic DAX Query Tables in Excel 2013 – link – I have tried to create a dynamic table from PowerPivot data without using VBA so it would be [...]

  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 …

    ActiveWorkbook.Connections(“ModelConnection_DimDate”).Refresh

    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….

    Ted Murphy

    February 11, 2014 at 8:28 pm

    • Does a non-dynamic query table work ok?

      Chris Webb

      February 11, 2014 at 8:33 pm

      • 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.

        Ted Murphy

        February 11, 2014 at 10:02 pm

  5. Do we have a similar example for MDX dynamic filtering?

    Veerendra Thati

    July 7, 2014 at 6:33 pm

    • 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.

      Chris Webb

      July 7, 2014 at 10:04 pm


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 3,113 other followers

%d bloggers like this: