cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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
Solution Supplier

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

Community Champion

You can use this calculated column

Column =
VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] }
RETURN
MINX ( Temp, [Value] )

Regards
Zubair

12 REPLIES 12
Anonymous
Not applicable

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

Anonymous
Not applicable

Hi @Anonymous ,

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

Cheers,

Antonio

Anonymous
Not applicable

Hey @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 MinColumn

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

Cheers,

Sven

Anonymous
Not applicable

Hi @Anonymous ,

Thanks that worked!
So I'm using the piece of code in a different way. I have different columns in my table and I need to find which column(s) have the least and/or most blank rows.

So I have measures for each column to give me the number of blanks given by:

Blanks_column1 = CALCULATE(COUNTROWS(table1), column1=BLANK())
Blanks_column2 = CALCULATE(COUNTROWS(table1), column2=BLANK())
Blanks_column3 = CALCULATE(COUNTROWS(table1), column3=BLANK())

Then I'm adapating your DAX code to give me the min or max from all of those measures, in other words, to give me the column that has the least or most blanks:

Least_Completed_Column =
VAR Temp =
{
Blanks_column1 );
Blanks_column2 );
Blanks_column3 )
}
VAR MaxValue = MAXX ( Temp; [Value] )

VAR MaxColumn =
SWITCH(
MaxValue;
Blanks_column1 ); "Column1";
Blanks_column2 ); "Column2";
Blanks_column3 ); "Column3"
)

RETURN MaxColumn

My next step would be to obtain the top 5 values for the 5 columns with highest number of blanks. Do you have a solution for that with, perhaps, using your original DAX measure?

Thanks,

Antonio

Solution Supplier

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

Community Champion

You can use this calculated column

Column =
VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] }
RETURN
MINX ( Temp, [Value] )

Regards
Zubair

Helper I

Thank you for the solution

Helper IV

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

Community Champion

You can do it from the Query Editor

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

Regards
Zubair

Helper IV

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

Anonymous
Not applicable

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

Regular Visitor

perfect solution!  thank you

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors