Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Who was assigned when booking confirmed

Hi, 

 

I have 2 tables in my dataset as well as a date table and what I'm trying to figure out is who was assigned to a booking when it was confirmed.

Image below shows a list of Event Dates, this shows when a booking was confirmed (can be confirmed multiple times, but the examples i have are just one confirmation per booking)

The Audit Dates show who was assigned (TE column, names are anonymised) at what time.

The red TE column on the Event Dates is what I'm trying to acheive.

 

Simp0ly what i need is to know who was most recently assigned to a booking pior to it being confirmed, I then want to assign the confirmation of the booking against this person.

How is best to go about acheiving this.

 

The real data set has about 4k rows of data on Event Dates and 8,500 on the Audit Dates

 

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi LaurenceSD ,

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    Perform a lookup that finds the latest AuditDateTime before the EventDateTime for each booking. This can be achieved using DAX. Here's how to approach it in DAX:

    Step-by-Step in DAX:

    1. Ensure relationships are set: Event Dates[BookId] → Audit Dates[BookId] (many-to-many or many-to-one depending on your model)

    2. Create a calculated column on Event Dates table: This will find the TE (the assigned person) with the most recent AuditDateTime before the EventDateTime for that booking:

    DAX

    AssignedTEAtConfirmation =
    VAR currentBookId = 'Event Dates'[BookId]
    VAR currentEventTime = 'Event Dates'[EventDateTime]
    RETURN
    CALCULATE (
    MAX('Audit Dates'[TE]),
    FILTER (
    'Audit Dates',
    'Audit Dates'[BookId] = currentBookId &&
    'Audit Dates'[AuditDateTime] <= currentEventTime
    ),
    TOPN (
    1,
    FILTER (
    'Audit Dates',
    'Audit Dates'[BookId] = currentBookId &&
    'Audit Dates'[AuditDateTime] <= currentEventTime
    ),
    'Audit Dates'[AuditDateTime],
    DESC
    )
    )

    This logic filters the Audit Dates table to only the same BookId and where the AuditDateTime is less than or equal to the EventDateTime. Then it finds the latest TOPN 1 and gets the TE value.

    Note: This method works efficiently on larger datasets. If you want to display the ConsultantId or other metadata, you can follow the same logic replacing MAX('Audit Dates'[TE]) with the field you need. If your data model isn’t performing well, consider optimizing relationships or pre-aggregating in Power Query.

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

6 Replies