Forum Discussion

devenchj's avatar
devenchj
Icon for Helper I rankHelper I
9 years ago
Solved

How to Find Column's Minimum value based on other columns group

Hello Experts,

 

trying to add new measure column which shows column's minimum value group by other column.

tried with DAX function Summarize, MINX  , but no luck.

 

below is data sample, where 4th column is desired value.

calculation required to find minimum of Transit Days per Plant (Region doesn't matter here)

so for Plant A  its 23 and for Plant B its 16

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance !!!

-Devendra

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    The way to achieve this is using EARLIER function:

     

    Column = CALCULATE(MIN(Table1[TransitDays]),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])))

     

    Hope this helps

     

    Michael Shparber

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi devenchj,

    For the sample data, add an index column in Power Query Edit by selecting Index Column(from0,1) under Add column on home page.



    Create a measur using the formula. 

    Measure = MINX(FILTER(SELECTCOLUMNS(ALLSELECTED(Table2),"index",Table2[Index],"Min",MINX(FILTER(Table2,Table2[Plant]=EARLIER(Table2[Plant])),Table2[Value])),[index]=MAX(Table2[Index])),[Min])


    Please see the screenshot, it will calculate the min value based on [Plant] field in diferrent months.


    You can dowbload mine .pbix file, and test using Table2.

    Best Regards,
    Angelia

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    The way to achieve this is using EARLIER function:

     

    Column = CALCULATE(MIN(Table1[TransitDays]),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])))

     

    Hope this helps

     

    Michael Shparber

    • devenchj's avatar
      devenchj
      Icon for Helper I rankHelper I
      9 years ago

      Thanks for response, I tried as suggested but getting error with Earlier function as below :

       

      "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

       

      here is actual formula I used, 

       

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    How about this...

    Min Transit Days by Plant =
CALCULATE ( MIN ( Table[Transit Days] ), ALLEXCEPT ( Table, Table[Plant] ) )
    • devenchj's avatar
      devenchj
      Icon for Helper I rankHelper I
      9 years ago

      Hi Sean, thanks for your help.

       

      this formula is giving wrong result in table visualization

       

       

       

      • tangutoori's avatar
        tangutoori
        Icon for Helper III rankHelper III
        3 years ago
        minvalue =

        Var MIn_val_pla = CALCULATE(min('Table'[Trnsis days]),ALLEXCEPT('Table','Table'[Plant]))

        Return MIn_val_pla