PowerBI Record Changes

Question

I have a table in PowerBI.

Table-

I need to create a custom column, that detects this flag changes. Output should look like -

e.g= EID 3 changed flag in 2020. thats why new flag is set to 1. this is a sample , I have multiple eids.

EID 3 changed from N to Y but EID 2 was already flagged Y in 2019, so I don't understand why that's considered a change. — Alexis Olson, Feb 11 '21 at 19:40

score 0 · Answer 1 · answered Feb 11 '21 at 20:18

I'd recommend checking out this post to see different approaches for looking up previous flags.

One particular implementation might look like this (but there are lots of other ways to do the same thing):

New Flag =
VAR CurrYear = Table1[Year]
VAR PrevYear =
    CALCULATE (
        MAX ( Table1[Year] ),
        ALLEXCEPT ( Table1, Table1[EID] ),
        Table1[Year] < CurrYear
    )
VAR PrevFlag =
    CALCULATE (
        SELECTEDVALUE ( Table1[Flag] ),
        ALLEXCEPT ( Table1, Table1[EID] ),
        Table1[Year] = PrevYear
    )
RETURN
    IF ( ISBLANK ( PrevYear ) || Table1[Flag] = PrevFlag, 0, 1 )

This finds the previous year, if it exists, using a max (in case the years aren't contiguous), then looks up the flag for that particular year, and finally checks if the current flag differs from the previous flag (if there is a previous flag).

I am new to DAX , so just to be sure. This code will set flag to 1 for those EID's which changed flag from 2019 to 2020 for all EIDs. This code goes through every row right ? I dont see a loop or something. You've clearly explained how it works , but is my understanding correct? — DJKarma, Feb 12 '21 at 05:32
There's no loop because this code runs separately for each row in the calculated column (at least in principle; internal optimizations may do otherwise). Even though the DAX is the same, it returns different results for different rows because the row context is different. — Alexis Olson, Feb 12 '21 at 14:34

PowerBI Record Changes

1 Answers1