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 By using a slicer to control which days of the week are displayedĢ. Now I want the user to be able to filter this table in two ways:ġ.
Step 3: Add some UI to allow the user to filter the data …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. All I’ve done in my example is to change the table to use the following DAX query: Kasper shows here how to use a static DAX query to populate a table in Excel, so I won’t repeat what he says. Step 2: Define a DAX query for this table 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: 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.įor my example, I have imported the DimDate table from the Adventure Works DW database in SQL Server into a table in Excel. 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. 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.
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.īefore I start, though, you may be thinking “What’s the point of this?”. 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. PivotTables are all well and good, but sometimes when you’re building reports you just want a plain old list of things.
0 kommentar(er)
