Forum Discussion
Expand count() to allow if statement to return data from different table
Hi, I'm trying to work out the year grade for enrolled students. We have two tables, one where we store the pupil data and another storing their school history, sometimes for future leavers the ...
v-piga-msft
Resident Rockstar
Resident RockstarHi MBegum ,
I've tried to expand the COUNT() part with an IF statement but that just gives me an error so not sure what I'm doing wrong.Current Grade= CALCULATE(COUNT(if(Pupil[CurrentYG]=Blank(), RELATED(SchoolHistory[LeavingYG]),FILTER(SchoolHistory, SchoolHistory[StartDate] <= TODAY() && PupilSession[LeavingDate] = BLANK() ||PupilSession[StartDate] <= TODAY() && PupilSession[LeavingDate] >= TODAY()))
Based on your formula, it seems that the syntax of this measure is incorrect.
I'm afraid that we cannot use IF function like that. You should use IF function outside of calculate function, maybe like below.
Current Grade =
IF (
Pupil[CurrentYG] = BLANK (),
CALCULATE (
COUNT ( Pupil[CurrentYG] ),
FILTER (
SchoolHistory,
SchoolHistory[StartDate] <= TODAY ()
&& PupilSession[LeavingDate] = BLANK ()
|| PupilSession[StartDate] <= TODAY ()
&& PupilSession[LeavingDate] >= TODAY ()
)
)
)
If you still need help, please share your desired output so that we can help further on it.
Best Regards,
Cherry
Thanks for your feedback. I tried your suggestion but couldn't get it working. I've added an edited section with my expected output.
- v-piga-msft
Hi MBegum ,
Sorry for the delay.
However, what is the PupilSession[LeavingDate]? It seems that you have the third table PupilSession?
If it is convenient, could you also share the sample data of the table PupilSession?
Best Regards,
Cherry