Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculated column based on a value or a previous value

Hello, I need to create the calculated column "FLAG" with DAX. Its value will be a 1 if the Start for the ID is B or has been B in the past. Otherwise, the value will be 0.   ID Year Start St...
  • MarkLaf's avatar
    1 year ago

    Here is another way to do it. This grabs all the rows of same ID, with same or earlier year, where Start = B; then checks if the result is not empty (ie we returned at least one row that meets the criteria). We then just cast the boolean to INT to get the desired output. We use VAR to capture our values from earlier row context rather than EARLIER*.

     

    FLAG = 
    VAR _thisYear = Data[Year]
    RETURN
    CALCULATE(
        CONVERT( NOT ISEMPTY( Data ), INTEGER ),
        ALLEXCEPT( Data, Data[ID] ),
        Data[Start] = "B",
        Data[Year] <= _thisYear
    )

     

     

    *EARLIER was a needed function before variables were introduced in DAX. Now, "it is recommended using variable (VAR) saving the value when it is still accessible, before a new row context hides the required row context to access the desired value." ( https://dax.guide/earlier )