How To Filter Funnel Stages so out-of-order events are ignored

[loganjones]

I need help ensuring that my funnel only counts events for a user where there is a record of an event in the preceeding stage.

I have a table of UX events from users that have an associated stage, and that stage represents progress along a defined workflow.

Example Table:

User ID	Session ID	Stage
A	abcd	1
A	abcd	2
A	abcd	5
B	sdfwe	1
B	sdfwe	5

The stages may not always be consecutive as users may visit pages or select buttons out of order.  I only care to know about stages that had an event for the previous stage.  For example, user A is recorded as reaching stages 1, 2, 5, but I will only count that user for stages 1 and 2.  In this case stage 5 is really easy to do without going through earlier stages, and so the funnel without filtering will have a bulge on step 5 (not good).  

I think I need a measure that will filter out any events for users that are missing current stage - 1 and then do a distinct count. I just don't know how to tell it to filter based on the combination of User ID and Stage. When adding this measure to a funnel it will run the measure for each stage and look something like this:

This represents the distinct users that reached each stage consecutively.

I was thinking something like this:

UsersWithPreviousStage = CALCULATE(DISTINCTCOUNT(Funnels[UserId]), FILTER(Funnels, Funnels[Stage] = CurrentStage - 1))

But this doesn't treat each user seperately and I don't know how to specify the CurrentStage being filtered by the funnel.

Any help would be awesome!  Thanks!

BONUS: If you know a clever way to do the requested filtering by User ID or Session ID using a slicer (to select either View by User or View by Session), that would be wonderful!  For clarity, I mean filter out any user/stage combo that does not have user/(stage - 1), or alternatively, any session/stage that does not have session/(stage-1).  This way I can visualize at different aggregation levels without a lot of copy-paste.

Thanks again!

[amitchandak]

@loganjones , Try a measure like

UsersWithPreviousStage =
var _Stage = CALCULATE(DISTINCTCOUNT(Funnels[UserId]), FILTER(Funnels, Funnels[Stage] = max(Funnels[Stage] ) - 1 && Funnels[UserId] = max(Funnels[UserId])))
var _curr = DISTINCTCOUNT(Funnels[UserId])
return
countx(addcolumns(summarize(Funnels, Funnels[UserId], Funnels[Stage]), "_1", if(isblank(_Stage), blank(), [_curr])))

[loganjones · Accepted solution]

@amitchandak,The query you shared was helpful, if not what I was looking for exactly.  It did, however, inspire me, and this was the query that resulted (let me know if it violates some best practices):

UsersWithPreviousStage = 
var Stage = MAX(Funnels[Stage])
var Previous = IF(Stage = 1, Stage, Stage - 1) //Keep all of Stage 1
var PreviousStage = 
CALCULATETABLE(
 DISTINCT(Funnels[UserId]),
 ALL(Funnels[Stage]), 
 Funnels[Stage] = Previous
) 
var CurrentStage = DISTINCT(Funnels[UserId]) 
var CombinedStage = NATURALINNERJOIN(CurrentStage, PreviousStage)
RETURN COUNTROWS(CombinedStage)

In summary, I establish the users in the current and previous stages and then do an inner-join to see which ones exist in both.  I then just count the result to get number of users in both stages.  This seems to work, Thanks!

And to answer my bonus request, I'm thinking I will replace finding the unique User IDs with a measure that will use an IF statement to select either User or Session ID and dynamically pick which one to summarize by via that measure.