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

[UHS]

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

[tackytechtom · Accepted solution]

Hi @UHS ,

How about this:

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/

[tackytechtom · Accepted solution]

Hi @UHS ,

Here the DAX code for a calculated column:

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/

[UHS]

Hi Tom,
It worked. Thank you so much

[UHS]

Thanks Tom,

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

[tackytechtom · Accepted solution]

Hi @UHS ,

How about this:

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/

[UHS]

Thanks for this.. It worked 🙂

[Idrissshatila]

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 )

[UHS]

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?

[Idrissshatila]

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.

[UHS]

Thanks @Idrissshatila  for sharing with me.Appretiate it 🙂