MIN with Multiple Columns

[Kristofferaabo]

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 

[DoubleJ · Accepted solution]

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

[Anonymous]

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]

Hi @Anonymous ,

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

Cheers,

Antonio

[Anonymous]

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]

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 · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

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

[fep]

Thank you for the solution

[Kristofferaabo]

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

[Zubair_Muhammad]

hI @Kristofferaabo

You can do it from the Query Editor

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

[Kristofferaabo]

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]

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 

[jenniferr]

perfect solution!  thank you