cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions
DoubleJ Member
Member

Re: MIN with Multiple Columns

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

View solution in original post

Super User
Super User

Re: MIN with Multiple Columns

Hi @Kristofferaabo

 

You can use this calculated column

 

Column =
VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] }
RETURN
    MINX ( Temp, [Value] )
Try my new Power BI game Cross the River

View solution in original post

10 REPLIES 10
ChrisHaas Established Member
Established Member

Re: MIN with Multiple Columns

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 

Super User
Super User

Re: MIN with Multiple Columns

hI @Kristofferaabo

 

You can do it from the Query Editor

 

Select the Columns>>>Go to "Add Column" Tab>>Statistics>>> MInimum

 

mINIMUM MULTIPLE cOLUMNS.png

Try my new Power BI game Cross the River

Re: MIN with Multiple Columns

Hi @ChrisHaas 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'..

DoubleJ Member
Member

Re: MIN with Multiple Columns

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

View solution in original post

Super User
Super User

Re: MIN with Multiple Columns

Hi @Kristofferaabo

 

You can use this calculated column

 

Column =
VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] }
RETURN
    MINX ( Temp, [Value] )
Try my new Power BI game Cross the River

View solution in original post

Re: MIN with Multiple Columns

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..?

min_column.jpg

 

 

Elektryon Frequent Visitor
Frequent Visitor

Re: MIN with Multiple Columns

Hi @Kristofferaabo 

 

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 MinYear

 

antoniogouveia Frequent Visitor
Frequent Visitor

Re: MIN with Multiple Columns

Hi @Elektryon ,

 

Is there a way to return the name of the column instead of the value?

 

Cheers,

Antonio

Highlighted
Elektryon Frequent Visitor
Frequent Visitor

Re: MIN with Multiple Columns

Hey @antoniogouveia ,

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 MinColumn

 

Would be interesting to know why you would do something like that? What do you use the column name for?

 

Cheers,

Sven

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)