Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi,
Download my PBI file from here. I have used measures only.
Hope this helps.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |