Forum Discussion
Direct Query- multiple column relationships?
Our vendor system that we are using as a data source has several tables that use a combination primary key and for relationships to other tables you have to join on 3 or 4 columns (I hate it, I do not know WHY they do it...but it is the way it is).
Is there anyway to support that in Power BI Desktop using Direct Query?
In a Tabular data model I can make a calculated column to achieve this, however... it does not seem possible to do multiple column relationships in the modeling option for Direct Query Mode?
Hi OneWithQuestion,
In Direct Query, you are still able to create a calculated column to combine multiple columns to generate the primary Key.
Col4 = Test5[Col1]&" "&Test5[Col2]
ColC = Test6[ColA]&" "&Test6[ColB]
Best regards,
Yuliana Gu
6 Replies
This is true and can be done, although is not very performant as the query is no longer hitting the actual column predicates for the join, right?
Agree. It is isn't practical to do it with large tables. The database ends up scanning the whole table to find the artificial keys it needs.
Could we address koureass concern on query performance?
UPDATE 07/07/2023 in behalf of my previous post
I have tested this on tables with million of rows using Direct Query, very very bad idea...
Hi OneWithQuestion,
In Direct Query, you are still able to create a calculated column to combine multiple columns to generate the primary Key.
Col4 = Test5[Col1]&" "&Test5[Col2]
ColC = Test6[ColA]&" "&Test6[ColB]
Best regards,
Yuliana Gu
After seven months and no activity on the performance issue that this approach of merge can cause.
