Reply
Regular Visitor
Posts: 16
Registered: ‎07-24-2018
Accepted Solution

Set column to TRUE on 2nd appearance of a row

[ Edited ]

Hi,

 

Couldn't find anything that matches what I'm looking for and I couldn't figure a good way to do this.

 

What I am trying to do is a second time deposit report - in order to see how many clients/percentage of clients has made a second depsit. In order to achieve that, I need a way to count only deposits that appear the 2nd time according to a specific identifier. In our case, our identifier is CustomerID. I will illustrate:

 

CustomerID            |    Amount              |    is_second_time

4                     |    $1000               |    FALSE (1st instance, so FALSE).
5                     |    $2000               |    FALSE (1st instance, so FALSE).
4                     |    $1500               |    TRUE (2nd instance, so TRUE).
4                     |    $4000               |    FALSE (3rd instance, so FALSE, etc..).
5                     |    $2000               |    TRUE (2nd instance, so TRUE).
5                     |    $2000               |    FALSE (3rd instance, so FALSE, etc..).

 

I think it's pretty clear, just need to mark 2nd instances of transactions according to CustomerID so I can use it in a measure to filter out only those with is_second_time = TRUE. (is_second_time is basically the column that will change to either TRUE or FALSE.)

 


Accepted Solutions
Super User
Posts: 680
Registered: ‎04-02-2017

Re: Set column to TRUE on 2nd appearance of a row

@analystict Please give a try with this...

 

IsSecondTime = IF(
                   RANKX(
                     FILTER(Test02Cust,Test02Cust[CustomerID] = EARLIER(Test02Cust[CustomerID])),
                     Test02Cust[CreatedDate],,ASC) = 2, TRUE(),FALSE())

image.png



Did I answer your question? Like and Mark my post as a solution!

Proud to be a Datanaut !





View solution in original post


All Replies
Member
Posts: 66
Registered: ‎05-28-2018

Re: Set column to TRUE on 2nd appearance of a row

try to add index column in table in query editor 

цф.jpg

and use dax formule

secondValue = IF(COUNTROWS(FILTER('table';EARLIER('table'[CustomerID])='table'[CustomerID]&&EARLIER('table'[Index])>='table'[Index]))=2;TRUE();FALSE())
Regular Visitor
Posts: 16
Registered: ‎07-24-2018

Re: Set column to TRUE on 2nd appearance of a row

[ Edited ]

It partly worked, but not exactly... I forgot to mentioon that the CustomerID is random and not ascending. So it can sometimes see a transaction that was created on the 15/01/2018 15:15 as the second one (TRUE) and a transaction that was created on the 15/01/2018 15:16 as the first one (FALSE).

 

So that way lets say we got transactions with the same CustomerID with the dates:

 

16/01/2018 15:15 -> FALSE

12/01/2018 15:15 -> FALSE

25/12/2017 15:15 -> FIRST ONE (FALSE)

05/01/2018 15:15 -> ACTUAL SECOND ONE (TRUE)

17/01/2018 15:15 -> FALSE

 

 

Right now I think it's going randomly, like:

 

16/01/2018 15:15 -> FALSE

12/01/2018 15:15 -> FALSE

25/12/2017 15:15 -> FALSE (CORRECT)

05/01/2018 15:15 -> FALSE -> (this one should be true because it's the actual second transaction from the customer).

17/01/2018 15:15 -> TRUE -> (this one should be false although it is a second instance, but it is not the actual second transaction from the customer)

 

 

Is there a way to make it apply as in the oldest, actual second transaction? Let's say the date column is named created_on.

 

Super User
Posts: 680
Registered: ‎04-02-2017

Re: Set column to TRUE on 2nd appearance of a row

@analystict Please give a try with this...

 

IsSecondTime = IF(
                   RANKX(
                     FILTER(Test02Cust,Test02Cust[CustomerID] = EARLIER(Test02Cust[CustomerID])),
                     Test02Cust[CreatedDate],,ASC) = 2, TRUE(),FALSE())

image.png



Did I answer your question? Like and Mark my post as a solution!

Proud to be a Datanaut !





Highlighted
Regular Visitor
Posts: 16
Registered: ‎07-24-2018

Re: Set column to TRUE on 2nd appearance of a row

Works like a charm. Thank you.