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 ...
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

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, 

     

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

        Hi mshparber,

        I have created calculated column with create "New Measure" option whereas you created "New Column" (Fx)

        anyway i changed it to calc column and error is gone while using EARLIER  function, but result is not correct.

         

         

        I belive extra columns in tables causing wrong result. 

        actual table structure is 

        DISRIBUTOR_ID

        REGION_ID

        PLANT_ID

        MLY_CALENDAR_ID

        TRANSIT_DYS

         

        one more point I noticed, there are Plant and Month slicer as well on report, therefore MIN value should be dynamic.

        How can i write FILTER expression having more than 1 column?

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Are you trying to create a calculated column in your table or a measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am having a similar problem "EARLIER/EARLIEST refers to an earlier row..."

       

      This is the formula I was using.

       

      MIN Apartment Code = CALCULATE(MIN(DynamoAreas[Area]),FILTER(DynamoAreas,DynamoAreas[Apartment Code]=EARLIER(DynamoAreas[Apartment Code])))