cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Established Member

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

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

Super Contributor

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

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

13 REPLIES 13
Super User

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

```Min Transit Days by Plant =
CALCULATE ( MIN ( Table[Transit Days] ), ALLEXCEPT ( Table, Table[Plant] ) )```
Established Member

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

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

Frequent Visitor

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

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,

Established Member

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

Can you attach your .pbix file?

This works in mine - see attached:

Frequent Visitor

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

Hi Sean, thanks for your help.

this formula is giving wrong result in table visualization

Established Member

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

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

Frequent Visitor

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

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?

Established Member

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

1. Using EARLIER as a COLUMN should return the right result - minimum for every PLANT_ID - regardless of other colunms. You probably have other rows in the table not shown in printscreen where PLANT_ID = 11 and TransitDays =0. Please check.
2. As for your requirement of dynamic calculation based on Slicers values : here you need to solve it with a measure, not column. Only measures are sensitive to context. So probably EARLIER won't work.
I'll try to help later since now I am only with iPhone
Super Contributor

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

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