filter only top level of drill down

[seth127]

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:

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:

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

[Sean · Accepted solution]

@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

[seth127]

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]

@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

[seth127]

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.