cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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

-Devendra

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

Employee

Hi @devenchj,

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 16
Anonymous
Not applicable

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

Helper I

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,

New Member

Try to create a new column instead of a measure.

Post Partisan

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])))

Anonymous
Not applicable

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

Anonymous
Not applicable

Can you attach your .pbix file?

This works in mine - see attached:

Helper I

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?

Employee

Hi @devenchj,

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
Not applicable

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.

Helper I

I tried as suggested, but its giving similar value as row and not minimum. used all other expression suggested till now and that gives Minimum Days per Plant across (not considering CALENDAR_ID,Distributor_ID).

Change month slicer value to 491 and see in attached pbix file. https://1drv.ms/f/s!AhX63mpgfjowagcrNOvuf5Srz5A

Column1 = CALCULATE ( MIN ( TRANSIT_TIME_MLY[TRANSIT_DYS] ), ALLEXCEPT ( TRANSIT_TIME_MLY, TRANSIT_TIME_MLY[PLANT_ID]),TRANSIT_TIME_MLY[MLY_CALENDAR_ID] )

Column2 = CALCULATE(MIN(TRANSIT_TIME_MLY[TRANSIT_DYS]),FILTER(TRANSIT_TIME_MLY,TRANSIT_TIME_MLY[PLANT_ID]=EARLIER(TRANSIT_TIME_MLY[PLANT_ID])))

Measure3 = MINX(FILTER(SELECTCOLUMNS(ALLSELECTED(TRANSIT_TIME_MLY),"index",TRANSIT_TIME_MLY[Index],"Min",MINX(FILTER(TRANSIT_TIME_MLY,TRANSIT_TIME_MLY[PLANT_ID]=EARLIER(TRANSIT_TIME_MLY[PLANT_ID])),TRANSIT_TIME_MLY[TRANSIT_DYS])),[index]=MAX(TRANSIT_TIME_MLY[Index])),[Min])

Thanks,

Devendra

Helper I

Its resolved now, I created new combined column instead, and used with all suggested  expressions and all provided solutions worked perfectly.

PlantGroup = TRANSIT_TIME_MLY[DISTRIBUTOR_ID] & "~" &TRANSIT_TIME_MLY[PLANT_ID] & "~" &TRANSIT_TIME_MLY[MLY_CALENDAR_ID]

thanks you all for your help 🙂

-Devendra

Employee

Can you share your pbix file?

Thanks

Anonymous
Not applicable
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
Community Champion

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

Hi Sean, thanks for your help.

this formula is giving wrong result in table visualization

Helper III
minvalue =

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

Return MIn_val_pla

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors