How can I get a count that involves two tables?

[SamOvermars]

Hello,

I cant find a way to get this to work but it sounds simple.
I have the following tables.

AttendedTable

AbsentTable

AllUsersTable - Which contains the users and the sections they belong to

I have a one-to-one relationship between AbsentTable and AttendedTable using the SectionKey

I want to populate the absent column in the absent table by counting how many users (UserID) minus the number of attended people (Attendee) for each section. This should give me the number of absents from each section. I tried to use count (UserID), but it gave me the overall count in that table. Which is not what I need.
How Can I do that?

[hnguy71 · Accepted solution]

@SamOvermars ,

Try this:

VAR _UserSection = CALCULATE(COUNTROWS(AllUsers), FILTER(AllUsers, AllUsers[UserSection] = [Section]))
VAR _UserAttended = CALCULATE(SUM(Attended[Attendee]), FILTER(Attended, Absent[SectionKey] = Attended[SectionKey]))

RETURN

_UserSection - _UserAttended