Forum Discussion

dphillips's avatar
dphillips
Icon for Helper IV rankHelper IV
8 years ago
Solved

Fill a column based on a value in another column

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. Wha...
Need Help
  • dphillips's avatar
    dphillips
    8 years ago

    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.

dphillips's avatar
dphillips
Icon for Helper IV rankHelper IV
8 years ago

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's avatar
Seward12533
Icon for Solution Sage rankSolution Sage
8 years ago
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's avatar
    dphillips
    Icon for Helper IV rankHelper IV
    8 years ago

    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.