Forum Discussion

nbs33's avatar
nbs33
Icon for Helper II rankHelper II
4 years ago
Solved

Filtering Matrix Hierarchy by Parent's Measure Value

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!

 

 

  

 

 

Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

12 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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.

    • nbs33's avatar
      nbs33
      Icon for Helper II rankHelper II
      4 years ago

      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?

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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.

    • nbs33's avatar
      nbs33
      Icon for Helper II rankHelper II
      4 years ago

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