Search code examples
powerbidax

How do i use the EARLIER() in DAX?


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


Solution

  • 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
    )
    

    enter image description here

    Then sum all instances where a player receives a pass :

    Total Passes Received = 
    SUMX(
        VALUES('MyTable'[player]), 
        CALCULATE(SUM('MyTable'[Pass Received]))
    )
    

    enter image description here