Forum Discussion
Related Not Working in DirectQuery for One to Many Relationship
Hey everybody. I am working on a report which involves showing orders and the users that enter them. They way our data model is configured is:
| Table (Key Column) | Database |
| Orders (Ord_Entry_User_Key) | DB_1 |
| Users (User_Key) | DB_2 |
We are able to create the join between the tables and use fields from the Users table in visuals that have data from Orders without a problem (shown below)
The issue we are hitting is we need to implement conditional logic that uses the _TrueFill calc column from Orders, and the User_ID_No_Domain column. When trying to implement this logic using RELATED, we get the below error.
The assumption is that this will not work because Users and Orders are in different databases.
To try to work around that, we created a view in DB_1 called PBI_Users (same as orders) that queries DB_2 for users.
| Table (Key Column) | Database |
| Orders (Ord_Entry_User_Key) | DB_1 |
| PBI_Users (User_Key) | DB_1 |
We are able to addfields from PBI_Users just like users, but we are still not able to use RELATED.
We also tried merging the query in Power Query and got the below error:
Is this a known issue and by design? It seems very strange that you can use the fields together if they are in different databases but in the same data model, but unable to use Related. Even stranger that even though the view is in the same database as Orders, it does not work.
Thanks all.
Hi Anonymous ,
It seems Limited relationships between table1 and table2. Table expansion never occurs for limited relationships. So, you can not use RELATED().
For more details, you can refer these links.
Model relationships in Power BI Desktop - Power BI | Microsoft Docs
RELATED function (DAX) - DAX | Microsoft Docs
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , I was able to use related for a new column. example https://www.youtube.com/watch?v=aA4ROG9dg8A
is it a column of measure? In case of measure, you need use some aggregation
amitchandak I appreciate it, but your video does not help. My question is related to DirectQuery across two different databases, and your video is all from the same database with the exception of the import table.
My issue is: Table 1 is in DB_1. Table 2 is in DB_2. Both are Direct Query. I cannot use RELATED because they are in different DBs (same server). Even if I create a view of Table 2 in DB_1, it will not work.
Hi Anonymous ,
It seems Limited relationships between table1 and table2. Table expansion never occurs for limited relationships. So, you can not use RELATED().
For more details, you can refer these links.
Model relationships in Power BI Desktop - Power BI | Microsoft Docs
RELATED function (DAX) - DAX | Microsoft Docs
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I see this issue as well. I have data in the PBIX dataset and then I am trying to use Reference data that is in a DataFlow, and trying to use direct query so that I can update the reference and the report will show new info (hopefully!).
Related did not work, despite being able to make the relationship.
LookUpValue seems to be working.
