Drillthrough Not Applying Filters Used In CALCULATE Expression?

M

mcornell13

New Member
Joined
Nov 1, 2013
Messages
1
<!--[if !supportLists]-->Hi guys -<o:p></o:p>
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:p></o:p>
Here's a simpleexample I created in PowerPivot to illustrate my problem:<o:p></o:p>
I have a simple Orderstable:<o:p></o:p>
[TABLE="class: MsoNormalTable, width: 210"]
<tbody>[TR]
[TD="width: 81"]Salesman<o:p></o:p>
[/TD]
[TD="width: 109"]OrderNumber<o:p></o:p>
[/TD]
[TD="width: 89"]Year<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mike<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
3<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2012<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mike<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2012<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mike<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
32<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2013<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bob<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2012<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bob<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
6<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2013<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tom<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
19<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2012<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]
I've created 2 simpleMeasures:<o:p></o:p>
CountOrders:=COUNTROWS(Orders)<o:p></o:p>
CountMikesOrders:=CALCULATE(Orders[CountOrders],Orders[Salesman]="Mike")<o:p></o:p>
Next, I created thesimple pivot table below:<o:p></o:p>
[TABLE="class: MsoNormalTable, width: 229"]
<tbody>[TR]
[TD="width: 92"]Row Labels<o:p></o:p>
[/TD]
[TD="width: 87"]CountOrders<o:p></o:p>
[/TD]
[TD="width: 127"]CountMikesOrders<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2012<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
4<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2013<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
2<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD]Grand Total<o:p></o:p>
[/TD]
[TD]
6<o:p></o:p>​
[/TD]
[TD]
3<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]
As you see, the valuescalculate as expected.<o:p></o:p>
Next, I'll drillthrough CountOrders (does not include additional filter context inmeasure) for the 2012 row (expect 4 rows):<o:p></o:p>
-----------------------<o:p></o:p>
[TABLE="class: MsoNormalTable, width: 352"]
<tbody>[TR]
[TD="width: 344, bgcolor: transparent, colspan: 2"]Data returned for CountOrders, 2012 (First 1000 rows).<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][$Orders].[Salesman]<o:p></o:p>
[/TD]
[TD][$Orders].[OrderNumber]<o:p></o:p>
[/TD]
[TD][$Orders].[Year]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD]Mike<o:p></o:p>
[/TD]
[TD]3<o:p></o:p>
[/TD]
[TD]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mike<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD]Bob<o:p></o:p>
[/TD]
[TD]1<o:p></o:p>
[/TD]
[TD]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tom<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]19<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2012<o:p></o:p>
[/TD]
[/TR]
</tbody>[/TABLE]
----------------------<o:p></o:p>
The returned valuesare as expected.<o:p></o:p>
Next, I'll drillthrough CountMikeOrders (does include additional filter context in measure) forthe same 2012 row (expect 2 rows):<o:p></o:p>
------------------<o:p></o:p>
[TABLE="class: MsoNormalTable, width: 352"]
<tbody>[TR]
[TD="width: 469, bgcolor: transparent, colspan: 3"]Data returned for CountMikesOrders, 2012 (First 1000 rows).<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][$Orders].[Salesman]<o:p></o:p>
[/TD]
[TD][$Orders].[OrderNumber]<o:p></o:p>
[/TD]
[TD][$Orders].[Year]<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD]Mike<o:p></o:p>
[/TD]
[TD]3<o:p></o:p>
[/TD]
[TD]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mike<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD]Bob<o:p></o:p>
[/TD]
[TD]1<o:p></o:p>
[/TD]
[TD]2012<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tom<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]19<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]2012<o:p></o:p>
[/TD]
[/TR]
</tbody>[/TABLE]
----------------------<o:p></o:p>
As you see, ALL rowsfor 2012 were returned, not just the rows for "Mike".<o:p></o:p>
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:p></o:p>
Am I missing some very obvious rule about filter context?:oops: 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:p></o:p>
I appreciate any andall responses.<o:p></o:p>
Thanks - Mike<o:p></o:p>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You must log in or register to reply here.

Similar threads

S
  • Question Question
DAX Expression Help
Replies
1
Views
141
RoryA
RoryA
I
  • Solved
DAX measure that filters a table to a single row and returns text from a specified column (Excel365)
Replies
3
Views
204
Insert_Key
I
A
  • Solved
Count Number of Repeat Customers
Replies
7
Views
644
JGordon11
JGordon11
P
  • Question Question
PowePivot - Create a slicer of a measure or calculate values from one table using filter from another table
Replies
5
Views
668
RoryA
RoryA
J
  • Question Question
SPC In PowerPivot
Replies
0
Views
440
JB2020
J

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top