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.
Super UserMaybe because 2019 doesn't have a previous year?
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.
- 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.
- nbs33
Hi 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?