Forum Discussion
How to setup a drilldown visual based on text column
Hello everyone,
I am struggling in setting up a drilldown on a visual I am creting in PowerBI. It might be something simple but I am definetely missing the solution after several attempts 🙂
I only have a text column with 2 values that I want to use for drilling down but after several experiments I wasn unable in creating a correct hierarchy setup that can be used in the visual. I have tried using bookmars to capture to have 2 visuals and hide/unhide but that's not going to correct on the long run.
This is my main view which list a measure trend over time( the x axis values are text)
And I would like to be able to drilldown to a secondary view which is showing 2 lines for the measure on the text column. Ideally I will be able to drilldown in the same visual and not go to another page for viewing drilldown results.
Appreciate any idea or suggestions for this issue.
Thank you,
Marius
Hi mdm2025
You can only drilldown with columns not with measures. Instead, add your measures to a field parameter then in the field parameter create a group for those measures using a calculated column.
Please see the attached pbix.
Hey mdm2025 ,
Because of complex model relationship you unable to use hierarchy, instead you can use the field parameter to achieve your goal. I attached the file where your problem is solved. Please check this out.
Field Parameter:Sales Trend View:
Type Trend View:
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
7 Replies
I have setup a mockup pbi to better reproduce the scenario I am trying to setup.
https://limewire.com/d/ePncs#qwLXKQVLfR
Thanks again for the support.
Marius
Hey mdm2025 ,
Because of complex model relationship you unable to use hierarchy, instead you can use the field parameter to achieve your goal. I attached the file where your problem is solved. Please check this out.
Field Parameter:Sales Trend View:
Type Trend View:
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Hey mdm2025 ,
If you did not try the hierarchy , please set up a proper hierarchy between your time period (Q2 2024, Q3 2024 …) and your text column with 2 values. That should be helpful.
-
In the Fields pane, right-click your time column (the one you use on the X-axis) --> New hierarchy.
-
Add your text column as the second level of that hierarchy.
-
Use this hierarchy on the X-axis of your line chart instead of just the time column.
-
Now the built-in drill down buttons will let you:
-
Stay at the top level which is a single line trend over time (your first screenshot).
-
Drill down which split the measure into 2 lines based on your text column for each time period (your second screenshot).
-
If you prefer not to use drilldown, an alternative is to create a field parameter and let users toggle between “Overall Trend” and “Split by Category,” but the hierarchy approach is the standard solution for your scenario.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam-
Hi mdm2025
You can only drilldown with columns not with measures. Instead, add your measures to a field parameter then in the field parameter create a group for those measures using a calculated column.
Please see the attached pbix.
Hi mdm2025 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Nasif_Azam danextian for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
Hi everyone,
danextianand Nasif_Azamthank you very much for your help.
In the end the suitable solution was creating a field parameter and use it as slicer.Best regards,
Marius
