Forum Discussion

Sdhn420's avatar
Sdhn420
Helper IV
5 years ago
Solved

Filter by Benchmark

Hi all,

I need to filter the margin of a table using the bechmarck selected from the slicer.

Example - table should display Margin% >=Slicer Selected Value1 && margin% <=Slicer selected value

 

Sample file attached.

https://drive.google.com/file/d/13YqsUiXwqNLFOd-J6FJW3gMHSrtkHVDQ/view?usp=sharing

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Sdhn420 ,

     

    Please use the following formula to create a measure:

    Measure =
IF (
    MAX ( 'Data'[Margin%] )
        >= MIN ( 'Benchmark'[Criteria] ) * 100
        && MAX ( 'Data'[Margin%] )
            <= MAX ( 'Benchmark'[Criteria] ) * 100,
    1,
    0
)

    And apply it to filter pane,set as "=1" like this:

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Sdhn420 , Add a what if parameter with a range slcier and try a new measure

     

    new measure =
    var _min = minx(allselected(whatif), whatif[value])
    var _max = maxx(allselected(whatif), whatif[value])
    return
    calculate([Margin %], filter(Values(Table[material]), [Margin %] >=_min && [Margin %] <=_max))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Sdhn420 

     

    Add a measure

     

    test = 
VAR minC = MINX(VALUES(Benchmark[Criteria]),[Criteria])*100
VAR maxC = MAXX(VALUES(Benchmark[Criteria]),[Criteria])*100
RETURN
MAXX(FILTER(Data,Data[Margin%] >=minC&&Data[Margin%]<=maxC),[Margin%])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Sdhn420 ,

     

    Please use the following formula to create a measure:

    Measure =
IF (
    MAX ( 'Data'[Margin%] )
        >= MIN ( 'Benchmark'[Criteria] ) * 100
        && MAX ( 'Data'[Margin%] )
            <= MAX ( 'Benchmark'[Criteria] ) * 100,
    1,
    0
)

    And apply it to filter pane,set as "=1" like this:

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.