Forum Discussion

Willgart's avatar
Willgart
Icon for Helper II rankHelper II
10 years ago

how to force inner joins in direct query SQLs?

Hi,   is it possible to force Power BI to create inner join queries instead of left outer join ones? I'm using SQL server with direct queries.   thanks.
Need Help
Show and Tell
Tips and Tricks
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Newest, November, version of Power BI Desktop allows you to pick your join type when doing a Merge query, and you can merge all of the queries into a single query. When doing a Merge query step, you have the options of:

  • Left Outer
  • Right Outer
  • Full Outer
  • inner
  • Left Anti
  • Right Anti

Here is a link to how to merge them together in a single query.

http://community.powerbi.com/t5/Desktop/Creating-a-relationship-between-two-queries-Dynamics-CRM-2015/m-p/10811/highlight/false#M2300

 

When you say "direct query" are you referring to DirectQuery for SSAS?

https://msdn.microsoft.com/en-us/library/hh230898.aspx?f=255&MSPPError=-2147217396

 

 

 

Willgart's avatar
Willgart
Icon for Helper II rankHelper II
10 years ago

Its a direct query to SQL Server, not SSAS.

my Power BI version is:  2.29.4217.221

Power BI didnt detect any new version... so not sure if its the latest or not.

I dont see any option to play with the links between the tables (except the cardinality option)

 

I think the merge query doesnt works with direct queries. there is limitation in this mode.

  • itchyeyeballs's avatar
    itchyeyeballs
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    The different join options in the latest version only apply to merging queries during the import stage, not in the model itself (I think).

     

    I would go with views in the database if thats a workable option, gives the most control and also means you can share a .pbix file with multiple users and still have the ability to just change code in one place if you want to modify later on.