March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
You can use this calculated column
Column = VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] } RETURN MINX ( Temp, [Value] )
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
Hi @Anonymous ,
Is there a way to return the name of the column instead of the value?
Cheers,
Antonio
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
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
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
You can use this calculated column
Column = VAR temp = { Table1[c1], Table1[c2], Table1[c3], Table1[c4], Table1[c5] } RETURN MINX ( Temp, [Value] )
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..?
You can do it from the Query Editor
Select the Columns>>>Go to "Add Column" Tab>>Statistics>>> MInimum
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'..
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
88 | |
71 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |