In my post two weeks ago on the new modern chart types in Excel 2016 (e.g. Treemap, Sunburst, Waterfall, etc) I left out the following two important points:
The New Chart Types can not source from a Pivot
So if you have a Power Pivot data model you can’t simply create a pivot chart, drag and drop the fields you want from the PivotChart fields list, and switch the chart type like you would in Power BI.
For these new charts, for now at least, you need a range of cells or a data table as a source. Why is this worth blogging about? Because this ‘detail’ wasn’t covered at MS Ignite and someone could easily go away with the conclusion that in Excel 2016 you can quickly build a dashboard or interactive report with these new visuals. Assuming you’re using the Excel Data Model (originally Power Pivot) rather than flattened tables (you should be), you have some extra work to do.
DAX Queries to Support New Chart Types
Did you know you can load an Excel data table from a DAX query? The DAX query can be pointed at the Power Pivot data model inside the workbook or you could even point it to an external SSAS instance via an ODC file. For this blog post, we’re only going to cover the local DAX to Power Pivot model method.
Here’s the chart. Steps for building a DAX query to support it are included below.
The chart is connected to a Data Table, which uses a DAX query against the local data model:
Steps for building the DAX Query:
- Data – Existing Connections – Tables
*Optionally filter on Connection Tables only.
Select one of the small tables of your model (e.g. dates/calendar) and click ‘Open’
In the Import Data dialog, select ‘Table’ and OK.
The table of data from your data model will be loaded to the Excel worksheet as a Data Table. (You’re almost there)
Right-click a cell within the table – Table – Edit DAX
Now you need to A) change the Command Type from Table to DAX and B) write or paste your DAX query expression into the dialog.
*Obviously this is not an Integrated Development Environment (IDE) like Visual Studio so you might prepare and test your DAX query in another tool like DAX Studio.
Click OK and the table generated is the results of your DAX query
At this point you can simply point which new Excel 2016 chart you wish to use. In this case, I deliberately retrieved three levels of a hierarchy to support the Sunburst so the shape of your query will of course vary based on the intended visualization.
Note: In Power BI Desktop you can write local DAX query/table expressions via the following.
Besides the local DAX query approach described in this blog, I might also consider building a pivot table with an appropriate structure and then converting it to Cube Functions. The chart could then reference this range and you could connect the functions to slicers if necessary.
Two broader takeaways:
- It’s good that PBI, SSRS, and Excel share similar visuals but as I’ve said for several weeks now we have to remember that these are separate tools intended for different use cases so you shouldn’t assume or expect the behaviors or options to be the same.
- DAX as a query language has always been a very powerful technique and is now greatly enhanced by the new functions in SSAS 16′, PBI, and Excel 2016 including the SUMMARIZECOLUMNS example as well as UNION, EXCEPT, INTERSECT, and many others. (See New DAX Functions.)
I’ll be using DAX table functions in queries from SSRS to SSAS Tabular in future posts.