Forum Discussion

SamOvermars's avatar
SamOvermars
Helper I
3 years ago
Solved

How can I get a count that involves two tables?

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?

Need Help
  • hnguy71's avatar
    hnguy71
    3 years ago

    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

1 Reply

  • hnguy71's avatar
    hnguy71
    Super User
    3 years ago

    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