Tabular pivot table in excel how to#

Violet part – add measure into our drill down result to get values user wants to see.For this purpose I created Semi-Automated solution in excel which I’ll explain further. (in this sample we implemented only CustomerID) We need this part for each attribute in our SSAS which can be used in excel for analyses. Orange Part – will basically pass currently selected members into my analyses.Therefore we can pass current member value from MDX statement. In our case we expect this measure will always be used with Calendar Year and Calendar Quarter attribute selected in your analyses. We need to pass the right context into this filtering criteria.

Tabular pivot table in excel how to#

DAX query directly is not supported for Custom Actions but you can define DAX query in Custom Actions Editor via RowSet and writing DAX into a string as you can see in the following figure.įigure 4 – How to Add DAX Query Custom Action into SSAS TabularĪs our DAX query is a string combination with MDX code (which evaluates again to string) it is a good idea to write a skeleton for query in DAX Editor first to test functionality.ĬalculateTable command to implement custom logic of measure (in our case PreviousQuarter function filter. But first we need to create a query which would perform custom action query. Tabular model do not have out of the box custom actions but thankfully there is a bids helper which adds this functionality to SSAS Tabular. This means we have to check for current context and we have to do this for every selected value in each attribute during the DrillDown action. Unfortunately we need our custom action to work in any combination of chosen dimensions/attributes. But the short answer has quite a long explanation behind it. The short answer is we need to implement custom action. So what can we do about this? SSAS Custom Actions However if I double click on the red cell I got only an empty result-set/sheet as we can see in the following figure.įigure 3 – Empty Result-set for Complex Measure See sum amount in the following figure.įigure 2 – Simple Measure DrillDown Result-set If I double click on the green cell in the example above I get the right list of detailed records. In the following picture we can see usage of the measures in simple Pivot Table analyses.įigure 1 – AW Quarter Sales Measures in Pivot Table Internet Previous Quarter Sales := CALCULATE ( , PREVIOUSQUARTER ( ‘Date' ) ) Internet Current Quarter Sales := TOTALQTD ( , ‘Date' ) (AW Internet Sales Tabular Model 2014) we have two measures to compare here: Current and Previous Quarter values.

We can simulate this behavior quite easily on Adventure Works samples. Out of the box DrillDown behavior of Pivot Table works well for simple measures but when you use even a slightly complex calculation in your DAX measure you will get an empty dataset or even worse – wrong data. I’m not sure how many users use this in real life, but our users do. However, it brings challenges as well ,such as proper implementation of DrillDown functionality from excel pivot table.

Using SSAS Tabular and Excel Pivot table is a common scenario in real life.