none
Tabular Drillthrough Not Applying Filters Used In CALCULATE Expression?

    Question

  • 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:

    CountOrders:=COUNTROWS(Orders)

    CountMikesOrders:=CALCULATE(Orders[CountOrders], Orders[Salesman]="Mike")

    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

    Friday, November 01, 2013 5:07 PM

All replies

  • 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:

    https://bidshelper.codeplex.com/wikipage?title=Tabular%20Actions%20Editor&referringTitle=Documentation

    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


    http://artisconsulting.com/Blogs/GregGalloway

    Wednesday, November 27, 2013 3:19 PM
    Moderator
  • I would also recommend you vote for this Connect item to help the Analysis Services team prioritize addressing this in a future release:

    http://connect.microsoft.com/SQLServer/feedback/details/696139/account-for-dax-filters-calculations-in-drillthrough-powerpivot


    http://artisconsulting.com/Blogs/GregGalloway

    Wednesday, November 27, 2013 3:21 PM
    Moderator
  • This is a showstopper, especially since the dimensional version of SSAS has always included this functionality.  Will it be addressed soon?

    Ben Lezin

    Monday, July 27, 2015 6:34 PM
  • Ive just come across this issue now in SSAS 2014. Unbelievable
    Tuesday, September 15, 2015 9:08 AM
  • This is very disappointing. Is it  fixed in SQL2016?
    Thursday, October 22, 2015 6:07 PM
  • This is a showstopper, especially since the dimensional version of SSAS has always included this functionality. 
    No it hasn't. In fact you can't do drillthrough on calculated measures at all in Multi-Dim SSAS.

    http://darren.gosbell.com - please mark correct answers

    Thursday, October 22, 2015 11:57 PM
    Moderator
  • There is one work around. If you use the tabular action editor in BIDS Helper http://bidshelper.codeplex.com

    You can create a rowset action which includes logic which simulates the same filtering as your measure.

    It takes a fair bit of code, but it does work. I've posted a partial sample (the full sample exceeds the maximum post size allowed on this forum) here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0164ce52-071b-4f74-a1b2-9113e5b0f63e/drilling-down-on-cumulative-measures?forum=sqlanalysisservices#ddb54608-b936-455b-83c1-01d526a3eea2


    http://darren.gosbell.com - please mark correct answers

    Friday, October 23, 2015 12:01 AM
    Moderator
  • I was working on this issue for some time and come with following conclusions. I believe it will be helpful for some one in the future:

    Analysis services drill through action ignores DAX calculations defined in the measures by design.

    Instead, it will return rows from the tables within the default context inferred by rows, columns, and filters on the report.

     

    As a workaround to get exact number of rows, we will have to define rowset action on measure group. This will look for all the filters in the model for every dimension and evaluate result to give same number of rows in the drill through.

    However this is complicated, hard to maintain and there will be performance impact on the drill through action.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Wednesday, April 06, 2016 4:25 PM
  • Hi

    You get Answer For your Question ? If you know can you share with us.

    3 hours 17 minutes ago