cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamOvermars
Helper I
Helper I

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

SamOvermars_0-1665096176455.png

 

 

AbsentTable

SamOvermars_1-1665096214202.png

 

 


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

SamOvermars_2-1665096265640.png

 

 

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?

1 ACCEPTED SOLUTION
hnguy71
Super User
Super User

@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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

1 REPLY 1
hnguy71
Super User
Super User

@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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.