Forum Discussion
Combine two tables
- 2 years ago
VAR CombinedTable = VAR SelectedSalesOrders = SELECTCOLUMNS( FILTER( 'SalesOrders', 'SalesOrders'[Date ordered] >= DATE(YEAR(TODAY()), 1, 1) && 'SalesOrders'[SO Line type] <> "6" ), "Company&SalesOrder&StockCode", 'SalesOrders'[Company] & 'SalesOrders'[Sales Order] & 'SalesOrders'[Stock Code] ) VAR WIPjobsColumns = SELECTCOLUMNS( WIPJobs, "Jobs", 'WIPJobs'[Job], "Company&SalesOrder&StockCode", 'WIPJobs'[Company] & 'WIPJobs'[Sales Order] & 'WIPJobs'[Stock Code] ) VAR LeftJoin1 = NATURALLEFTOUTERJOIN(SelectedSalesOrders, WIPjobsColumns) RETURN DISTINCT ( SELECTCOLUMNS(LeftJoin1, "Jobs", [Jobs]) ) // Or if you want to summarize based on Jobs and perform aggregation, you can do something like this: // SUMMARIZE(LeftJoin1, [Jobs], "SomeAggregatedValue", SUM('WIPJobs'[SomeColumnForAggregation])) RETURN CombinedTableYou need to perform the join and selection operations as you did in table1 and then immediately summarize the resulting table in the same query. However, since SUMMARIZE is typically used for grouping data and performing aggregations, and you seem to want to list Jobs uniquely, you might consider using DISTINCT if you're just listing distinct Jobs values. If you need additional aggregation, you can include those in the SUMMARIZE.
VAR CombinedTable =
VAR SelectedSalesOrders =
SELECTCOLUMNS(
FILTER(
'SalesOrders',
'SalesOrders'[Date ordered] >= DATE(YEAR(TODAY()), 1, 1) &&
'SalesOrders'[SO Line type] <> "6"
),
"Company&SalesOrder&StockCode", 'SalesOrders'[Company] & 'SalesOrders'[Sales Order] & 'SalesOrders'[Stock Code]
)
VAR WIPjobsColumns =
SELECTCOLUMNS(
WIPJobs,
"Jobs", 'WIPJobs'[Job],
"Company&SalesOrder&StockCode", 'WIPJobs'[Company] & 'WIPJobs'[Sales Order] & 'WIPJobs'[Stock Code]
)
VAR LeftJoin1 =
NATURALLEFTOUTERJOIN(SelectedSalesOrders, WIPjobsColumns)
RETURN
DISTINCT ( SELECTCOLUMNS(LeftJoin1, "Jobs", [Jobs]) )
// Or if you want to summarize based on Jobs and perform aggregation, you can do something like this:
// SUMMARIZE(LeftJoin1, [Jobs], "SomeAggregatedValue", SUM('WIPJobs'[SomeColumnForAggregation]))
RETURN
CombinedTableYou need to perform the join and selection operations as you did in table1 and then immediately summarize the resulting table in the same query. However, since SUMMARIZE is typically used for grouping data and performing aggregations, and you seem to want to list Jobs uniquely, you might consider using DISTINCT if you're just listing distinct Jobs values. If you need additional aggregation, you can include those in the SUMMARIZE.