Dynamic Count of Value Combinations That Appear In Previous Rows

[Anonymous]

Hello PBI Community! I've been trying to work out a way to count how many times a rows combination of values appears in previous rows based on that specific rows entries but I'm hitting a wall...

For instance, I have a table that pools the entries from an online referral form, consisting of the referred persons code, the referrer's code, and a hidden field for when I pay the referrer for that referral. What I'd like to do is count how many times the combination of codes has been paid for each row, regardless of whether I've paid that specific referral or not. I made a reference column concatenating the values together hoping that would help ease the process but I've had no luck so far 😞

Below is a table of what I have with an added  "Paid Count" column of how I'd imagine the measured column would operate.

Referred	Referrer	Paid?	Concatenated Value	Paid Count
BHEH3	TNEW2	FALSE	BHEH3TNEW2FALSE	0
KJJK2	OJCK3	FALSE	KJJK2OJCK3FALSE	1
KJJK2	OJCK3	TRUE	KJJK2OJCK3TRUE	1
MJJD5	IHDH4	TRUE	MJJD5IHDH4TRUE	2
MJJD5	IHDH4	TRUE	MJJD5IHDH4TRUE	2
MGGE1	IJJK3	FALSE	MGGE1IJJK3FALSE	1
MGGE1	IJJK3	TRUE	MGGE1IJJK3TRUE	1
MGGE1	IJJK3	FALSE	MGGE1IJJK3FALSE	1

All I've managed so far is a column that will identify this number for the row that shows "TRUE" only, leaving all the repeated combinations that are still "FALSE" blank..

If anyone has any suggestions I'd love to hear them! Thank you all for your help!

[Anonymous]

Just an update to this, I think I was making it harder on myself than it needed to be haha. I ended up using a reference column that marks 1 for those paid and then used an expression like below to sum these values for each code pair/row:

Reference Column = IF( [Column3] = TRUE, 1, 0 )

Expression = 

       CALCULATE(

                    SUM( [Reference Column] ) ,

                    FILTER( 'Table', [Column1] = EARLIER( [Column1] ) && [Column2] = EARLIER( [Column2])

     )

Thanks again for the help @Greg_Deckler!

[Greg_Deckler · Accepted solution]

@Anonymous Can you paste as text? But essentially:

Column = COUNTROWS(FILTER('Table',[Column]=EARLIER([Column]) && [Column1] = EARLIER([Column1]) && [Some Column] = "TRUE"))

Something along those lines.

[Anonymous]

@Greg_DecklerGreg, thanks for your help! Your function does indeed count the amount of code combinations that have been paid, but it's only calulating it for the rows that have been paid. If at all possible, I'd like it to be able to show those same values for the ones that aren't paid as well. Like if Jerry referred Cindy three times but was only paid out for one, I'd like all three rows to show a value of 1, the way the function operates now would show "blank/null" for two of the rows and "1" for only one row. 

Retrying Pasting...

Referred   Referrer    Paid?      Concatenated Value     Paid Count
BHEH3,     TNEW2,    FALSE,     BHEH3TNEW2FALSE,     0/NULL
KJJK2,       OJCK3,      FALSE,     KJJK2OJCK3FALSE,         1
KJJK2,       OJCK3,      TRUE,      KJJK2OJCK3TRUE,          1
MJJD5,     IHDH4,      TRUE,      MJJD5IHDH4TRUE,         2
MJJD5,     IHDH4,     TRUE,      MJJD5IHDH4TRUE,          2
MGGE1,      IJJK3,     FALSE,     MGGE1IJJK3FALSE,          1
MGGE1,      IJJK3,     TRUE,      MGGE1IJJK3TRUE,           1
MGGE1,      IJJK3,     FALSE,     MGGE1IJJK3FALSE,          1

No Format:

Referred Referrer Paid? Concatenated Value Paid Count
BHEH3 TNEW2 FALSE BHEH3TNEW2FALSE 0
KJJK2 OJCK3 FALSE KJJK2OJCK3FALSE 1
KJJK2 OJCK3 TRUE KJJK2OJCK3TRUE 1
MJJD5 IHDH4 TRUE MJJD5IHDH4TRUE 2
MJJD5 IHDH4 TRUE MJJD5IHDH4TRUE 2
MGGE1 IJJK3 FALSE MGGE1IJJK3FALSE 1
MGGE1 IJJK3 TRUE MGGE1IJJK3TRUE 1
MGGE1 IJJK3 FALSE MGGE1IJJK3FALSE 1