Forum Discussion
Advocate IIWho was assigned when booking confirmed
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
Community SupportHi LaurenceSD ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Regards,
Dinesh
Advocate IIApologies, you should now be able to access the spreadsheet, data is on Sheet2
- v-dineshya
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