How to join two tables in direct query

[Anonymous]

Hello,

I have two tables: The first tables shows my orders and products which were ordered with a quantity and the second table shows all my available products without quantity field. 

So I want to calculate based on a specific date which products were ordered and which not. 

Problem: How can I merge a table with quantity and the table without quantity. All products without quantity should get the value zero.

I can do that with an join using import modus but how to do the merge in direct query? Just need some ideas 🙂

Thank you in advance. Best regards.

[amitchandak]

I am assuming product here is product dimension, Same way you have date dimension,

When you create sum of qty measure add +0

Qty Total = Sum(Table[Qty]) +0

Also, explore this option

[Greg_Deckler]

You can't merge in Direct Query, you would need to edit the source system.

You could theoretically merge them in DAX using things like NATURALINNERJOIN, etc. but that will be part of a measure calculation.

[Anonymous]

@Greg_Deckler  Thank you for your fast reply. So I tried to do a join but I get the error message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."  On the picture I tried to describe what I want to create but is this even possible since the tables have different columns?

[Ashish_Mathur]

Hi,

Try this

1. Create a relationship from the SKU field of Table1 to the SKU field of the Products Table
2. To your visual, drag SKU and Product from the Products Table
3. Write this measure: Total Quantity = SUM(Table1[Quantity])+0

[Greg_Deckler · Accepted solution]

Try this:

VAR __Table =
NATURALINNERJOIN(
'Products',
SELECTCOLUMNS(
'Orders',
"SKU",[SKU],
"Quantity",[Quantity]
)
)

[camargos88]

Hi @Anonymous ,

Can't you just submit your sql with this join ?

Ricardo