Combine two tables

[OlegV]

Hello,

I have created two calculated tables. 

But I need to have only one.

Could you please tell me how can I combine them in one query?

I tried to use summarize, but it cannot see fileds of the variable.

Here are the codes:

table1 =

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 ( LeftJoin1)

Table 2 =

SUMMARIZE(

    'table1',

    'table1'[Jobs]

)

[AmiraBedh · Accepted solution]

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
  CombinedTable

You 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. 

[OlegV]

Thank you very much. It has worked.

I understood my mistake now.

[AmiraBedh]

Glad to help 🙂