topic Re: Filtering Matrix Hierarchy by Parent's Measure Value in DAX Commands and Tips

Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Wed, 02 Feb 2022 22:17:59 GMT

Hi,

I've been stuck on this problem for a few weeks. You can find a sample of the data and the model here.

I have a Orders, Companies, and Dates table. The Companies table has a Parent/Child relationship that forms a ragged hierachy.

I have visualized it in a matrix to show YTD Revenue, PYT Revenue, YTD vs PYTD and YTD vs PYTD %.

I would like to be able to filter the visual for Parents YTD vs PYTD % that fall in a 10% range. For exmaple, I'd like a slicer that showed 100% to 90%, 89% to 70%...etc. and to be able to select one of these ranges to filter Parents whos YTD vs PYTD % fall in this range along with their childern, even if the childern have a different value.

I have tried using dynamic segemnations patterens, also tried createing a calculated table to compute the YTD vs PYTD % and use as a filter. I think my DAX skills aren't advanced enough, as I struggle conceputally how to tackle this problem. Any guidence is greatly apprciated.

Thanks!

Re: Filtering Matrix Hierarchy by Parent's Measure Value

AlexisOlson — Wed, 02 Feb 2022 22:31:46 GMT

I can't access the file you linked to.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Wed, 02 Feb 2022 23:17:51 GMT

AlexisOlson sorry, put the wrong type of permision in google drive. It works now, just checked.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

AlexisOlson — Thu, 03 Feb 2022 16:55:49 GMT

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.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Thu, 03 Feb 2022 19:19:24 GMT

Thank you AlexisOlson, I am going to try and implement into my model. Question is there a way to filter a whole page for just the customers that fall in the ranage?

Re: Filtering Matrix Hierarchy by Parent's Measure Value

AlexisOlson — Thu, 03 Feb 2022 21:24:26 GMT

You could define a calculated column on the Companies table that buckets each [Level 1] into the corresponding range and then use that column for your slicer. This isn't dynamic though. Each [Level 1] item can only have a single value regardless of what report filters you have.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Thu, 03 Feb 2022 21:48:24 GMT

Ok I'll play with that and see if the end result gets me close.

I added your measure filter to my model and it works except if I have a year selected in my date slicer. Do I need to add some type of date filter in the Level1Growth variable?

Re: Filtering Matrix Hierarchy by Parent's Measure Value

AlexisOlson — Thu, 03 Feb 2022 22:30:13 GMT

I'm not sure what you're saying doesn't work.

This is an example of what I see with a year selected:

Is this not the expected output?

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Thu, 03 Feb 2022 23:17:43 GMT

Strange, if you look at the pictures below both range filters are the same but populate the matrix differently. The only modification I made was to extended the range down to -1.

I also have one additional page level filter to filter out inactive customers but removing this didn't change anything.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

AlexisOlson — Fri, 04 Feb 2022 15:10:59 GMT

Maybe because 2019 doesn't have a previous year?

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Fri, 04 Feb 2022 23:50:28 GMT

Maybe 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.

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Thu, 10 Feb 2022 22:36:21 GMT

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?

Re: Filtering Matrix Hierarchy by Parent's Measure Value

nbs33 — Tue, 15 Feb 2022 14:38:16 GMT

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.