Related Not Working in DirectQuery for One to Many Relationship

[ajcooper35]

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.

[amitchandak]

@ajcooper35 , 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

[ajcooper35]

@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.

[v-chenwuz-msft · Accepted solution]

Hi @ajcooper35 ,

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.