Fill a column based on a value in another column

[dphillips]

I have a column called StudentIBFlag which is a Boolean and a column called YearSem which gives me a year and a semester. I want to fill a column with the most up to date values for each student. Whatever the value of StudentIBFlag is in the most current YearSem, I want that to populate all rows for that student in the table. I can find the most recent YearSem in the table just by doing LastYearSem = Max([YearSem]). This gives me a value in this case of 2018 (T3) as shown below. How can I then look at the StudentIBFlag for a student in that YearSem and populate all rows for that student with the value at that time?

For example - for student 113798 his student IBFlag is True for YearSem = 2018 (T3) so the IBStatus column is set to true for all rows with his ID in the table. The next student, 125444, is False for 2018 (T3) so all his rows are set to false. What is the best way of doing this? 

Would love to provide the data but not sure how to attach the data to this post. Thanks for any help.

[Seward12533]

Yes Data will help us help you - to sharevdata or ideally a PBIX file with your attempt at a solution in addtion to your desired results.  To share upload to file sharing site like drop box, google drive, one drive etc... and then share, get a link and paste it here. 

TIP you will find it easier to represent the the LAST FILE YEAR and SEMESTER as Dates and have the SEMESTER (Spring, Fall, T3, T2 or whatever as columns in a date table that its linked to. 

[dphillips]

Basically, as a student moves through school, he may change IB status in Years 11 and 12. If I filter on the IB status = True then it removes all of his results from the junior years when he wasn't in IB. I want some way of saying, if he is now in the IB, set the IB to true for all the years he was at school so I can get all his results, even those in junior years. 

Here is the link to my data. I want a formula that duplicates the last column - IB status

Thanks again for any help

[v-yuta-msft]

Hi dphillips,

Create a calculate column using DAX as below:

lBStatus_ = 
VAR Most_Current_Year_Sem = MAX(Sheet1[Year Sem])
RETURN
CALCULATE(VALUES(Sheet1[IBStatus]), FILTER(ALLEXCEPT(Sheet1, Sheet1[StudentID]), Sheet1[Year Sem] = Most_Current_Year_Sem))

 

Regards,

Jimmy Tao

[dphillips]

Thanks so much for your answer. I implemented the calculation and it works for a current student but I have run into a problem I didn't think about. What if a student left the school prior to the Max(Year Sem) currently in the database? For example, if Max(Year Sem) returns 2018 (T3) but a student left in 2017 (T3). In this case it returns a blank. What I really want is for the calculation to return the Max(Year Sem) filtered by each student. So if a student left in 2017 (T3) that would be returned for him as Max(Year Sem) and for another student who left in 2016 (T3), that would be returned as the Max.

Clearly I have to filter the first part which stores the Max value in Most_Current_Year_Sem but not sure how to go about that. Are you able to help me work that out?

Thanks

[Seward12533]

It’s about filter context. When used in a calculated column The following only works in the context of the current row.

VAR Most_Current_Year_Sem = MAX(Sheet1[Year Sem])
RETURN

Try replacing it with this

VAR Most_Current_Year_Sem = CALCULATE(MAX(Sheet1[Year Sem]),ALL(sheet1)) RETURN

This removes the implied filter context.

[dphillips · Accepted solution]

Found a sloution to my problem which seems to work okay.

HSCIBStatus = VAR Most_Current_Year_Sem = CALCULATE(MAX(uNCPBIRepResultsAll[YearSem]), ALLEXCEPT(uNCPBIRepResultsAll,uNCPBIRepResultsAll[StudentID]))

RETURN CALCULATE(VALUES(uNCPBIRepResultsAll[StudentIBFlag]), FILTER(ALLEXCEPT(uNCPBIRepResultsAll, uNCPBIRepResultsAll[StudentID]), uNCPBIRepResultsAll[YearSem] = Most_Current_Year_Sem))

Thanks for all the help.

[Seward12533]

Looking at your data its clear your trying apply an excel paradigm and actually build the tables with the data rather than let PowerBI filter data and build visuals. And your trying to take an output of an excel worksheet or pivot table that is derived and build a visual in powerBI. For example you have Last Semster and IB Status as value on each row. The last semsester can easily be calculated from the data) and is not needed and ideally you would have the course data in one table and the IB status in a lookup table with student, Year Semester, and IB Status. If your source data actually had this on every row for every class you woudl have to maintian it in multiple places. 

I was able to get an IB status in a calculated column in your table but I also proposed an example of a DAX solution that does not require it. 

See this version of your file 

I solved your IB Status column

• First I added a column for [Year SEM Number] this allows specifying a sort order for [Year Sem] and calculating the Last Year Semester using Last Year Semester = CALCULATE(MAX(Sheet1[Year Sem Number]),ALL(Sheet1)) this could have been skipped
• Created a few measures

Student Count = DISTINCTCOUNT(Sheet1[StudentID])
Students with IB in Last Semsester = CALCULATE([Student Count],Sheet1[StudentIBFlag],FILTER(Sheet1,Sheet1[Year Sem Number]=[Last Year Semester]))

• Built a dyamic table using DAX that lists the students and if they had IB Flag in the last semester or not

StudentIDs = VALUES(Sheet1[StudentID])

I then added a column with the measure Students with IB in last semester 
I could have done this with a single DAX line

StudentIDs = ADDCOLUMNS(VALUES(Sheet1[StudentID]),"IB In Last Semster",[Students with IB In Last Semster])

• Linked this StudentIDs table the Fact Table (Sheet1)
• Added calculated column IBStatus = RELATED(StudentIDs[IB in Last Semester])

I also included a demonstration where I built a different table with DAX to show the Students and the semesters that they had IB flag set to true. And then built a Matrix to diplay this table. Then a Matrix that shows the courses in each semester.  I wrote a single DAX Measure to that calculates if any row in the table should be displayed or not. 

Display Flag = CALCULATE(IF(HASONEVALUE('StudentIDs Semesters with IB'[StudentID]),1),ALL(Sheet1[ClassCode],Sheet1[Year Sem]))

I then filtered the second Matix to only display where the Display flag is true.  This has the effect that if you select any single student  in the first matrix then the second matrix displays all the cources that student took regardless of IB Flag. If No Students or mor than one are selected the table is empty. Try clicking on the student in table 1 and they all will appear. 

Both of these solutions will dynamically adjust to the data.  I didn't elmimnat the IB Status flag in the base table to demonstrate using a lookup table of just the Student, Year Semester and IB Flag.

I know its alot but hopefully it gives you both a solution to satisfy your current approach but also gives you a perspective of how the paradigm of PowerBI is different and how much eaiser it is when you let PowerBI do its magic and how much easier it can be versus the way we had to do it in excel before Power Pivot or PowerBI.