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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
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
7 years ago

Hi, I have got a same scenario and the measure v-huizhn-msft  provided works correctly but difficult to understand the concept behind, how do i filter out another column within the same table to get the minimum along with other filtered columns? i.e. for example first three columns are correctly filtered out with your v-huizhn-msft  solution but I'm not sure how to filter out the last column using the same measure.

 

Please advise. Thanks