Hi guys -
In testing a Tabular (2012 SP1) model I've been working on, I'm noticing that when invoking the drillthrough action in Excel (2010), it does not appear to be functioning as I expect when the Calculated Measure includes a CALCULATE expression with additional filter context applied. The value being displayed in the Pivot Table is correct, but when I drillthrough a cell, ALL values are being returned for the original expression within the CALCULATE expression for the filter context of the Pivot Table. It's like any additional filter context used in the Calculated Measure AFTER the CALCULATE expression is being ignored.
Here's a simple example I created in PowerPivot to illustrate my problem:
I have a simple Orders table:
Salesman OrderNumber Year Mike 3 2012 Mike 2 2012 Mike 32 2013 Bob 1 2012 Bob 6 2013 Tom 19 2012
I've created 2 simple Measures:
Next, I created the simple pivot table below:
Row Labels CountOrders CountMikesOrders 2012 4 2 2013 2 1 Grand Total 6 3
As you see, the values calculate as expected.
Next, I'll drill through CountOrders (does not include additional filter context in measure) for the 2012 row (expect 4 rows):
Data returned for CountOrders, 2012 (First 1000 rows). [$Orders].[Salesman] [$Orders].[OrderNumber] [$Orders].[Year] Mike 3 2012 Mike 2 2012 Bob 1 2012 Tom 19 2012
The returned values are as expected.
Next, I'll drill through CountMikeOrders (does include additional filter context in measure) for the same 2012 row (expect 2 rows):
Data returned for CountMikesOrders, 2012 (First 1000 rows). [$Orders].[Salesman] [$Orders].[OrderNumber] [$Orders].[Year] Mike 3 2012 Mike 2 2012 Bob 1 2012 Tom 19 2012
As you see, ALL rows for 2012 were returned, not just the rows for "Mike".
To me, this seems incorrect. I'm also sure it will seem incorrect to my users, as they will expect only the rows to be returned for the value they drilled through.
Is this functionality expected? Is there a way (either setting or DAX code) to get the appropriate records returned when applying additional filter context within a measure?
I appreciate any and all responses.
Thanks - Mike
Unfortunately, that's how it is designed to work currently. Drillthrough displays the rows from the current PivotTable filter context. It doesn't take into account any filters embedded in your DAX measures.
I don't have any really good option other than training your users what to expect. The best suggestion I have is to use BIDS Helper Tabular Actions Editor:
You can create an alternate action (not the default double-click drillthrough action) which returns a rowset. And you can painstakingly define a query which captures the filter context, manually applies the additional DAX measure filtering, and then returns a result. See the very last drillthrough rowset action example at the end of the page. Just to warn you, that example is only detecting the current filter context on one column in one table. Yours may have to do that for every column in every table. That's a lot of code and isn't very easily maintainable.
Unfortunately, Tabular models in AS2012 and forthcoming AS2014 can't use .NET assemblies. Otherwise we could automate detecting the current context and building a drillthrough command like we did for Multidimensional models here: https://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home
I would also recommend you vote for this Connect item to help the Analysis Services team prioritize addressing this in a future release: