Forum Discussion

MBegum's avatar
MBegum
Frequent Visitor
7 years ago

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 staff enter their leaving grade early so I need to use the current YG and if that is blank then I need to use their leaving YG.


Pupil Table

PupilIDForenameSurnameCurrentYG 
Pupil1JohnDoeYR04 
Pupil2JaneDoeYR05 
Pupil3SimonBrown  

 

School History

PupilIDStartDateLeavingDateLeavingYG
Pupil101/09/201601/12/2017YR03
Pupil101/09/2018  
Pupil201/09/2016  
Pupil301/09/201621/07/2018YR02

 

The query below returns the current grade for enrolled student. 

 

Current Grade = CALCULATE(COUNT(Pupil[CurrentYG]),
FILTER(SchoolHistory, SchoolHistory[StartDate] <= TODAY() && PupilSession[LeavingDate] = BLANK() ||
PupilSession[StartDate] <= TODAY() && PupilSession[LeavingDate] >= TODAY()))
 
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()))
 
Any help on sorting out the above query or suggestions on alternative ways of calculating the same thing will be appricated.
 
Thanks in advance 
 
EDIT:  Pupil 1 and 2 are on-roll so I am pick up their current YG (my 1st query does that), Pupil3 has left so I need to pickup their leavingYG which is in a differen table - this is the bit I'm struggling with. I am not sure how to update the original query to work this out.
 
PupilIDForenameSurnameIf on-Roll, CurrentYGif Left, leaving YGExpected YG Output
Pupil1JohnDoeYR04 YR04
Pupil2JaneDoeYR05 YR05
Pupil3SimonBrown YR02YR02
Need Help

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi 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

    • MBegum's avatar
      MBegum
      Frequent Visitor
      7 years ago

      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's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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