Forum Discussion
Helper IIFiltering Matrix Hierarchy by Parent's Measure Value
Try defining a new parameter that has values from 0% - 100% and create a measure to filter based on those parameter values in a slicer.
I defined a new table named Range and defined this as a filter measure.
InSelectedRange = VAR Level1Growth = CALCULATE ( [YTD vs PYTD Revenue %], ALLSELECTED (), VALUES ( Companies[Level 1] ) ) VAR RangeBuckets = FILTER ( Range, Range[Range] <= Level1Growth && ( Range[Range] = 1 || Range[Range] + 0.1 > Level1Growth ) ) RETURN IF ( ISEMPTY ( RangeBuckets ), 0, 1 )See attached file.
Helper IIMaybe you're right, I expanded my date table to include the full range of dates in the Orders table and I still have a blank in the slicer. It's a head scratcher....the only other thing that is different is I user Company Name instead Company Id for the levels of the hierarchy.
When I change the range slicer to less than zero the behavior is even stranger. The matrix shows positive percentages.
Helper IIHi AlexisOlson, I've been trying to break you solution apart to see where it fails in my model. I think I found where the issue is coming from.
When I test the effect of selecting the year on the RangeBuckets varible I get the results below. When I run this test in your file, the RangeBuckets remain -100% no matter if a year is selected or not.
I tried using a REMOVEFILTER for the year but no luck. Any idea how to solve this?
- nbs33
I figured out the issue, it is two fold. First, I had a relationship in my model that created a circular dependancy. Second, the filter mease is calculating in 10% increments so it includes values greater than the max or min value in the slicer. I change the slicer to a drop down so the user can select a bucket of value in 10% increments which makes more sense from a usability perspective.