count rows in a table based upon another table and field

[AdenJones]

I am from a programming background and mostly familiar with SQL for developing reports. I am very keen to learn PowerBI but have come across something I haven't found a clear answer to.

Basically I have the following:

Table1

Participant ID	Participant Name
12	Bob
24	Gretel

Table2

Participant ID	Family ID
12	2
24	2

Based upon the Participant ID in Table1 I want to count all the Family IDs that are the same. So if bob has participant ID 12 in table1 then I want to count all rows in Table2 with the family id of Bob;s 12 Participant ID therefore it would count both rows in Table2 because both rows have the same participant id. Any help would be greatly appreciated.

[v-shex-msft · Accepted solution]

Hi @AdenJones,

You can try to use below formula if it works for your scenario.

Calculate column to Table1:

Count From Another Table =
VAR familyList =
    CALCULATE (
        VALUES ( Table2[Family ID] ),
        FILTER (
            ALL ( Table2 ),
            Table2[Participant ID] = EARLIER ( Table1[Participant ID] )
        )
    )
RETURN
    COUNTROWS ( FILTER ( ALL ( Table2 ), [Family ID] IN familyList ) )

Regards,

Xiaoxin Sheng