Join not possible with direct query?

[joglidden2]

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. 

[v-eqin-msft · Accepted solution]

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 Qin

[joglidden2 · Accepted solution]

@v-eqin-msft , 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. 

[v-eqin-msft]

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

[v-eqin-msft · Accepted solution]

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 Qin

[joglidden2]

@v-eqin-msft , 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'. 

[v-eqin-msft]

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]

Like this should work, no?

[amitchandak]

@joglidden2 , you should filter for that .

calculate([measure], filter(Table2, Table2[col] = "ABC"))

Can you let us know exact need

[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 , 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]

And BTW, it fails on the many-side too: