topic Re: Flag first timestamp for a list of events per LeadId in DAX Commands and Tips

Flag first timestamp for a list of events per LeadId

Anonymous — Thu, 04 Mar 2021 20:59:12 GMT

Hi,

I need to identify and isolate the first timestamp modify per lead. The goal is to count number of leads per the first status of a lead.

I tried with the following:

Test =
VAR LowestTime =
MIN( 'SR.LeadDetails'[TimeStatusModified] )
RETURN
CALCULATE(
MIN( 'SR.LeadDetails'[TimeStatusModified] ),
ALLSELECTED(),
VALUES( 'SR.LeadDetails'[LeadId] )
)

Which gives me this:

So I can isolate the first timestamp, but when I wrap it in a IF and try to make it a 1 or 0, it messes up the filter context an table.

Any thoughts on how I can do this? It should be the first timestamp where a status is not "Blank".

Thank you!

// Ali

Re: Flag first timestamp for a list of events per LeadId

amitchandak — Fri, 05 Mar 2021 05:00:50 GMT

Anonymous , Try a measure like

measure = if(MIN( 'SR.LeadDetails'[TimeStatusModified] ) = calculate(MIN( 'SR.LeadDetails'[TimeStatusModified] ), filter( allselected('SR.LeadDetails'), 'SR.LeadDetails'[LeadId] = max('SR.LeadDetails'[LeadId]))) ,1 ,0)

Re: Flag first timestamp for a list of events per LeadId

Anonymous — Fri, 05 Mar 2021 07:17:05 GMT

Hi,

Thanks for your response.

Almost! But when I use that it shows up another row for some reason now? I also want it to not take "blank" into consideration so I changed it to:

Test =
IF(
MIN( 'SR.LeadDetails'[DateStatusModifiedFull] )
= CALCULATE(
MIN( 'SR.LeadDetails'[DateStatusModifiedFull] ),
FILTER(
ALLSELECTED( 'SR.LeadDetails' ),
'SR.LeadDetails'[LeadId] = MAX( 'SR.LeadDetails'[LeadId] )
),
'SR.LeadDetails'[Status] <> "Blank"
),
1,
0
)