Forum Discussion
MIN with Multiple Columns
Hi,
I have problems getting the min value of dates returned across more than two columns.
For instance columns C1, C2, C3, C4 and C5.
How can I get a column or a measure returning the min value for each row across these colunms?
Thanks
Kristoffer
Hi Kristoffer
You could create a new column with DAX and use the MIN() function. As MIN() only takes 2 arguments you have to nest them:
MinValue = MIN(MIN(MIN(MIN(Demo[C1],Demo[C2]),Demo[C3]),Demo[C4]),Demo[C5])
There might be an easier way which I am not aware of.
Hope this helps!
JJ
You can use this calculated column
Column = VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] } RETURN MINX ( Temp, [Value] )
12 Replies
I would add this as a native column using Query Editor.
Click on "Add Custom Column", and paste in this formula. Note that you may need to adjust it depending on the names of your 5 columns.
List.Min( { [C1], [C2], [C3], [C4], [C5] } )This formua first generates a list of values, using the 5 columns. It uses Curly Braces {} to say it's a list.
Then List.Min finds the smallest value of that list.
Hope this helps,
~ ChrisH
perfect solution! thank you
Hi Kristoffer
You could create a new column with DAX and use the MIN() function. As MIN() only takes 2 arguments you have to nest them:
MinValue = MIN(MIN(MIN(MIN(Demo[C1],Demo[C2]),Demo[C3]),Demo[C4]),Demo[C5])
There might be an easier way which I am not aware of.
Hope this helps!
JJ
You can use this calculated column
Column = VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] } RETURN MINX ( Temp, [Value] )Hi Zubair_Muhammad I'm trying to use your formula on another dataset. I want to find the MIN value over 15 columns... but it seems that there is a restriction on the number of columns to put in the formula..?
You can do it from the Query Editor
Select the Columns>>>Go to "Add Column" Tab>>Statistics>>> MInimum
Hi Anonymous and Zubair_Muhammad
Both great solutions... this is however a table already generated from another table (my a summarize function) so I'm not able to see the table in 'edit queries'..
If you would like to save the memory overhead of a calculated column, you could do it over a very simple measure as well:
min_date = VAR Temp = { MIN ( table1[C1] ); MIN ( table1[C2] ); MIN ( table1[C3] ) } VAR MinYear = MINX ( Temp; [Value] ) RETURN MinYearHey Anonymous ,
You could of course do something like this:
min_date_column = VAR Temp = { MIN ( Table1[Column1] ); MIN ( Table1[Column2] ); MIN ( Table1[Column3] ) } VAR MinDate = MINX ( Temp; [Value] ) VAR MinColumn = SWITCH( MinDate; MIN ( Table1[Column1] ); "Column1"; MIN ( Table1[Column2] ); "Column2"; MIN ( Table1[Column3] ); "Column3" ) RETURN MinColumnWould be interesting to know why you would do something like that? What do you use the column name for?
Cheers,
Sven
