cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
devenchj Frequent Visitor
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

sampledata.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance !!!

-Devendra

2 ACCEPTED SOLUTIONS

Accepted Solutions
mshparber Established Member
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

v-huizhn-msft Super Contributor
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.

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

13 REPLIES 13
Sean Super Contributor
Super Contributor

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

How about this...

Min Transit Days by Plant =
CALCULATE ( MIN ( Table[Transit Days] ), ALLEXCEPT ( Table, Table[Plant] ) )
mshparber Established Member
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

devenchj Frequent Visitor
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, 

 

Image 4.png

mshparber Established Member
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:

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

 

Highlighted
devenchj Frequent Visitor
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

 

Image 5.png

 

 

mshparber Established Member
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?

devenchj Frequent Visitor
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.

 

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?

 

 

mshparber Established Member
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
v-huizhn-msft Super Contributor
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.

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 373 members 3,922 guests
Please welcome our newest community members: