Populate a column based on the latest boolean value

[dphillips]

A list of students are stored in a database. Every term, the students are rolled over to the next term so in the students table there will be mulitple rows for each student. For each row, the status of a student, called the IBFlag, is one of the fields. See table below. When the student's YearLevel = 11 the IBFlag may change to True. I want to created a column called FinalFlag which is 0 in every row for a student if their IBFlag is always false but, if the IBFlag becomes TRUE in Year 11  then every row for that student right back to when they were in their first year level is set to 1. This will allow me to set a filter of 1 with the FinalFlag and for all the data to show for a student, not just when he was in Year 11 and 12.

Year	Semester	ID	YearLevel	IBFlag	FinalFlag
2012	1	110421	5	FALSE	0
2012	2	110421	5	FALSE	0
2013	1	110421	6	FALSE	0
2013	2	110421	6	FALSE	0
2014	1	110421	7	FALSE	0
2014	2	110421	7	FALSE	0
2015	1	110421	8	FALSE	0
2015	2	110421	8	FALSE	0

Here is a link to data and an example of what the FinalFlag coulumn should look like.

Any help on the best way to do this would be much appreciated.

[Anonymous · Accepted solution]

Try this:

FinalFlag =
VAR Test = 
	CALCULATE(
		MAX( TableName[IBFlag] )
		,ALLEXCEPT( TableName, TableName[ID] )
	)
RETURN
IF(
	Test
	,1
	,0
)

A couple things to note.

1) change the TableName to your actual table name.  Columns in DAX statements should always be referenced as TableName[ColumnName], this is best practice.

2) TRUE() will evaluate to 1 as a decimal, and FALSE() will evaluate to 0.  So I think this formula will work.

Essentially, we're testing to see if the IBFlag is ever true by asking for the MAX.  We're removing the filter context created by using CALCULATE() in a row context by using ALLEXCEPT(), so that the only thing it's checking against is the [ID] column.

if the above formula doesn't work, try this one:

FinalFlag =
VAR Test = 
	CALCULATE(
		MAX( TableName[IBFlag] )
		,ALLEXCEPT( TableName, TableName[ID] )
	)
RETURN
IF(
	Test
	,1
	,0
)

This changes the TRUE() to a 1.

[dphillips]

Max did not want to work with boolean values so I had to create a column which stored the True as 1 and False as 0 then use that field in the calculated column. Thanks for your help. Works well. Am able to filter on the IBFlag and it will keep all values, even those before the IBFlag went from False to True...

[Anonymous]

Ok, use this calculated column then, it will eliminate the need to have a separate column to convert TRUE to 1 and FALSE to 0:

FinalFlag =
VAR Test = 
	CALCULATE(
		VALUES( TableName[IBFlag] )
		,ALLEXCEPT( TableName, TableName[ID] )
	)
RETURN
IF(
	TRUE() IN Test
	,1
	,0
)

VALUES() will produce a table of the distinct values in the [IBFlag] column.  So it will be one of these:

null (0 rows)

True (1 row)

False (1 row)

True, False (2 rows)

Now we check if TRUE() exists in that test table that we created.