mcornell13
New Member
- Joined
- Nov 1, 2013
- Messages
- 1
<!--[if !supportLists]-->Hi guys -<o
></o>
In testing a Tabular(2012 SP1) model I've been working on, I'm noticing that when invoking thedrillthrough action in Excel (2010), it does not appear to be functioningas I expect when the Calculated Measure includes a CALCULATE expression with additionalfilter context applied. The value being displayed in the Pivot Table iscorrect, but when I drill through a cell, ALL values are being returned forthe original expression within the CALCULATE expression for the filter contextof the Pivot Table only. It's like any additional filter context used inthe Calculated Measure AFTER the CALCULATE expression is being ignored by the drill through.<o></o>
Here's a simpleexample I created in PowerPivot to illustrate my problem:<o></o>
I have a simple Orderstable:<o></o>
<tbody>
</tbody>I've created 2 simpleMeasures:<o></o>
CountOrders:=COUNTROWS(Orders)<o></o>
CountMikesOrders:=CALCULATE(Orders[CountOrders],Orders[Salesman]="Mike")<o></o>
Next, I created thesimple pivot table below:<o></o>
<tbody>
</tbody>As you see, the valuescalculate as expected.<o></o>
Next, I'll drillthrough CountOrders (does not include additional filter context inmeasure) for the 2012 row (expect 4 rows):<o></o>
-----------------------<o></o>
<tbody>
</tbody>----------------------<o></o>
The returned valuesare as expected.<o></o>
Next, I'll drillthrough CountMikeOrders (does include additional filter context in measure) forthe same 2012 row (expect 2 rows):<o></o>
------------------<o></o>
<tbody>
</tbody>----------------------<o></o>
As you see, ALL rowsfor 2012 were returned, not just the rows for "Mike".<o></o>
To me, this seemsincorrect. I'm also sure it will seem incorrect to my users, as they willexpect only the rows to be returned for the value they drilled through.<o></o>
Am I missing some very obvious rule about filter context? Is this functionalityexpected? Is there a way (either setting or DAX code) to get the appropriatedrillthrough records returned when applying additional filter context within a measure?<o></o>
I appreciate any andall responses.<o></o>
Thanks - Mike<o></o>
></o>In testing a Tabular(2012 SP1) model I've been working on, I'm noticing that when invoking thedrillthrough action in Excel (2010), it does not appear to be functioningas I expect when the Calculated Measure includes a CALCULATE expression with additionalfilter context applied. The value being displayed in the Pivot Table iscorrect, but when I drill through a cell, ALL values are being returned forthe original expression within the CALCULATE expression for the filter contextof the Pivot Table only. It's like any additional filter context used inthe Calculated Measure AFTER the CALCULATE expression is being ignored by the drill through.<o
></o>Here's a simpleexample I created in PowerPivot to illustrate my problem:<o
></o>I have a simple Orderstable:<o
></o>| Salesman<o></o> | OrderNumber<o></o> | Year<o></o> |
| Mike<o></o> | 3<o ></o> | 2012<o ></o> |
| Mike<o></o> | 2<o ></o> | 2012<o ></o> |
| Mike<o></o> | 32<o ></o> | 2013<o ></o> |
| Bob<o></o> | 1<o ></o> | 2012<o ></o> |
| Bob<o></o> | 6<o ></o> | 2013<o ></o> |
| Tom<o></o> | 19<o ></o> | 2012<o ></o> |
<tbody>
</tbody>
></o>CountOrders:=COUNTROWS(Orders)<o
></o>CountMikesOrders:=CALCULATE(Orders[CountOrders],Orders[Salesman]="Mike")<o
></o>Next, I created thesimple pivot table below:<o
></o>| Row Labels<o></o> | CountOrders<o></o> | CountMikesOrders<o></o> |
| 2012<o></o> | 4<o ></o> | 2<o ></o> |
| 2013<o></o> | 2<o ></o> | 1<o ></o> |
| Grand Total<o></o> | 6<o ></o> | 3<o ></o> |
<tbody>
</tbody>
></o>Next, I'll drillthrough CountOrders (does not include additional filter context inmeasure) for the 2012 row (expect 4 rows):<o
></o>-----------------------<o
></o>| Data returned for CountOrders, 2012 (First 1000 rows).<o></o> | ||
| [$Orders].[Salesman]<o></o> | [$Orders].[OrderNumber]<o></o> | [$Orders].[Year]<o></o> |
| Mike<o></o> | 3<o></o> | 2012<o></o> |
| Mike<o></o> | 2<o></o> | 2012<o></o> |
| Bob<o></o> | 1<o></o> | 2012<o></o> |
| Tom<o></o> | 19<o></o> | 2012<o></o> |
<tbody>
</tbody>
></o>The returned valuesare as expected.<o
></o>Next, I'll drillthrough CountMikeOrders (does include additional filter context in measure) forthe same 2012 row (expect 2 rows):<o
></o>------------------<o
></o>| Data returned for CountMikesOrders, 2012 (First 1000 rows).<o></o> | ||
| [$Orders].[Salesman]<o></o> | [$Orders].[OrderNumber]<o></o> | [$Orders].[Year]<o></o> |
| Mike<o></o> | 3<o></o> | 2012<o></o> |
| Mike<o></o> | 2<o></o> | 2012<o></o> |
| Bob<o></o> | 1<o></o> | 2012<o></o> |
| Tom<o></o> | 19<o></o> | 2012<o></o> |
<tbody>
</tbody>
></o>As you see, ALL rowsfor 2012 were returned, not just the rows for "Mike".<o
></o>To me, this seemsincorrect. I'm also sure it will seem incorrect to my users, as they willexpect only the rows to be returned for the value they drilled through.<o
></o>Am I missing some very obvious rule about filter context?
Is this functionalityexpected? Is there a way (either setting or DAX code) to get the appropriatedrillthrough records returned when applying additional filter context within a measure?<o></o>I appreciate any andall responses.<o
></o>Thanks - Mike<o
></o>
