cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
analystict Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

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

Proud to be a Datanaut !





4 REPLIES 4

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())
analystict Regular Visitor
Regular Visitor

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

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
Super User

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

Proud to be a Datanaut !





analystict Regular Visitor
Regular Visitor

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

Works like a charm. Thank you.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 361 members 4,363 guests
Please welcome our newest community members: