Thanks @lbendlin ,
That's useful to know. I'm new at this, and its not really my background - so still trying to work out the best way to learn and ask for help. I appreciate you taking the time to say I wasn't asking in the right way. I think my ask is maybe both complex, and uses an unusual data model?
My main general problem is, most of the Dax examples/ patterns I see are based on a sales database, often with a single sales table (sometimes an additional orders tables). The CRM I'm working with has a quite different structure to that and I'm struggling to adapt - here I've hit a brick wall. I think I was hoping someone may have adapted the SQLBI pattern (I should have provided the link previously) in a similar way (i.e. using multiples fact tables rather than just one), may be able to provide a bit of a modified template. I wasn't really expecting anyone to have the time to work out a whole new pattern just for this scenario.
One simple solution is maybe to merge the three activity fact tables into one table (the pattern should then be able to work with that single table) but the general advice seems to be not to do that. In my case they contain quite different data so it would be a bit of a mess I fear. Maybe this could be done in a virual table, I'm not sure if possible and what the implications might be of that approach.
My test files do contain two tables that are not immediately relevant - Outcomes and Assessments, but in a future version of my churn analysis I will need to bring them in. Once I've got a general approach to using this pattern across the three activities tables, I think it'll be fairly straightforward to add the two further tables.
I've already adapted the SQLBI template pattern to use a single table in my model (Project Enrolments) but that won't meet the business requirement, the pattern needs to scan the three activities tables to identify each type of customer during a period.
Essentially, in the template pattern, there are three types of measure, the first two are causing the difficulty:
1. There are three measures that identify the date in which each customer was new, lost, temporarily lost, and recovered within each period (say on a monthly basis) in my model this needs to be done at a project level, so a customer can be 'new' each time they attend (i.e. participate in either a visit, appointment, or workshop) linked to a project for the first time.
2. Then using these date measures, a second set of measures counts the clients that fall into each definition during the period.
3. A third will also use this segmentation to count the number of activities the clients have attended in total. This part is simple as it just a case of using an existing measure that simply counts the activities across the three tables.
Note: The project enrollment table is unique in my model in that it contains both a start and a end date (I had some help on this forum to adapt the template for this). The activities tables however each only have the one date:
'Visits' [VisitDate]
'Appointments'[StartDate]
'Workshop_Attendances'[WorkshipDate]
For example, take the new Client date measure:
Date New Client Enrolment =
CALCULATE (
MIN ( Project_Enrollments[Start Date]), -- The first enrollment is the MIN of enrolment Date
ALLEXCEPT (
Project_Enrollments, -- ignoring filters
Project_Enrollments[URN], -- other than client
Contact,
'Pre_Project'[Project Name] -- and the Project Name
)
)
Rather than just looking at the single Project Enrollments table, I need it to look across the three tables (Visits, Appointments, and Workshops). So the min date for each client needs to be across the three tables and the list of projects.
It then gets a bit more complex with the other measures, e.g. the measure Date Lost Client below, but once I have a general approach I think I can adapt the rest of the pattern.
Date Lost Client =
VAR MaxStartDate = CALCULATE( -- The last enrolment is the MAX of Enrollment Start Date and if present the Enrolment end date
MAX(Project_Enrollments[Start Date]), -- in the current time period (set by the calling measure)
ALLEXCEPT(
Project_Enrollments, -- ignoring any filter
Project_Enrollments[URN], -- other than Customer
Contact,
'Pre_Project'[Project Name] -- and Project Name
)
)
VAR MaxEndDate = CALCULATE(
MAX(Project_Enrollments[Finish Date]),
ALLEXCEPT(
Project_Enrollments,
Project_Enrollments[Finish Date],
Contact,
'Pre_Project'[Project Name]
)
)
VAR MaxDate = IF(
NOT ISBLANK(MaxEndDate),
MAX(MaxStartDate, MaxEndDate),
MaxStartDate
)
VAR Result = IF(
NOT ISBLANK(MaxDate),
EOMONTH(MaxDate, 2), -- two months added to the most recent enrolment start or finish date (end of month)
BLANK()
)
RETURN Result
Many thanks,
Adam
