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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
seth127
Regular Visitor

filter only top level of drill down

Hello,

I'm trying to create a filter that will only effect one level of my plot. Is this possible?

 

For example:

I am creating a bar chart with hours logged to each Project. Then, when you drill down on a project, it shows hours logged to by each Department, to that project. Like so:

hours by dept no filter.PNG

 

However, There are a lot of tiny projects, so I want to filter to only show projects with more than 500 hours logged to them. No problem, but when I add that filter, then when I drill down I only see Departments that have logged over 500 hours (to that project). Like so:

hours by dept with filter.PNG

Obviously, this is not ideal. Does anyone know a way to apply a filter to only the Project Name level, but NOT the Department level? I've tried a few ways to find a workaround and I keep coming up empty.

 

Thanks a lot for any help!

Seth

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@seth127 Use this Measure in the Visual Level Filter instead... Let me know if it works

 

Filter Measure =
CALCULATE ( [Hours Logged] ), ALLEXCEPT ( 'Table', 'Table'[Project Name] ) )

EDIT: Keep your [Hours Logged] for the chart... just add this Measure to the Visual Level Filter and use it to filter

View solution in original post

4 REPLIES 4
Sean
Community Champion
Community Champion

@seth127 Use this Measure in the Visual Level Filter instead... Let me know if it works

 

Filter Measure =
CALCULATE ( [Hours Logged] ), ALLEXCEPT ( 'Table', 'Table'[Project Name] ) )

EDIT: Keep your [Hours Logged] for the chart... just add this Measure to the Visual Level Filter and use it to filter

Thanks Sean. But, **bleep**, I keep running into this problem: I'm hooked in SSAS and as far as I can tell you can't yet add measures or do calculated columns. It's on the ideas page, but not added yet. Bummer.

 

Hopefully Sean's solution works for other folks, but does anyone (including Sean) know how to do this without adding a measure?

 

Thanks!

Sean
Community Champion
Community Champion

@seth127 Sorry dude! I can't think of a way without the Filter Measure which always calculates the top level total regardless of which level on your drill-down you are

 

 

Filter Only Top Level.png

Thanks for the effort. Adding calculated columns to SSAS is fairly high on the ideas list, so hopefully your solution will work for me soon.

 

For anyone else in my boat, I've solved this in a hack-ish, undignified manner by adding a slicer which effects only the bar graph, and manually selecting the just the projects that are above my threshold. Then you just turn off the filter and the drill down works as it should. Works, but you have to manually create the original filter by looking at the data and clicking in the slicer. Ergo, undignified.

 

Thanks again.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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