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

## 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
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

Super User

## Re: MIN with Multiple Columns

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
10 REPLIES 10
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

## Re: MIN with Multiple Columns

You can do it from the Query Editor

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

Try my new Power BI game Cross the River
Member

## 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'..

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

Super User

## Re: MIN with Multiple Columns

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
Member

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

Highlighted
Frequent Visitor

## Re: MIN with Multiple Columns

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``````

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

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

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

#### 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

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)