Forum Discussion

kasiaw29's avatar
kasiaw29
Icon for Resolver II rankResolver II
6 years ago
Solved

Find max in a group based on a condition

Hi there,   I'm looking to find MAX baseline revision no within my data. This is what I have used so far :     Pretend Project column = x  My goal is to find max baseline revision no wher...
Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi kasiaw29 ,

    Based on your description, you can create this measure, put it in the visual filter and set its value as 1:

    Measure = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Baseline Revision No] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] ),
        'Table'[Cus Search] = 1
    )
VAR _br =
    SELECTEDVALUE ( 'Table'[Baseline Revision No] )
RETURN
    IF ( _br = _max, 1, 0 )

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

  • kasiaw29's avatar
    kasiaw29
    6 years ago

    I have had help on a spanish version of community. 

    I've changed my LC Max to include following: 

     

    LC Max = CALCULATE(
    MAX('Activity History'[Baseline Revision No]),
    FILTER(
    ALLEXCEPT(
    'Activity History',
    'Activity History'[Project]
    ),
    'Activity History'[CUS Search] = 1
    )
    )
     
     
    Then I have created a custom column with this:
    Max Baseline CA = IF('Activity History'[Baseline Revision No] = 'Activity History'[LC Max],1,0)
     
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

kasiaw29 , try like

Measure =
VAR __id = MAX ( 'Activity History'[Project] )
VAR __dt = CALCULATE ( MAX( 'Activity History'[Baseline Revision No] ), ALLSELECTED ( 'Table' ), 'Table'[Project] = __id )
If( 'Activity History'[Project] = __id, 'Activity History'[Baseline Revision No] = __dt ,1,0 )

  • kasiaw29's avatar
    kasiaw29
    Icon for Resolver II rankResolver II
    6 years ago

    amitchandak Not sure if this is it, please have a look at another sample data. 

    Highlighted row should be max baseline rev where cus search = 1 and that's the noly result I want to see.

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      kasiaw29 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi kasiaw29 ,

      Based on your description, you can create this measure, put it in the visual filter and set its value as 1:

      Measure = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Baseline Revision No] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] ),
        'Table'[Cus Search] = 1
    )
VAR _br =
    SELECTEDVALUE ( 'Table'[Baseline Revision No] )
RETURN
    IF ( _br = _max, 1, 0 )

      Attached a sample file in the below, hopes to help you.

       

      Best Regards,
      Yingjie Li

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

      • kasiaw29's avatar
        kasiaw29
        Icon for Resolver II rankResolver II
        6 years ago

        I have had help on a spanish version of community. 

        I've changed my LC Max to include following: 

         

        LC Max = CALCULATE(
        MAX('Activity History'[Baseline Revision No]),
        FILTER(
        ALLEXCEPT(
        'Activity History',
        'Activity History'[Project]
        ),
        'Activity History'[CUS Search] = 1
        )
        )
         
         
        Then I have created a custom column with this:
        Max Baseline CA = IF('Activity History'[Baseline Revision No] = 'Activity History'[LC Max],1,0)