Forum Discussion

honkiedonkie's avatar
honkiedonkie
New Member
2 years ago
Solved

Measure to select rows based on dynamic conditions

Hi, I'm relatively new to PowerBI so I have been searching for so many ways to do this but none of them seem to fit.  I would like to find a way using a measure to select rows based on a condition, ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi honkiedonkie ,

Please have a try.

Create 2 measures.

Measure =
VAR _1 =
    SELECTEDVALUE ( 'Table (2)'[value] )
VAR _2 =
    IF ( MAX ( 'Table'[Value] ) <= _1, "Yes", BLANK () )
RETURN
    _2

Measure2 =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
    ),
    [Measure]
)

 

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

 

 

 

  • honkiedonkie's avatar
    honkiedonkie
    New Member
    2 years ago

    This also works, thank you!

     

    However I was wondering - if I want to insert the corresponding value into Measure 2 rather than 'Yes', how could I go about it? Eg. for the C rows, it will display 80 and 32 respectively instead of 'Yes'. MAXX will give me the max of all the values corresponding to the same category and I know that that is expected behaviour. Could you advise me on how to go about doing this? Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi honkiedonkie , 

      Please try to change the measure2.

      Measure2 =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
        ),
        [Measure]
    )
RETURN
    IF ( _1 <> BLANK (), MAX ( 'Table'[Value] ), BLANK () )

       

      How to Get Your Question Answered Quickly 

       

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

       

      Best Regards
      Community Support Team _ Rongtie

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

       

       

       

       

      • honkiedonkie's avatar
        honkiedonkie
        New Member
        2 years ago

        That's genius, thank you! 

        One more thing, if rather than 'Table'[Value] I had a measure of the value - is there a way I can still do the above? 

        In my actual dataset there are some values with repeated Category-Week combination and I have a measure that returns the average of those values for the rows with same combination. 

         

        Averaging Measure = CALCULATE(AVERAGE('Table'[Value]), ALLEXCEPT('Table', 'Table'[Category], 'Table[Week]))

         

        The current way I can go about doing this is to create a calculated column rather than measure, but for a large dataset my computer does not have sufficient memory. I was wondering if there is a way I could use only measures to do this. Thank you again!