cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analystict
Helper I
Helper I

Set column to TRUE on 2nd appearance of a row

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.)

 

1 ACCEPTED SOLUTION

@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? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
petrovnikitamai
Resolver V
Resolver V

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())

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.

 

@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? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Works like a charm. Thank you.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors