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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
UHS
Helper I
Helper I

How to get the second smallest number from the list of columns

Hi All,

I need help with below scenario.

I have 5 columns as A,B,C,D,E and F

If F =1 the i need the smallest value from A,B,C,D,E

If F>1 then i need second smallest value from A,B,C,D,E.

 

Eg : If F=1 and A=1,B=2,C=2,D=3,E=3 then result shoud be 1 
IF If F>1 and A=1,B=2,C=3,D=3,E=3 then result shoud be 2

IF If F>1 and A=2,B=2,C=3,D=3,E=3 then result shoud be 2 (Irrespective of the value even if it is repeating)

Can you please help with the dax function that i can use. In excel we can use the small function to get the second lowest value. How can we achieve this using power bi dax

Thanks in advance

2 ACCEPTED SOLUTIONS
tackytechtom
Super User
Super User

Hi @UHS ,

 

How about this:

tackytechtom_0-1702494893827.png

 

Here the DAX:

Measure = 
VAR _helpTable1 =
UNION ( 
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[A],
           "Col", "A"),
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[B],
           "Col", "B"),
        SELECTCOLUMNS ( 
            'Table', 
            "Value", 'Table'[C],
           "Col", "C"),
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[D],
           "Col", "D"),
        SELECTCOLUMNS ( 
            'Table', 
            "Value", 'Table'[E],
           "Col", "E")
)
VAR _minValue = MINX(_helpTable1, [Value])
VAR _minCol = CALCULATE ( MINX ( FILTER ( _helpTable1,  [Value] = _minValue ), [Col] ) )
VAR _secMinValue = CALCULATE ( MINX ( FILTER ( _helpTable1,  [Col] <> _minCol ), [Value] ) ) 
RETURN
IF ( SELECTEDVALUE('Table'[F]) = 1, _minValue, _secMinValue )

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

tackytechtom
Super User
Super User

Hi @UHS ,

 

Here the DAX code for a calculated column:

tackytechtom_0-1702972119333.png

Column =
VAR List_ = { 'Table'[A], 'Table'[B], 'Table'[C], 'Table'[D], 'Table'[E] }
VAR MinValue = MINX ( List_, [Value] )
VAR CountMinValue = COUNTROWS ( FILTER ( List_, [Value] = MinValue ) )
VAR FilteredValues= FILTER ( List_, [Value] > MinValue )
VAR secondMin =
IF ( 'Table'[A] = MINX ( FilteredValues, [Value] ), 'Table'[A],
IF ( 'Table'[B] = MINX ( FilteredValues, [Value] ), 'Table'[B],
IF ( 'Table'[C] = MINX ( FilteredValues, [Value] ), 'Table'[C],
IF ( 'Table'[E] = MINX ( FilteredValues, [Value] ), 'Table'[E],
IF ( 'Table'[D] = MINX ( FilteredValues, [Value] ), 'Table'[D], BLANK () ) ) ) ) )
RETURN
IF ( 'Table'[F] = 1, MinValue, IF ( CountMinValue > 1, MinValue, secondMin ) )

 

 

Please do not forget to mark this answer as a solution, if it solves your issue 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

9 REPLIES 9
tackytechtom
Super User
Super User

Hi @UHS ,

 

Here the DAX code for a calculated column:

tackytechtom_0-1702972119333.png

Column =
VAR List_ = { 'Table'[A], 'Table'[B], 'Table'[C], 'Table'[D], 'Table'[E] }
VAR MinValue = MINX ( List_, [Value] )
VAR CountMinValue = COUNTROWS ( FILTER ( List_, [Value] = MinValue ) )
VAR FilteredValues= FILTER ( List_, [Value] > MinValue )
VAR secondMin =
IF ( 'Table'[A] = MINX ( FilteredValues, [Value] ), 'Table'[A],
IF ( 'Table'[B] = MINX ( FilteredValues, [Value] ), 'Table'[B],
IF ( 'Table'[C] = MINX ( FilteredValues, [Value] ), 'Table'[C],
IF ( 'Table'[E] = MINX ( FilteredValues, [Value] ), 'Table'[E],
IF ( 'Table'[D] = MINX ( FilteredValues, [Value] ), 'Table'[D], BLANK () ) ) ) ) )
RETURN
IF ( 'Table'[F] = 1, MinValue, IF ( CountMinValue > 1, MinValue, secondMin ) )

 

 

Please do not forget to mark this answer as a solution, if it solves your issue 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Hi Tom,
It worked. Thank you so much

Thanks Tom,

 

This solution is not working for me.. I am getting the result as 1 for all the rows. 

tackytechtom
Super User
Super User

Hi @UHS ,

 

How about this:

tackytechtom_0-1702494893827.png

 

Here the DAX:

Measure = 
VAR _helpTable1 =
UNION ( 
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[A],
           "Col", "A"),
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[B],
           "Col", "B"),
        SELECTCOLUMNS ( 
            'Table', 
            "Value", 'Table'[C],
           "Col", "C"),
        SELECTCOLUMNS( 
            'Table', 
           "Value", 'Table'[D],
           "Col", "D"),
        SELECTCOLUMNS ( 
            'Table', 
            "Value", 'Table'[E],
           "Col", "E")
)
VAR _minValue = MINX(_helpTable1, [Value])
VAR _minCol = CALCULATE ( MINX ( FILTER ( _helpTable1,  [Value] = _minValue ), [Col] ) )
VAR _secMinValue = CALCULATE ( MINX ( FILTER ( _helpTable1,  [Col] <> _minCol ), [Value] ) ) 
RETURN
IF ( SELECTEDVALUE('Table'[F]) = 1, _minValue, _secMinValue )

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Thanks for this.. It worked 🙂

Idrissshatila
Super User
Super User

Hello @UHS ,

 

use the following calculated column 

SecondMinValue =
VAR List_ = { 'Table'[A], 'Table'[B], 'Table'[C], 'Table'[D], 'Table'[E] }
VAR MinValue =
    MINX ( List_, [Value] )
VAR FilteredValues =
    FILTER ( List_, [Value] > MinValue )
VAR secondMin =
    IF (
        'Table'[A] = MINX ( FilteredValues, [Value] ),
        'Table'[A],
        IF (
            'Table'[B] = MINX ( FilteredValues, [Value] ),
            'Table'[B],
            IF (
                'Table'[C] = MINX ( FilteredValues, [Value] ),
                'Table'[C],
                IF (
                    'Table'[E] = MINX ( FilteredValues, [Value] ),
                    'Table'[E],
                    IF ( 'Table'[D] = MINX ( FilteredValues, [Value] ), 'Table'[D], BLANK () )
                )
            )
        )
    )
RETURN
    IF ( 'Table'[F] = 1, MinValue, secondMin )

 

Idrissshatila_0-1702494069374.png

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Thanks @Idrissshatila  for sharing the solution. It is giving me the second lowest value but i am facing problem with one scenario. 

As seen in your power bi snapshot example for row 3.
I want the result value to be 2 and not 4. I know the second lowest value after 2 is 4 as per the example but for our client even the second lowest value is the repeated value of first lowest we would need that value only. 
So if A=2,B=2,C=4,D=5,E=5 then result for second lowest should be 2 and not 4. HOw can we achieve this along with your calculated calculation?

Hello @UHS ,

 

but logically speaking  the second low value between the following A=2,B=2,C=4,D=5,E=5  is 4 and the lowest is 2.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Thanks @Idrissshatila  for sharing with me.Appretiate it 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.