Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
devenchj
Helper I
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

sampledata.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance !!!

-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

View solution in original post

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.

6.PNG

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.

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

Best Regards,
Angelia

View solution in original post

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

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, 

 

Image 4.png

Try to create a new column instead of a measure.

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:

 https://1drv.ms/u/s!AoP_9ampPIT7jQBNaZXQS-m6d-ZQ

 

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.

 

Image 7.png

 

I belive extra columns in tables causing wrong result. 

actual table structure is 

DISRIBUTOR_ID

REGION_ID

PLANT_ID

MLY_CALENDAR_ID

TRANSIT_DYS

Image 6.png

 

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?

 

 

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.

6.PNG

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.

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

 

Capture3.PNG

Please advise. Thanks

Hi @v-huizhn-msft ,

 

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

 

Image 8.png

 

Thanks,

Devendra

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 🙂 


Image 9.pngImage 10.png

-Devendra

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
Sean
Community Champion
Community Champion

How about this...

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

Hi Sean, thanks for your help.

 

this formula is giving wrong result in table visualization

 

Image 5.png

 

 

minvalue =

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

Return MIn_val_pla

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.