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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.