Need Help - Referencing columns in a different table using Direct Query.

[Anonymous]

Hi,

I have two tables, a student table and a family table. 

The student table is as follows, where Student ID is the primary Key.

Student ID	FamilyID
1	F001
2	F002
3	F003
4	F004
5	F001

The Family table is as follows, where many family members may belong to the same family ID..

FamilyID	Member	Phone
F001	Dad	016-456-89876
F001	Mom
F001	Uncle	676-876-98543
F002	Dad
F002	Mom	412-562-88765
F003	Dad	416-542-86758
F004	Dad	416-592-86988

I need to find the list of pupils for whom any of the family member's phone no is missing. I'm using a direct query which makes creating Calculated columns and using calculate function difficult. 

I need a final output of something similar to 

Student ID	column
1	1
2	1
3	0
4	0
5	1

Can anyone help me with this?

Thanks in advance.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

It is not recommended to create columns due to DQ mode. so you could create a measure or create a new table.

Method 1: create a measure.

count =
CALCULATE (
    COUNT ( family[Member] ),
    FILTER ( ALL ( family ), [FamilyID] = MAX ( 'student'[FamilyID] ) ))
    - CALCULATE ( COUNTROWS ( 'student' ), ALLEXCEPT ( student, student[FamilyID] ) )

The final output is shown below:

Method 2: create a new table

Newtale =
SUMMARIZE (
    'student',
    [StudentID],
    "value",
        CALCULATE (
            COUNT ( family[Member] ),
            FILTER ( ALL ( family ), [FamilyID] = MAX ( 'student'[FamilyID] ) ))
            - CALCULATE ( COUNTROWS ( 'student' ), ALLEXCEPT ( student, student[FamilyID] ) ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"I'm using a direct query which makes creating Calculated columns and using calculate function difficult" - Not any more. The Composite Model has added a lot more flexibility to this, by allowing you to create a local copy of the Direct Query data model.

Anyway, you can do all this in the Power BI UI directly

Make sure your data model is correct

Then grab the Student ID from the Student table and the Phone Number from the Family table. 

Add a filter to only include empty phone numbers

Set the student to "show items with no data"

Set the Phone number column aggregation to "Count"