PowerBI - Sum of rows of one column based on criteria

Question

I have what I think is pretty simple requirement - but having trouble implementing. Basically I have two tables - with a 1:M relationship on the Name field

Log-Ins Table

ID	Name	Date
login1	Evan	March
login2	Evan	March
login3	Ryan	March
login4	Ryan	March
login5	Jack	March
login6	Mike	March
login7	Mike	April
login8	Mike	April
login9	Mike	April
login10	Evan	April
login11	Evan	April

Contact Table

Name	FamilyMembers
Evan	3
Ryan	2
Mike	4
Jack	1

I want a report that totals the family members based on a log in - but I only want to count them once - using the Date as a slicer. I created a measure called LoginCount which gives me a view that looks like this:

LoginCount = COUNTROWS(RELATEDTABLE('Log-Ins'))

What I'm looking for is the total of FamilyMembers (which when slicer is set to March should be 10)

Name	LoginCount	FamilyMembers
Evan	2	3
Jack	1	1
Mike	1	4
Ryan	2	2
	6

and in April should be 7.

Closest I can get is to summarize the FamilyMembers Column - but it gives the total for each log-in (Evan h as 2 logins and 3 family members, which equals six) which I don't want. The idea is to get a general headcount of serviced users for the month and not re-count the family members each time.

Angelo Canepa Angelo Canepa · Accepted Answer · 2021-04-10T08:23:41

The problem seems to rely on how the filters are propagating from one table to another. If you apply CROSSFILTER you can solve this issue without changing the type of your relationship.

In others words, Log-Ins will filter the Contacts table.

Calculation: Measure

Total Family Members =
CALCULATE (
    SUM ( Contacts[FamilyMembers] ),
    CROSSFILTER ( Contacts[Name], 'Log-Ins'[Name], BOTH )
)

PowerBI - Sum of rows of one column based on criteria

1 Answers

Calculation: Measure

Output

Relationship