Forum Discussion

mcomsto's avatar
mcomsto
Icon for Helper I rankHelper I
6 years ago
Solved

Finding the next event type in a sequence

Hi,

I have been struggling to get to the finish line with the analysis and I am hoping for some help.

 

I am tracking two visit types (SA, PC) for customers and I want to be able to associate the last visit date of the PC visit to the previous SA visits only if the PC visit is after the SA visit in the visit date sequence. I belive the image below does a better job of explaining what I am trying to accomplish, and my desired results are in the "Next PC Event" column. 

 

All help is greatly apprieciated.

 

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  mcomsto 

    You could just use this simple formula to create a calculate column

     

    Next PC Event = 
VAR NEXTPC=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Date]>EARLIER('Table'[Date])&&'Table'[Event Type]="PC")) RETURN
IF('Table'[Event Type]="PC",
BLANK(),
NEXTPC)

     

    Result:

     

    Regards,

    Lin

4 Replies

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi mcomsto ,

     

    I have achieved your desired output, but using power query.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFLDoAgDETvwtqkH1Rg2XgBjUvj/a8hEz9RUhd08zKvA2xbMFtCF+apDqmHe2IhZSlh7z5UQTNxbOlqdURQIU5vajeFWVrzQ2FWJVaXwqzxj/Ywl6aV3Z0HtNK/VuPVKrs0YW+lxTVnZBMJu9mCvSOJf19hjMHB5zfgtfSV3g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Event Type" = _t, #"Customer Index" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Event Type", type text}, {"Customer Index", Int64.Type}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)
in
    #"Added Index"
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFLDoAgDETvwtqkH1Rg2XgBjUvj/a8hEz9RUhd08zKvA2xbMFtCF+apDqmHe2IhZSlh7z5UQTNxbOlqdURQIU5vajeFWVrzQ2FWJVaXwqzxj/Ywl6aV3Z0HtNK/VuPVKrs0YW+lxTVnZBMJu9mCvSOJf19hjMHB5zfgtfSV3g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Event Type" = _t, #"Customer Index" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Event Type", type text}, {"Customer Index", Int64.Type}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table (4)", {"Index"}, "Table (4)", JoinKind.LeftOuter),
    #"Expanded Table (4)" = Table.ExpandTableColumn(#"Merged Queries", "Table (4)", {"Customer ID", "Event Type", "Date"}, {"Table (4).Customer ID", "Table (4).Event Type", "Table (4).Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (4)", "Next PC Event", each if [#"Table (4).Event Type"] = null then null else 
if [Customer ID] <> [#"Table (4).Customer ID"] then ""
else 


if [Customer ID] = [#"Table (4).Customer ID"]
and Text.Contains([Event Type], "SA") 
and Text.Contains([#"Table (4).Event Type"], "PC")
then [#"Table (4).Date"]
else

if  Text.Contains([Event Type], "PC") 
and Text.Contains([#"Table (4).Event Type"], "SA")
then ""


else
null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Customer ID", Order.Descending}, {"Customer Index", Order.Ascending}}),
    #"Filled Up" = Table.FillUp(#"Sorted Rows",{"Next PC Event"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Table (4).Customer ID", "Table (4).Event Type", "Table (4).Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Next PC Event", type date}})
in
    #"Changed Type1"

     

     

     

    What I did is i duplicated your column and merged them.

    See the queries above.

     

    Output:

    Hope this helps.

    Also, here is the link for the sample pbix

    https://gofile.io/?c=3FU7kC

    • mcomsto's avatar
      mcomsto
      Icon for Helper I rankHelper I
      6 years ago

      Thank you for taking the time to come up with this solution.  I may end up using this as well.

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi  mcomsto 

    You could just use this simple formula to create a calculate column

     

    Next PC Event = 
VAR NEXTPC=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Date]>EARLIER('Table'[Date])&&'Table'[Event Type]="PC")) RETURN
IF('Table'[Event Type]="PC",
BLANK(),
NEXTPC)

     

    Result:

     

    Regards,

    Lin

    • mcomsto's avatar
      mcomsto
      Icon for Helper I rankHelper I
      6 years ago

      I have a followup question.  I would like to count the number of rows where the Next PC Event exists.  Is this possible?  I am having an issue where the count is not returning an accurate count.  Is it because the Next PC Event is a calculated column?  If so, how would I accomplish this?