Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.)
Solved! Go to Solution.
@analystict Please give a try with this...
IsSecondTime = IF( RANKX( FILTER(Test02Cust,Test02Cust[CustomerID] = EARLIER(Test02Cust[CustomerID])), Test02Cust[CreatedDate],,ASC) = 2, TRUE(),FALSE())
Proud to be a PBI Community Champion
try to add index column in table in query editor
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())
Proud to be a PBI Community Champion
Works like a charm. Thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |