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.

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.