With the table below i want to look into each "possession id" with at least one "action" being a "pass" and see how many passes each "player" has received. the "sequence" += 1 for every action taken inside the same "possession id" meaning a player has received one pass when the "sequence" is one larger than the sequence where the action was a pass, within the same "possession id" How do i check with with at dax function?
possession_id sequence action player
54 1 pass pl
98 1 cross p2
76 1 shot p2
54 2 pass p3
23 1 shot p5
54 3 shot p8
87 1 shot p1
98 2 shot p9
The function would return 0 passes received for all players except p3 and p8 who received 1 pass each
My idea is to create a calculated column to check for each row if there's a "pass" in the previous sequence of the same possession or not :
Pass Received =
VAR CurrentPossessionID = 'MyTable'[possession_id]
VAR CurrentSequence = 'MyTable'[sequence]
VAR PreviousSequence = CurrentSequence - 1
RETURN
IF(
CONTAINS(
FILTER(
'MyTable',
'MyTable'[possession_id] = CurrentPossessionID &&
'MyTable'[sequence] = PreviousSequence &&
'MyTable'[action] = "pass"
),
[action], "pass"
),
1,
0
)
Then sum all instances where a player receives a pass :
Total Passes Received =
SUMX(
VALUES('MyTable'[player]),
CALCULATE(SUM('MyTable'[Pass Received]))
)