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