Forum Discussion
How to join two tables in direct query
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...
- Try this:
VAR __Table =
NATURALINNERJOIN(
'Products',
SELECTCOLUMNS(
'Orders',
"SKU",[SKU],
"Quantity",[Quantity]
)
)
Greg_Deckler
Community Champion
Community ChampionYou 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.
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?
- Greg_DecklerTry this:
VAR __Table =
NATURALINNERJOIN(
'Products',
SELECTCOLUMNS(
'Orders',
"SKU",[SKU],
"Quantity",[Quantity]
)
) - Ashish_Mathur
Super User
Hi,
Try this
- Create a relationship from the SKU field of Table1 to the SKU field of the Products Table
- To your visual, drag SKU and Product from the Products Table
- Write this measure: Total Quantity = SUM(Table1[Quantity])+0
