Left Outer Join in Direct Query report

[Anonymous]

Hi All

I have situation where 2 tables are joined with each other with say Column1. Now I wanted to have left outer join so that all data from Table 1 reflect and only common values of Column1 reflect from Table 2.

Please note this is DirectQuery report so can not use merge queries option.

tried using NATURALLEFTOUTERJOIN as below:

RequiredComplTime = NATURALLEFTOUTERJOIN(Orders,ReqCartComplteTime)

But it failed with error below 

No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.

Any thoughts please?

Thanks

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Very likely that it is your many-to-many that is causing the issue. If you read the documentation on the DAX join functions, they call out one-to-many relationships at the end of the documentation.

Please also note that:

• Columns being joined on must have the same data type in both tables.

• Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].

• Strict comparison semantics are used during join. There is no type coercion; for example, 1 does not equal 1.0.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Very likely that it is your many-to-many that is causing the issue. If you read the documentation on the DAX join functions, they call out one-to-many relationships at the end of the documentation.

Please also note that:

• Columns being joined on must have the same data type in both tables.

• Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].

• Strict comparison semantics are used during join. There is no type coercion; for example, 1 does not equal 1.0.

[edhans]

Hi @Anonymous - you absolutely can do a merge in Power Query. This is a Direct Query to the Contoso DW database. The SQL generated is:

select [$Outer].[OnlineSalesKey],
    [$Outer].[StoreKey],
    [$Outer].[ProductKey],
    [$Outer].[PromotionKey],
    [$Inner].[Store Manager]
from [DaxBook].[Sales] as [$Outer]
left outer join 
(
    select [_].[StoreKey] as [StoreKey2],
        [_].[GeographyKey] as [GeographyKey],
        [_].[Store Manager] as [Store Manager],
        [_].[Store Type] as [Store Type],
        [_].[Store Name] as [Store Name],
        [_].[Status] as [Status],
        [_].[Open Date] as [Open Date],
        [_].[Close Date] as [Close Date],
        [_].[Zip Code] as [Zip Code],
        [_].[Zip Code Extension] as [Zip Code Extension],
        [_].[Store Phone] as [Store Phone],
        [_].[StoreFax] as [StoreFax],
        [_].[Continent] as [Continent],
        [_].[City] as [City],
        [_].[State] as [State],
        [_].[CountryRegion] as [CountryRegion],
        [_].[Address Line 1] as [Address Line 1],
        [_].[Address Line 2] as [Address Line 2],
        [_].[Close Reason] as [Close Reason],
        [_].[Employees] as [Employees],
        [_].[Selling Area] as [Selling Area],
        [_].[Last Remodel Date] as [Last Remodel Date]
    from [DaxBook].[Store] as [_]
) as [$Inner] on ([$Outer].[StoreKey] = [$Inner].[StoreKey2])

You can see the last section does the join. The Merge dialog looks like this:

Definitely do the merges in Power Query. Please post back with specific issues you face.

[Anonymous]

Hi you can NOT do merge in deirect query report, as 2 queries are sourced from different databases

[edhans · Accepted solution]

@Anonymous this is not correct. I do merges all of the time. If the tables are from different databases, you have to make a tweak to the server itself. You can create a View in DB1 to the table in DB2, then use Power Query to access both in DB1, and merges work fine, or you can create a synonym. See this StackOverflow article. Addresses both issues. Both will preserve foldling and direct query.