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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Zelbinian
Frequent Visitor

How do I get a slicer to be driven by drill through, but not constrained by it?

Just a heads up, below I'm using fake data to describe a real problem that I'm having. 

 

The data I'm dealing with is not customer support data, but that is a reasonable facsimile. Each entry (or observation, if you like) has columns that describe a hierarchical relationship - one that is identical to the region -> store relationship that might be in data relating to customer support - along with many other columns.

 

Just to visualize this for a second, let's say my data looks something like this:

 

ID Status Region Store ...

1NewPNWSeattle...
2InvestigatingPNWSpokane...
3ClosedNEBoston...
...............

 

I have two pages in my report. One focuses on the data at the "Region" level, another focuses on the data at the "Store" level. Y'know, pretty typical Power BI use case here.

I have a stacked bar chart visualization on the "Region" page, with the axis set to the Region column, the value the count of the Id column, and the Legend set to the Status column.  On the "Store" page, there is an identical bar chart save for the axis is set to the Store column. Additionally, the "Store" page has "Region" as a drill through variable. 

I've got linked slicers on both reports that allows users filter the "Status" column. That works well. After folks drill through from a Region to a Store, they can select different status filters if they then become interested in different data.

So far so good, but now I'm having trouble setting up a similar slicer on the "Store" page to let the user switch between "Region" without having to go back to the "Region" page and drill through again (or open up the side bar and mess with the drill through filter criteria). This slicer is not on the "Region" page (because that wouldn't make sense) and when I add this slicer to the "Store" page, it behaves very strangely (to my mind, anyway). I have it set to single select via drop down. When I drill through to the "Store" page from the "Region" page, the only option in the drop down is the one I have drilled through to. How do I get this to show all the potential states regardless? 

Also, lets say I happen to be on the "Store" page with no drillthrough filters selected and I filter using the above Region slicer, selecting "NE." If I now go back to the "Region" page and try to drill through on a different value, say "PNW" ... the feature page shows me nothing. If I investigate the Region slicer, the previously selected slicer filter seems to be overriding the drill through filter (or, I guess, they are "AND"-ing and producing a null set?) even though in the other scenario the drill through filter overrided the slider. How do I get the slicer to switch filters to whatever the drill through option was?

 

Thanks. And again, the data I used above was fake data but is structurally similar the problem I am trying to solve.

3 REPLIES 3
parry2k
Super User
Super User

@Zelbinian this is great candidate for "new filter experience", if you don't know what it is, please read it here. It is much intuitive and become part of the report.

 

Basically it is a preview whcihyou have to enable in options->preview and what it will allow, it will show your drill thru filter on the new filter pane where use can select different region.

 

I hope this meets your requirement, if you still want to user slicers for regions on store page (drill thru page) let me know and will figure out some other way.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k That new filter experience does seem promising and I'm happy to give it a go in an alternate version of the file, but I'm not so sure about using an unstable preview build to drive a report that people are already using and relying on. If there's a way to fix this with the current slicers, at least in the mean time, that'd be ideal.

@Zelbinian Understood your concern but I would still recommend  to use this new filter experience, although I will try to find a work around which I would not suggest to use. 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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