Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Kristofferaabo
Helper IV
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
DoubleJ
Solution Supplier
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

View solution in original post

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] )

Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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

 

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

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

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] )

Regards
Zubair

Please try my custom visuals

Thank you for the solution

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

 

 

Zubair_Muhammad
Community Champion
Community Champion

hI @Kristofferaabo

 

You can do it from the Query Editor

 

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

 

mINIMUM MULTIPLE cOLUMNS.png


Regards
Zubair

Please try my custom visuals

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

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 

perfect solution!  thank you

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors