Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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
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
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,
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])))
Are you trying to create a calculated column in your table or a measure?
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.
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?
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
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
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])
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 🙂
-Devendra
Can you share your pbix file?
Thanks
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
User | Count |
---|---|
136 | |
73 | |
72 | |
55 | |
55 |
User | Count |
---|---|
199 | |
95 | |
63 | |
62 | |
51 |