Forum Discussion

nbs33's avatar
nbs33
Helper 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
Helper II
4 years ago

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?

 

 

 

 

nbs33's avatar
nbs33
Helper II
4 years ago

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.