Calculated column based on a value or a previous value

[dragon2]

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	Stop	FLAG
1	2000	A	B	0
1	2001	B	C	1
1	2002	C	C	1
2	1990	L	K	0
2	1991	B	M	1
2	1992	M	N	1
3	2021	S	B	0
3	2022	B	T	1
3	2024	T	U	1
3	2025	U	V	1

[MarkLaf · Accepted solution]

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 )

[dragon2]

Worked perfectly, I appreciate your help.

[Ashish_Mathur]

Hi,

This calculated column formula works

Flag = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&CALCULATE(MIN(Data[Year]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Start]="B"))<=EARLIER(Data[Year])&&Data[Start]="B"))+0

[rohit1991]

Hi @dragon2 ,

So when you want to flag if a previous or current row (same ID) has “B” in the Start column up to a given year.

Try this as a calculated column in DAX:

FLAG =
VAR CurrentID = Table[ID]
VAR CurrentYear = Table[Year]
RETURN
IF (
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            Table,
            Table[ID] = CurrentID &&
            Table[Year] <= CurrentYear &&
            Table[Start] = "B"
        )
    ) > 0,
    1,
    0
)

For each row, it looks back at all rows for the same ID where the year is less than or equal to the current year, and checks if any have “B” in Start. If yes, it flags it as 1. If not, you get 0.

Couple of tips, Make sure your [Year] column is a number, not text (if it’s text, just wrap with VALUE([Year])). If your data ever gets huge and you want to optimize, you can also do this in Power Query with a custom step. 

[dragon2]

Obtaining this error. When I hover over the EARLIER parameter, it reads, "Parameter is not the correct type."

[v-sdhruv]

Hi @dragon2 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[anilelmastasi]

Hello @dragon2 ,

Can you use below code:

FLAG =
VAR CurrentID = 'Table'[ID]
RETURN
IF (
CALCULATE (
COUNTROWS('Table'),
ALLEXCEPT('Table', 'Table'[ID]),
'Table'[ID] = CurrentID,
'Table'[Start] = "B"
) > 0,
1,
0
)