Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
"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"