Left Outer Join in Direct Query report

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.

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.