Attendance report - If is blank lookup?

[echobase]

Greetings and thank you in advance for any help with this!

I am having trouble coming up with a solution to the creation of an attendance status report.  The goal is to see which students did or did not attend class on any  given day.  I have two tables that I am working with.  Table 1 is the student schedule, which contains the days each student is scheduled to attend.

 

Table 2 is the student attendance, which contains the days each student was present and account for in school.  

The result I would like is a new column or a new table that shows the attendance

I was trying If(ISBLANK(LOOKUPVALUE, however, I have not figured out how to incorporate looking at each date.  Does that anyone have some thoughts on this?  Thanks again in advance for any help with this!!

[bcdobbs]

If your attendance table is large I'd move the logic into Power Query.

Merge the two tables on StudentId and Date. Expand the result and replace not nulls with 1 and nulls with 0.

I'd also then reference your original schedule table and create a student dimension by removing all columns apart from name and student id. Then use remove duplicates.

I'll send a demo in morning.

[VahidDM · Accepted solution]

Hi @echobase 

Try this measure:

Attended = 
VAR _A =
    FILTER (
        'student attendance',
        'student attendance'[StudentID] = MAX ( 'student schedule'[StudentId] )
            && 'student attendance'[AttendedDate] = MAX ( 'student schedule'[ScheduleDate] )
    )
RETURN
    IF ( COUNTROWS ( _A ) = 0, "No", "Yes" )

Output:

sample file attached.

If you want a calculated column, try this in Student Schedule table:

Attended Column = 
VAR _A =
    FILTER (
        'student attendance',
        'student attendance'[StudentID] = EARLIER(  'student schedule'[StudentId] )
            && 'student attendance'[AttendedDate] = EARLIER( 'student schedule'[ScheduleDate] )
    )
RETURN
    IF ( COUNTROWS ( _A ) = 0, "No", "Yes" )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[echobase]

Wonderful!  Thank you for taking the time and providing a detailed reponse and the .pbix example.  Very useful and appreciated.  I used the example file and was able to reproduce the same in the actual report.  It was great to have the example file as I could confirm things like the relationships and the auto date time setting to have a true Apples to Apples comparison.   I was able to make some variations as well, thanks to your work!  Thanks again!

[jdbuchanan71]

@echobase 

You can add a column to your schedule table that looks for the StudentID and Date in question like this.

Attended =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Atendance[StudentID],
            Atendance[StudentID], Schedule[StudentID],
            Atendance[AttendDate], Schedule[ScheduleDate]
        )
    ),
    "No",
    "Yes"
)

The column we lookup against doesn't matter really, it just checking if there is a row for that student / date in the attendance table.