Help Debugging a Calculated Column to Find First Project Participated In

Hi All,

I'm still trying to debug this calculated column, with much thanks to 123abc who has pointed me in a good direction. and I think its nearly there.

I've also now created a demo dataset and .pbix file to explain better what's going on, and the result I need:

https://www.dropbox.com/scl/fo/ys84v81p0k4nms68r4mxy/h?rlkey=8u3rrcg7vax8q41ggimrignli&dl=0

There are two Calculated Columns in the Contact table. I think somewhere in the Dax, part of the code is sorting the list of activities alphabetically by the project name rather than the date, this is returning the wrong result.

For example, for URN01, the first project attended should be RRR (The Visit on 01/01/2023) not AAA, and the most recent project should be AAA, the Appointment on 31/12/2023, not AAA. The MINX or maybe the TOPN function I think its sorting by the project column, and passing the wrong data to the MIN (Calendar [Date]) part of the code.

Anyway that's my best guess of what's happening. I don't know Dax well enough to fix it however, and it might be something else anyway.

Just to add. For the Coloumn [Most Recent Project Participated] there's some added issues. I need it to use the inactive calendar relationship to 'Project_Enrollment'[Finish Date]. Sometimes this will be blank, i.e. if the Enrollment is still active, in which case it should use the active date relationship to 'Project_Enrollment'[Start Date].

Finally, I would like to have these columns in the Contact_Fact table, but that means navigating a inactive (bi-directional) relationship to the contact table - where all the URN references link to. Again not really sure how to work that in.

Many thanks, apprecaite any further support.

Adam