Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
Can't seem to solve this - I want to count the candidates that move from 'Job Application Created' with a Media Campaign form to 'Placement Created'. And only if that's in a time period of max. 60 days.
What I tried is
Social count =
VAR _Start = MAXX( FILTER((Activities), Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid]) && Activities[act_job_application_form_name]="Media Campaign form" && Activities[act_description]="Job Application Created"), Activities[act_job_application_created_date])
VAR _End = MAXX( FILTER((Activities), Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid]) && Activities[act_description]="Placement Created"), Activities[act_job_application_created_date])
RETURN
CALCULATE(
DISTINCTCOUNT(Activities[act_candidate_sfid]),
FILTER(Activities,
Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid])
&& DATEDIFF(_Start, _End, DAY)<60
))
In this dataset there's 2 counts for a valid sequence like described. There are multiple Job Applications Created per candidate but it's about those with a media campaign form and who are followed up by a Placement created.
Adding data table via wetransfer:
Thanks!
Jan
Solved! Go to Solution.
Hi @jbrooi ,
Please try:
Measure =
VAR _a =
FILTER (
Activities,
( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
|| ( Activities[act_description] = "Placement Created" )
)
VAR _b =
SUMMARIZE ( _a, [act_candidate_sfid] )
VAR _c =
ADDCOLUMNS (
_b,
"StartDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& ( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
),
[act_job_application_created_date]
),
"EndDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& ( Activities[act_description] = "Placement Created" )
),
[act_job_application_created_date]
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Activities[act_candidate_sfid] ),
FILTER ( _c, DATEDIFF ( [StartDate], [EndDate], DAY ) <= 60 )
)
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jbrooi ,
Please try:
Measure =
VAR _a =
FILTER (
Activities,
( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
|| ( Activities[act_description] = "Placement Created" )
)
VAR _b =
SUMMARIZE ( _a, [act_candidate_sfid] )
VAR _c =
ADDCOLUMNS (
_b,
"StartDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& ( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
),
[act_job_application_created_date]
),
"EndDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& ( Activities[act_description] = "Placement Created" )
),
[act_job_application_created_date]
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Activities[act_candidate_sfid] ),
FILTER ( _c, DATEDIFF ( [StartDate], [EndDate], DAY ) <= 60 )
)
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, much appreciated @v-jianboli-msft !
After reviewing I had to add statements for an Index field I have in the dataset, to check for multiple batches of Applications and Placements - and not just the last one (from the MAXX).
I'm adding the code here for reference:
Social plaatsingen =
VAR _a =
FILTER (
Activities,
( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
|| ( Activities[act_description] = "Placement Created" )
)
VAR _b =
SUMMARIZE ( _a, [act_candidate_sfid] , Activities[pl_segm_index])
VAR _c =
ADDCOLUMNS (
_b,
"StartDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
&& ( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
),
[act_job_application_created_date]
),
"EndDate",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
&& ( Activities[act_description] = "Placement Created" )
),
[act_job_application_created_date]
),
"SegmentStart",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
&& ( Activities[act_job_application_form_name] = "Media Campaign form"
&& Activities[act_description] = "Job Application Created" )
),
Activities[pl_segm_index]
),
"SegmentEnd",
MAXX (
FILTER (
Activities,
[act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
&& Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
&& ( Activities[act_description] = "Placement Created" )
),
Activities[pl_segm_index]
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Activities[act_candidate_sfid] ),
FILTER ( _c, DATEDIFF ( [StartDate], [EndDate], DAY ) <= 60 && [SegmentStart]=[SegmentEnd])
)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |