Drillthrough on measure with dax filter context not working

[LBE]

Hi,

In my report I use some measures with filter context in the dax code, for example:

Count Orchid Cut = CALCULATE(COUNT(VarietyTable[VarietyId]);VarietyTable[Crop] = "Orchid Cut")

On my main page I have a gauge based on this measure. Now I want to do a drillthrough on this gauge, so I have made a second page with details from my VarietyTable and the Count Orchid Cut measure as the drillthrough field.

But when I hit drillthrough on the gauge the result is the complete set, not filtered for the specified crop.

Is this a flaw in Power BI or am I doing something wrong?

Thanks in advance!

Leon

[OwenAuger · Accepted solution]

Hi Leon,

When using drillthrough, filters created within a measure with CALCULATE are not passed to the drillthrough page.

Only filters from the report itself can be passed, i.e. fields in the visual itself, slicers, other filters.

I agree that this makes it confusing when drilling through from a measure like Count Orchid Cut.

This post discusses the same issue and proposes a solution by using a measure on the drillthrough page that replicates the filter on the original visual.

In your case, it sounds like you have a table or similar visual on the drillthrough page. You could add a visual level filter to that visual with a slightly modified measure set to is not blank:

Count Orchid Cut =
CALCULATE (
    COUNT ( VarietyTable[VarietyId] );
    KEEPFILTERS ( VarietyTable[Crop] = "Orchid Cut" )
)

By the way, do you have multiple measures of a similar nature that all need to drillthrough?

Regards,

Owen

[Anonymous]

I have a similar setup too and wish Drillthrough passed Filters from Measures too. Isnt that how Cross Filter works? I am was in shock to know that my entire report will lose the "Drill through" functionality due to this limitation! 😞

Here is my table with Categories coming from a Static Table (Commit, Upside, etc) , I have regions on Column
The matrix values varies basic on each category displayed here

Here is a shorter version of my Measure. As you can see each category has a different filter. When I try to Drillthrough, only matrix filters are passed , not the Filters applied in the Measurs. Same happens when i have a simple , straightforward measure too! Please help or suggestion a possible alternative

CategoryMetrics = 
			
var vCommit= CALCULATE([ForecastSales],'FC'[forecast category]="Commit")
var vUpside= CALCULATE([ForecastSales],'FC'[forecast category]="Pipeline")
var vEarly =CALCULATE([ForecastSales],left('FC'[sales stage],2) in {"X0","X2","X3"})
var vLate =CALCULATE([ForecastSales],left('FC'[sales stage],2) in {"X4","X5"})

return
 SWITCH(SELECTEDVALUE('Category'[ID]),
 1, format(vCommit,"$ #.00 M"),
 2,format(vUpside,"$ #.00 M"),
 3, format(vEarly,"$ #.00 M"),
 4, FORMAT(vLate,"$ #.00 M"),
0)
/* Calculate Percentages
 5,FORMAT(DIVIDE(vCommitPerc,vCommit),"#.##%"),    5,FORMAT(DIVIDE(vCommitPerc,vCommit),"#.##%")
 6, FORMAT(DIVIDE(vUpsidePerc,vUpside) ,"#.##%"),
 7,FORMAT(DIVIDE(vLatePerc,vLate),"#.##%"),
*/
 

[LBE]

Hi Owen, thanks for your answer. Indeed I have multiple measures that all need to drillthrough.

Regards, Leon

[ccdatavizzer]

@LBE Did you get this to work for multiple measures? So far, the only way I've been able to get it to work is to do a different drill-through page for each variation, but that seems excessive to me. I feel like there has to be a more elegant way to do this!

[Delvirio]

@ccdatavizzer hi I did the same method with you, I created many drill through pages for each measure. did you figured another way?

[ccdatavizzer]

I sure didn't! My measures were simple counts based on a status value.

I made the decision to instead create 2 drop down filters on the top of a single detail page to let the user filter that way. Not the greatest solution, but a lot less to maintain.