Forum Discussion
Helper IIhow to force inner joins in direct query SQLs?
Helper IIright, 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.
Community ChampionNewest, 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.
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
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.
itchyeyeballsImpactful Individual
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.