DAX Table Query Example

As I referenced in my previous blog entry, we should take a look at DAX Table Queries.  In the world I live in, which is really focused on Power Pivot, you really don’t see these queries too often.  It probably doesn’t help that it’s a rather “hidden” feature in Excel.  I get the feeling it is more common to write these queries when doing work against a Tabular server, but that’s typically isn’t my space.

Still, I think there are some interesting reasons to use them in Excel… if nothing else just because it is kinda neat    In the previous article, this came up in the context of “detail reports”.  Those reports that have lots of cells can be a performance problem, for the simple fact that each and every cell in the pivot table is evaluated completely independently.  This gives us a way to generate all the data with just one query.

Here we see a pivot table with Category, Subcategory, CalendarYear and MonthName on rows,  Countries on columns, and Sales LTD (Lifetime to Date) for our values.

Let’s work through the magic to soooooorrrta get the same thing through a DAX query.  Oh, now would probably be a good time to mention this works in Excel 2013 only.  (1) On the Data Tab, (2) Click “Existing Connections”, then the (3) Tables tab on the popup.  Then here is the where I always get confused    I always want to click on the top entry, but what you actually need to do is (4) double-click any table from your model.   I wouldn’t pick the Sales table    It is going to bring in all the data from that table.

On the Import Data dialog that shows up, as long as you see “Table” enabled and selected, you are happy and you can just click “OK”.   When I mess up… it is not enabled, nor selected.  Then I am sad.  Don’t be sad.

At this point, you will see a table of (say) your Calendar data.  Which, of course, isn’t what you want.  So… you right-click on any cell in the table, and find this top-secret option to “Edit Dax…” which will bring up a dialog where you can edit the text of your query.

Our goal is to replicate the pivot table from above, which I will be honest… I have no idea close we can get to that goal.  I’m only qualified to pull the data out in some shape.  Below right you can see the dialog where you can edit the DAX, and the query I have used.  It is a straight-forward call to Summarize. 

We pass the Sales table into the first param of Summarize, then all the columns to use for our dimensions.  We are allowed to use these columns from other tables, for the simple reason that they are related.  If we tried to use a column from a table other than one of our lookup tables, we would get an error.

And if we can ignore the margins that are too large on my blog, and the font that is too big, and the screen shots that take up like all the space that I want to use for writing… you will see the final result. (Yes, I am working on that problem – if you are reading this in the future, and it makes no sense, hurray!  I was successful!)

So, ignoring the collapsible hierarchy fields in the original pivot table… this still looks quite a bit different, for the simple fact that the original pivot table had the Country field across columns, instead of on rows like we have here.  I suppose I could write some Country specific measures [Sales YTD – Canada] but that certainly feels completely like cheating.  If there is some magic way to add the Years across columns of the table, somebody please tell me

Aside from the different shape… the other sadness here is the lack of slicers.  Not that we had them in our initial pivot table, but we could.  Here we can’t.  Well, that isn’t totally true.  Clearly, you would have to be a sick freak to use a disconnected slicer and VBA to fake it, but… well, Chris Webb is that kind of sick freak.

I will close with something I learned from  Marco Russo and Alberto Ferrari.  I want to say it was in one of their books, but I have read so many books and articles, and watched so many videos, that I can’t keep them straight anymore.

When all is said and done, the output of our DAX Query is a table in Excel.  You know what you can do with tables in Excel?   Of course you do.  You can use them in your Power Pivot model as a Linked Table.  And that will completely work with these crazy tables made via DAX.   You would be taking data out of your model, running the query on it to create a new table, then linking that new table back into the power pivot model.   It’s seems crazy, but it totally works.

In our example here, we have taken daily data and converted into Monthly data (by category, subcategory and country).  We can then bring that back to Power Pivot to do some month-granularity calculations (say, against monthly budget) and kick-butt performance levels since we have so many fewer rows.     It is neat, it is crazy, it is just what you expect out of those wacky Italians.