Hi All,
I'm trying to write two calculated columns in Dax. Been starring at it for a few weeks now, with help from this forum that's got me closer but not there yet. This is a bit of re-post now that I have a fully worked up demo dataset and .pbix file.
The first calculated column will find the first activity (i.e. min/ lowest date) that each client took part in (across four tables - Appointments, Visits and Workshop Attendances, and Enrollments), and returns the project linked to that activity. If I'm honest, I’m not really sure how to manage any situation where the date of the first interaction for a client has two activities linked to different projects on the same day - maybe it should concatenate the two projects, or maybe just return "Multiple Projects".
The second, will find the most recent (i.e. latest or MAX date) for the activities, and again return the project linked to that activity.
I've put together a dummy dataset and .pbix file at the link below:
https://www.dropbox.com/scl/fo/yywvidb85p6vgk1buw3gh/h?rlkey=9nkcp3zam0a4mziynl5hvvdek&dl=0
The draft columns are currently in the 'Contact' table but eventually they need to be in the 'Contact Fact' table. The first column will act as a relationship key, linking a client registration to a project - which otherwise is only possible using a list of staff in each project, which would change overtime and be a pain to manage.
The dax is not quite working currently, and seems to be returning the min and max projects based on their alphabetical sorting rather than the date, at least in part.
For example the expected result for the following sample of clients would be (in brackets I’ve just added the type of activity and the date, but I just want the project to be returned):
First Project Most Recent Project
URN1 RRR (Visit on 01/01/2023 AAA (Appointment on 01/01/24)
URN2 RRR (Visit on 08/01/23) CCC (Workshop Attendance 18/07/23)
URN3 GGG (Appointment on 10/01/23) CCC (Workshop Attendance 21/07/23)
URN20 AAA (Appointment on 01/01/23) AAA (Appointment on 01/12/23)
There's a visualisation showing the wrong results on page 1, based on the current dax, which is in the following format:
First Project Participated =
CALCULATE (
IF ( // checks if a client has had the following activities, if row count greater than Zero
COUNTROWS (
UNION (
VALUES('Visits'[URN]),
VALUES('Appointments'[URN]),
VALUES('Workshop_Attendances'[URN]),
VALUES('Project_Enrollments'[URN])
)
) > 0,
MINX (
TOPN (
1, // Finds the first row in the table below, based on the earliest date
SUMMARIZE (
UNION ( // Creates a table with the following columns -
SELECTCOLUMNS(RELATEDTABLE('Visits'), "Project", [Project]),
SELECTCOLUMNS(RELATEDTABLE('Appointments'), "Project", [Project]),
SELECTCOLUMNS(RELATEDTABLE('Workshop_Attendances'), "Project", [Project]),
SELECTCOLUMNS(RELATEDTABLE('Project_Enrollments'), "Project", [Project])
),
[Project] // Not really sure what this does?
),
CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC // Finds the first/ earliest date in the table of activities generated above - based on relationship to the calendar table
),
[Project] // Returns the Project Name for the Appointment, Workshop, ConnectionZone Visit, Project Enrollment, or Client Survey with the earliest date (The Project linked to the first activity)
),
"Not Yet Joined A Project" // Returns this if a client has not has either am Appointment, Workshop, ConnectionZone Visit, Enrolled in a project, or had a Client Survey
)
)
I'm using the same files for a different problem with respect to client churn so that part of the .pbix file can be ignored.
Do let me know if I missed something, and there are any questions.
Many thanks for any help you can offer.
Adam
