Forum Discussion
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 | 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 |
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 )
7 Replies
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 )
Hello Anonymous ,
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
)Hi Anonymous ,
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 YouHi Anonymous ,
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.
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
