Forum Discussion
Post PatronJoin not possible with direct query?
I have two sql server data sources that are direct query. I'm attempting to use naturalinnerjoin(), and I get the following message:
But this is not true!
And, indeed, I'm attempting to naturalinnerjoin() on the 1 side of 1-to-many. This should work!
Is it because PBI simply will not support joins on direct query data?
Any help or suggestions would be appreciated.
Hi joglidden2 ,
Try to use RELATEDTABLE() function after building one-many relationship like this:
Measure 3 = COUNTX ( RELATEDTABLE ( 'SEM' ), 'SEM'[Computer_Name] )Or change the storage mode from DirectQuery to Import.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn QinAnonymous , your solution was adequate, and it works. But I'll tell you that I just simply gave up on direct query. Too troublesome. I've found that doing an import, then eliminating duplicates (to keep PBI from jack-knifing) is the most reliable solution. Then, I just do the "mashup" of data sources in a data gateway and schedule a refresh. I don't know if this is the best solution, but it's the most "working" solution.
10 Replies
- joglidden2
And BTW, it fails on the many-side too:
- amitchandak
Super User
joglidden2 , Why you need natural join when the tables are already joined. Column name should be same in case natural join
refer : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
- joglidden2
Good question. Because I need to be able to create a ratio of two measures. One measure is filtered based on a column in the other table. I don't know how to create a ratio otherwise.
- amitchandak
joglidden2 , you should filter for that .
calculate([measure], filter(Table2, Table2[col] = "ABC"))
Can you let us know exact need
- joglidden2
Like this should work, no?
Hi joglidden2 ,
In my test, NATURALINNERJOIN function works fine in DirectQuery mode as well when the two tables are from the same source table.
But when there is no relationship between the two tables, or they are from different databases , the same error is reported. Please check.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qin- joglidden2
Anonymous , thanks for looking in to this for me. So basically, if the two data sources are from different databases, then joining is not possible, no matter the cardinality. This is good to know.
I wonder if the only way to do this is through 'Direct Import'.
Hi joglidden2 ,
Try to use RELATEDTABLE() function after building one-many relationship like this:
Measure 3 = COUNTX ( RELATEDTABLE ( 'SEM' ), 'SEM'[Computer_Name] )Or change the storage mode from DirectQuery to Import.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn QinHi joglidden2 ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.Best Regards,
Eyelyn Qin- joglidden2
Anonymous , your solution was adequate, and it works. But I'll tell you that I just simply gave up on direct query. Too troublesome. I've found that doing an import, then eliminating duplicates (to keep PBI from jack-knifing) is the most reliable solution. Then, I just do the "mashup" of data sources in a data gateway and schedule a refresh. I don't know if this is the best solution, but it's the most "working" solution.
