Forum Discussion

Kristofferaabo's avatar
Kristofferaabo
Helper IV
8 years ago
Solved

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 va...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • DoubleJ's avatar
    DoubleJ
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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] )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

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

 

Cheers,

Antonio

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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