Calculate Function not working for referencing columns from different table using Direct Query

[Anonymous]

Hi,

I have created the following calculated column in a PBI report using import query.
Column = CALCULATE(COUNTBLANK(Phone), FILTER(Family, Student[FamilyId] = Family[FamilyId])

where as the tables are as follows 

Student ID	FamilyID
1	F1
2	F2
3	F1
4	F3
5	F2

FamilyID	Member	Phone
F1	Dad	016-456-89876
F1	Mom
F1	Uncle	676-876-98543
F2	Dad
F2	Mom	412-562-88765
F3	Dad	416-542-86758

So Ideally, the output column would be 

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

But, Now I have to create this report using DirectQuery connection. But when I try the same query, I'm not able to use the Calculate functions in the calculated column.

Can you pls help me with a solution for this issue?

[Ashish_Mathur]

Hi,

Download my PBI file from here.  I have used measures only.

Hope this helps.

[AlexisOlson]

Direct Query has modeling limitations. See this documentation.

In particular, "Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions." So, you definitely can't refer to another table in a calculated column using DirectQuery.

Not all hope is lost though. You can often achieve the same goal using measures instead of calculated columns. It's not clear what your purpose for this column is though so it's hard to suggest a workaround.

[Anonymous]

Hi Alexis,

Thank you for helping me out.. But I'm not sure whether measures will help me with my current issues.

I have two tables.. a student table and a familymember table.

The student table has the details of individual students like StudentID, Name, year, class, FamilyID etc., where StudentID is the Primary key.

Student ID	FamilyID
1	F1
2	F2
3	F3
4	F4
5	F1

In the Family table, the details of the family members of the students will be stored such as FamilyID, Family member name, relation, Phone No & email ID etc. But the Family ID is not a primary key as multiple family members belonging to the same family will be tagged to the same ID.

FamilyID	Member	Phone
F1	Dad	016-456-89876
F1	Mom
F1	Uncle	676-876-98543
F2	Dad
F2	Mom	412-562-88765
F3	Dad	416-542-86758
F4	Dad	414-546-88678

I needed to find the no of students for whom any of the family member's Phone no was missing. In the above example, Students 1 &5 belonging to same family F1 have Mom's Phone no missing and Student 2 belonging to family F2 has Dad's phone no missing.

In the import mode, I created a calculated column to identify the Pupils for whom the family member's were missing phone no information using the following query, 

Column = CALCULATE(COUNTBLANK(Phone), FILTER(Family, Student[FamilyId] = Family[FamilyId]))

which would give me the following calculated column in the student table,

Student ID	FamilyID	Column
1	F1	1
2	F2	1
3	F3
4	F4
5	F1	1

Then i used a measure to count the no of pupils having column values using a COUNT function on 'Column' which would give me the result 3.

But now, I need to change the connection to DirectQuery because of which, the calculated column could not be created and I'm stuck.

Could you suggest me with an alternative workaround?

Pls let me know if you need any additional information.