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:
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.
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.
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….
Does a non-dynamic query table work ok?
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.
Do we have a similar example for MDX dynamic filtering?
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.
Great post, thanks. Just wondering if VBA is still the only way to achieve this?
Yes, if you want to use a DAX query to get data from the Excel Data Model.
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.
I don’t know of one, sorry
I found the answer. In your code above instead of
.CommandText = Array(DAXQuery)
use
.CommandText = DAXQuery
because Array() has a limitation of 255 characters for each element.
Mr. Webb
May I ask a question 10 years later? I used the process above with success and appreciate you providing this solution.
The two statements are of interest to create the DAXQuery.
DAXQuery = “evaluate Filter(DimDate, DimDate[DayNumberOfMonth]>” &
DayNumberOfMonthFilter
DAXQuery = DAXQuery & ” && (” & SelectedList & “)) order by DimDate[DateKey]”
I have DaxCode form DAX Studio that is 2-3 pages and works well when physically inserted into an excel table as performed in your Step 2.
Can I create a function (or similar) in VBA to hold the contents of the dax query to use as you suggest above. I am having much trouble trying to incorporate such a large amount of rows; it is obvious, 2-3 lines of DAX is easily implemented.
Can you suggest how I may proceed? Thanks for your insight and great web resource.