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/Chil...
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.

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

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?

 

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 )

 

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

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?

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

    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. 

     

     

     

     

     

     

     

     

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

      Maybe because 2019 doesn't have a previous year?

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

        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.