How to drill through and dynamically select metric

[OldDogNewTricks]

Here is my use case, I would like to have a PowerBI report with a table.  I would like to make it so that when somebody selects a cell in the table, they can drill through to another tab on the report and pass through the specific METRIC selected as well as the other fitler context (in the case below, Geo).  Examples below:

Sample drill-through target ("Average price per unit" measure on all vizzes):

Any ideas on how I can accomplish this?

I know that I could create a "field parameter" on the second page and have people manually select that value on the second page; but I don't know how to tie that to a drill-through filter.

[OldDogNewTricks · Accepted solution]

Unfortunatly there is no current solution within PowerBI to do this as of now.  I created this post to mark it as the "solution", eventhough there really is no solution.  Only workarounds.

[samratpbi]

Hi, you can achieve this using drill through measure.

I have created a simple dataset to demonstrate this. below is the dataset.

Now, I have created 2 simple measures:

Product Avg Sales = AVERAGE(DatePoint[Sales])

Product Sum Sales = SUM(DatePoint[Sales])

Now, create another page and make it as drillthrough page and in drill through field add the desired measure, in this case [Product Avg Sales].

in the main page, add a matrix visual and also add Product Avg Sales measure which will enable drill through.

And once I you click on drill through, below would be drill through page.

As you can see only Apple details are available in drill through page. You may create many visuals in this page.

Hope this helps.

If this helps to resolve your problem then please mark it as Solution provided. Dont forget to give kudos 🙂 

Thanks

[OldDogNewTricks]

Sorry, this does not do what I'm asking.  When I go from page 1 I want ONLY the metric that I drilled through to show on the second page.  For example, I have 4 metrics on page 1 (the example shows "Avg price per unit" as the selected metric); when that is drilled through from page 1 I want ONLY "Avg price per unit" to be the metric in all 4 vizzes on page 2 (the drill through target page).

[Jai-Rathinavel]

@OldDogNewTricks  Drill through is not currently supported with Field Parameters. But you can achieve the expected result / drillthrough by using the sync slicer. Follow the steps below.

1. Create your field parameter by clubbing your measures, in my case it is Total Shipping cost and Average Shipping cost and pull the field parameter in the measure.

2. Also include the field paramter in your matrix/Tabel visual in Page 1

3. Now Copy the Parameter from page 1 and paste it on Page 2 then sync slicers. Replace the existing measures in the visuals with a field parameter.

4. In Page 2 you should add the catgeory (Geo) from which you want to drill from under drill through settings. 

5. Now head over to page 1 and select a metric from slicer and choose a row you want to drill from. Now the drillthrough option should be enabled for the selected metric.

Did I anwer your question ? Mark this post as a solution.

Thanks,

Jai

[OldDogNewTricks]

Sorry, this does not do what I'm asking.  I have 4 metrics on page 1 (the example shows "Avg price per unit" as the selected metric); when that is drilled through from page 1 I want ONLY "Avg price per unit" to be the metric in all 4 vizzes on page 2 (the drill through target page).  I know that I could sync slicers, but I'm trying to get the drilled through metric on page 1 to be the field paramater on page 2.  If I cannot get this functionality, I would rather create a field parameter with all of the metrics on page 2 and have the user select them manually (which is not ideal).

[Anonymous]

Thanks for the reply from samratpbi  and Jai-Rathinavel , please allow me to provide another insight:

Hi, @OldDogNewTricks 
Thanks for reaching out to the Microsoft fabric community forum.

I believe your proposed alternative solution is a good one.

Here is an example I created:

As you have tested, if you use the original data as the drill-through field, the target page will retain a filter. Currently, I have not found an effective way to avoid this.

Therefore, if you insist on using drill-through, I suggest you create a new table with similar measures, using only the original values as the drill-through field. The data on the target page should be populated with the copied table data.

Here is an example I created:

Other feasible page navigation solutions include bookmarks and page navigation.

For more details, please refer to:

Create report bookmarks in Power BI - Power BI | Microsoft Learn
Create page and bookmark navigators - Power BI | Microsoft Learn
 

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.