Forum Discussion

Jeffrey_VC's avatar
Jeffrey_VC
Icon for Helper III rankHelper III
4 years ago
Solved

Max funtion

Hello Community,


I'm a newbie who is interesting in Power Bi and the working of DAX.

Now I'm trying to get the penultimate record in a colum. Is there a function in DAX what can help me?

 

Please can anyone help me in achiving this measure.

 

Thanks,

Jeffrey

 

  • johnt75's avatar
    johnt75
    4 years ago

    If you have a unique column in the table, like an ID, then you could do something like

    Penultimate value =
VAR maxID =
    MAX ( 'Table'[ID] )
VAR val =
    SELECTCOLUMNS (
        CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[ID] ), 'Table'[ID] < maxID ),
        "@val", 'Table'[Desired column]
    )
RETURN
    val

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    If you have a unique column in the table, like an ID, then you could do something like

    Penultimate value =
VAR maxID =
    MAX ( 'Table'[ID] )
VAR val =
    SELECTCOLUMNS (
        CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[ID] ), 'Table'[ID] < maxID ),
        "@val", 'Table'[Desired column]
    )
RETURN
    val
    • Jeffrey_VC's avatar
      Jeffrey_VC
      Icon for Helper III rankHelper III
      4 years ago

      Hi Johnt75,

       

      Yes that looks like a solution. Thnx!

  • colacan's avatar
    colacan
    Icon for Resolver II rankResolver II
    4 years ago

    Hi, You can filter out the row which has minium value then get the minimum value to find penultimate as below.

     

    I hope this helps you. 

    Thanks

     

     

    • Jeffrey_VC's avatar
      Jeffrey_VC
      Icon for Helper III rankHelper III
      4 years ago

      Hi,

      Thank you for your answer.

      I have tried:

       

      Penultimate =
      var minscore = minx(all('Ledigingen uit Middleware'[Datum/tijd]),
              'Ledigingen uit Middleware'[Datum/tijd])
      var penul_wo_min = FILTER('Ledigingen uit Middleware',
           'Ledigingen uit Middleware'[Datum/tijd] <> minscore)
      return MINX(penul_wo_min,'Ledigingen uit Middleware'[Datum/tijd])
       
       
      The result is not the Penultimate but the first in the dataset:
       

      And I need: