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?

[v-angzheng-msft]

Hi, @Anonymous 

In fact, there are some limitations in calculating columns in direct query mode.

• Limitations in calculated columns: 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. Additionally, the DAX scalar functions, such as LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#implications-of-using-directquery

You can change the storage mode to Import. Go to the Model tab and select the target table, change the storage mode to Import in Advanced under Properties, and then create a new column.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.