Forum Discussion

AuroraNI's avatar
AuroraNI
Helper III
5 years ago
Solved

difference based on dynamic rank

Hi,

I have the below table in PBI and I would like another column in this table that calculates the time differences between 1st place and all the other runners, and this would change based on the event slicer.

below image is how I would like it to display:

Thanks to anyone who can provide some assistance!

Need Help

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    AuroraNI , Create a new column like

    New colum =

    var _1 = minx(filter(Table, event =earlier([event]) && [Time] > earlier([Time])), [Time])

    return

    if(isblank( _1),0, _1 -[Time])

     

    • AuroraNI's avatar
      AuroraNI
      Helper III
      5 years ago

      Hi amitchandak thank you for taking the time to repond and help me.  The column calculates the time difference from the next runner. 

       

      I would like the time difference to be calculated always from the no.1 Rank runner like below

      sorry if that was not clear in my last post

      Thank you for the help.

       

      • AuroraNI's avatar
        AuroraNI
        Helper III
        5 years ago

        hi amitchandak thank you again for helping, in the initial post.  Would you have time to look at my 2nd question please?  Thank you.

        Olly

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi AuroraNI ,

     

    Create a calculated column as "dynamic rank"

    rank = RANKX(FILTER('Table',EARLIER('Table'[event])='Table'[event]),'Table'[time],,ASC)

    Then create a measure like this:

    Measure = var _rank = SELECTEDVALUE('Table'[rank])
var _event = SELECTEDVALUE('Table'[event])
var diff = CALCULATE(MAX('Table'[time]),FILTER(ALL('Table'),'Table'[rank]=_rank-1&&'Table'[event]=_event))
var current_ = MAX('Table'[time])
return IF(ISBLANK(diff),0,current_-diff)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • AuroraNI's avatar
      AuroraNI
      Helper III
      5 years ago

      hi V-lianl-msft thank you for taking the time to help me.  I would like the measure to calculate the time from rank =1 so the time difference gets bigger as the rank increases like below:

      Thank you for your help.