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

7 Replies

  • PowerBIGuy's avatar
    PowerBIGuy
    Icon for Responsive Resident rankResponsive Resident
    10 years ago

    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

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

      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.

      • PowerBIGuy's avatar
        PowerBIGuy
        Icon for Responsive Resident rankResponsive Resident
        10 years ago

        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.