Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Willgart
Helper II
Helper II

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.

7 REPLIES 7
Greg_Deckler
Super User
Super User

Here is a similar discussion with a possible solution:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d05f895a-5c13-46ba-9058-6dcae925b898/powerp...

 

M code gives you more flexibility around joins if you used Advanced View and edit the code directly if you can possibly join the tables pre-DAX.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
PowerBIGuy
Responsive Resident
Responsive Resident

@Willgart By default most if not all visuals should suppress null values. Is this for performance reasons if not could you explain the requirement that's driving this question? That may help.

 

Thanks

Business Intelligence Architect / Consultant

right, its first for performance reasons.

but also for functionnal point, as the left join results in NULL values in my case.

 

I have a log table which is like:

Source, LogID, Severity, Timestamp, message (and other columns)

 

and a table which is a list of possible sources

Source, sourcename, sourcedescription

 

I want to display the number of logs by sourcename.

but I have some logs without a related source, the left join results in a NULL value and PowerBI display this in the chart.

the possible source table is not the only one connected to my log table. so having inner joins will solve my problem by returning only good values for the users and also will improve the performance as an inner join provides better results than outer joins.

 

remember that I'm using direct SQL queries.

For now I'm in demo mode, and I'll be able to create custom SQL statements or views if there is no out of the box solution.

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

 

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

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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.

@Willgart Understood, since you are using a direct query your options are limited. Best practice would be to create a default member for your null values in general there shouldn't be any nulls in your data set. for example on your log fact table you should replace your nulls with a default value lets say "Missing Source". In your source table you should have this default value as well  "Missing Source". You can than filter out these values. Also if your users wanted to preform cleanup of this data in the future they could create reports to see how many logs are being created without Sources. Heres some additional intofrormation Link. Hope this helps.

Business Intelligence Architect / Consultant

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.