Forum Discussion

japolo's avatar
japolo
Frequent Visitor
3 years ago

Rows or values present between two row values

Hi,

I've ran into a question that seems complicated to decipher. I have a dataset where an initiated session has multiple rows, where every action is in sequential order. What I want to look at is the first value where label is e.g., "planner2d", then look at when the "planner3d" is present during the sequence. If it comes immediatelly after, it can return 1, and if there is a certain event happening in between, the number of rows in between can be returned, in the image, it would return 27.

Is it possible to check the first value in a column within the same session, and check when the next set value appears? I've tried creating a separate column but I can't figure out the count between, is this doable?

Thank you in advance!

 

/J

Need Help

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi japolo 

    Thanks for reaching out to us.

    you can try this

    Difference = 
var _id1=MINX(FILTER('Table','Table'[label]="planner2d"),[sequence_nbr])
var _id2=MINX(FILTER('Table','Table'[label]="planner3d"),[sequence_nbr])
return _id2-_id1-1

     

     

    Best Regards,

    Community Support Team _Tang

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

    • japolo's avatar
      japolo
      Frequent Visitor
      3 years ago

      Hi v-xiaotang ,
      I've tried it but it doesn't work for me. I'm not sure if your solution is created based on just one id for example, but mine seems to check if there is a label and returns the first number, which is always 0.

      There are lots of IDs with their own number of sequences. I've tried with calculate and removing filters from the id but that just returns the sequence number

      Edit:
      Since the step can happen at different sequential steps, I need the filter on the id to stay, but the calculate doesn't work propely

       

      /J