cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Customer next step

Hi all,
My data look like below 

 CustomerId         DATE                        START                                                           END                    JOURNEY_STATE
   1111                  2020-01-02             2020-01-02 22:16:23.000            2020-01-02 22:19:02.000        Arrival
   1111                  2020-01-02              2020-01-02 22:19:03.000           2020-01-02 22:23:17.000        Enquiry
    2222                  2020-01-02             2020-01-02 15:48:21.000            2020-01-02 15:49:09.000         Arrival
    2222                  2020-01-02            2020-01-02 15:49:10.000               2020-01-02 15:52:28.000       Navigation1

Count of each unique customer who is in the "Arrival" step and reached to "Navigation1"
 The count should be based on a two-point relationship that showcases the customers who convert from "Arrival" to "Navigation1" as a direct link/flow my data set is very large its nearly 2CR 

the count I need is =1

 

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Customer next step

The information you have provided is not making the problem clear to me. Can you please explain with an example and better data

Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Customer next step

@amitchandak  Here 2222 customer  from Arrival  (2020-01-02 15:49:09.000  session end previous record ) directly converted to  in Navigation1(2020-01-02 15:49:10.000 ,session start next record)
count number of customers converted from arrival to Navigation1 =1

Thanks for your reply 

 

Highlighted
Helper III
Helper III

Re: Customer next step

@amitchandak  My data
CustomerId   DATE                        START                                             END                   JOURNEY_STATE

 1111             2020-01-02      2020-01-02 22:16:23.000    2020-01-02 22:19:02.000     Arrival
 1111             2020-01-02      2020-01-02 22:19:03.000      2020-01-02 22:23:17.000      Enquiry
  2222            2020-01-02     2020-01-02 15:48:21.000       2020-01-02 15:49:09.000      Arrival
  2222            2020-01-02      2020-01-02 15:49:10.000       2020-01-02 15:52:28.000     Navigation1

 

Highlighted
Super User IV
Super User IV

Re: Customer next step

@manideep547 

I create a new column like :

 

Is nav = if(ISBLANK( MiNX(FILTER(journey,journey[START]>EARLIER(journey[end]) && journey[CustomerId]=EARLIER(journey[CustomerId]) && journey[JOURNEY_STATE] = "Navigation1"),journey[START])),"No","Yes")

 

 

You can modify as per need

File is attached below signature . if you need more help make me @

Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Customer next step

@amitchandak 
Screenshot (76).png
I think we should not use the journey,  [START]>EARLIER(journey[end]) see the above fig.
co column, define the next step of the customer.  
In the above data, the customer started his journey at arrival 2:10:07 Pm and next he went to Enquiry 10:55:16 PM in that column co the value should be Enquiry I am not getting which constraint we have to add to get that values. 


 

Highlighted
Super User IV
Super User IV

Re: Customer next step

@manideep547 , Can you share this data with the expected output? Let me check again

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Customer next step

@amitchandak 
values in co column are like below   
1]Enquiry
2]Departure
3]Navigation2
4]Navigation2
5]Navigation2
6]Navigation2
7]Navigation2
8]Navigation2
9]Navigation2
10]Navigation2
11]Navigation2
12]Departure
13]Navigation2

 

 

Highlighted
Helper III
Helper III

Re: Customer next step

@amitchandak  Here customer Id is repeating so we can't us MAX &MIN function to get next values in co column 

In SQL we can achieve this by using a lag function it gives the next values of the same customer. 
If we use MAX then, we have to compare only two rows of the customer.

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors