Hi,
I have found it very challenging to explain this scenario. I will try my best using 'expected outcome'. See links below. The expected outcome is in Table C, Column name = 'Commit counts'. All other columns are derived using logic I have written in Power Query. I am open to Power query based or Excel formula based solution that is efficient/fast.
Expected outcome screenshot: https://i.stack.imgur.com/SbE0N.jpg
Sample Test Data: https://docs.google.com/spreadsheets/d/10VOhG_ryqHP6v-kDDIp2Y8TWaYwl00P-/edit?usp=share_link&ouid=10...
1.0 I want a 'Count' of all valid rows in Table B. (These are typical Git hub commits, by developers tied to a particular set of Application Ids. End goal is to count Total commits per Scrum team to measure developer efficiency)
1.1 For all valid [Team member email IDs] that are matching between Table A & Table B
1.2 Where there is a valid [Team Workspace name] + [Application ID] static entry in Table C, calendarize the total number of commits per Team workspace name + Application ID in the 'Commit counts' column.
Note: Karen's commits should not be counted towards Team Alpha. As Application ID 333333333 + Team Alpha is not a valid (static) combination in Table C.
Note:Tom's commits should not be counted as Tom is not part of the Team Aplha.
I have tried the below excel function. It seems to be working. It is very slow and not efficient. I want to understand if there is a better, faster way to derive the 'count'. My data size is full enterprise wide data set (over 700,000 commits records in Table B alone).
I have tried this so far: Table A = Table 1, Table B = Table 2, Table C = Table 3
=LET(
emails, FILTER(
Table2[commit_author_email],
(Table2[Application ID] = [@[Application ID]]) * (Table2[commit date] = [@Month]),
NA()
),
validemails, IF(
OR(ISERROR(emails)),
NA(),
FILTER(Table3[Team member email ID], Table3[Workspace Name] = [@[Workspace Name]], NA())
),
counts, IF(OR(ISERROR(_xlpm.validemails)), NA(), MAP(emails, LAMBDA(a, --OR(validemails = a)))),
IFNA(SUM(counts), 0)
)
