Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LBE
Frequent Visitor

Drillthrough on measure with dax filter context not working

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
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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
tmp2.PNG
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
Frequent Visitor

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

Regards, Leon

@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!

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

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.