Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.