Forum Discussion

bisartorelli's avatar
bisartorelli
New Member
3 years ago
Solved

Get Next Record for the same ID

Hi guys,

I'm kinda new in PBI and need help with this logic. 

I did something in M (following a youtube video), but its taking too long to join the tables and it is timing out in the service.

 

I want to know if you guys have another solution for this problem:

I need to identify if the same ID (Customer + Child) had a Rebook or not. In this case, I'll need to check if the same ID has a "next date".

 

This is the original data:

 

Customer NameChild's NameAppointment
AAAAAXXXXX9/8/2022
AAAAAXXXXX9/9/2022
AAAAAYYYYY10/1/2022
AAAAAYYYYY10/5/2022
BBBBBXXXXX11/2/2022
BBBBBXXXXX11/5/2022
BBBBBXXXXX11/7/2022
BBBBBXXXXX11/8/2022
BBBBBYYYYY12/5/2022

 

This is what I am trying to do - if Next Appointment > Appointment then Rebook else No Rebook. 

 

Customer NameChild's NameAppointmentNext AppointmentResult
AAAAAXXXXX9/8/20229/9/2022Rebook
AAAAAXXXXX9/9/2022NULLNo Rebook
AAAAAYYYYY10/1/202210/5/2022Rebook
AAAAAYYYYY10/5/2022NULLNo Rebook
BBBBBXXXXX11/2/202211/5/2022Rebook
BBBBBXXXXX11/5/202211/7/2022Rebook
BBBBBXXXXX11/7/202211/8/2022Rebook
BBBBBXXXXX11/8/2022NULLNo Rebook
BBBBBYYYYY12/5/2022NULLNo Rebook

 

Could you pleeeeease help me with that? Any ideas?

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • lukiz84's avatar
    lukiz84
    3 years ago

    Hi,

     

    2 calculated columns:

     

     

    Next Appointment :=
      CALCULATE(
          MIN(YourTable[Appointment]),
          FILTER(
              YourTable,
              YourTable[Customer Name] = EARLIER(YourTable[Customer Name]) && 
              YourTable[Childs Name] = EARLIER(YourTable[Childs Name]) && 
              YourTable[Appointment] > EARLIER(YourTable[Appointment])
          )
       )

     

     

    and

     

     

    Result := 
   IF(
      ISBLANK(YourTable(Next Appointment]),
      "No Rebook",
      "Rebook"
   )

     

     

2 Replies

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

     

    2 calculated columns:

     

     

    Next Appointment :=
      CALCULATE(
          MIN(YourTable[Appointment]),
          FILTER(
              YourTable,
              YourTable[Customer Name] = EARLIER(YourTable[Customer Name]) && 
              YourTable[Childs Name] = EARLIER(YourTable[Childs Name]) && 
              YourTable[Appointment] > EARLIER(YourTable[Appointment])
          )
       )

     

     

    and

     

     

    Result := 
   IF(
      ISBLANK(YourTable(Next Appointment]),
      "No Rebook",
      "Rebook"
   )