Forum Discussion

itsmebvk's avatar
itsmebvk
Continued Contributor
5 years ago
Solved

Dynamically Get First , Second, Third Lowest Values?


Hi Experts,

 

I need some dax help to achieve a requirement , can you please share your inputs around this requirement.

 

I have a Multiselect slicer which shows 1 to 10 numbers. Based on numbers user selcted I want to get Minimum, Second Minimum, Third Minimum values dynamically in Different columns.

 

For example :

 

If user selects 4,7,8,9,10 I want to get 4 as Minimum values column, 7 as Second Minimum Values column and 8 as third minimum values column.

 

If user selects 1,2,4,7,8,9,10 I want to get 1 as Minimum values columns, 2 as Second Minimum Values column  and 4 as third minimum values column.


Alternatively If we can create one column also fine.

 

Please suggest. Thank you.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, itsmebvk 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Min Value Measure =
    MAXX (
    TOPN (
    1,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
    Second Min Value Measure =
    MAXX (
    TOPN (
    2,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
    Third Min Value Measure =
    MAXX (
    TOPN (
    3,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, itsmebvk 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Min Value Measure =
    MAXX (
    TOPN (
    1,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
    Second Min Value Measure =
    MAXX (
    TOPN (
    2,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
    Third Min Value Measure =
    MAXX (
    TOPN (
    3,
    ALLSELECTED ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ), ASC
    ),
    'Table'[Value]
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • itsmebvk's avatar
      itsmebvk
      Continued Contributor
      5 years ago

      Jihwan_Kim  excellent, worked like a charm. Thank you so much for your help Jihwan. Really appreciate your help.