Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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]
)
Solved! Go to 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.
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.
Thank you very much. It has worked.
I understood my mistake now.
Glad to help 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
78 | |
62 | |
61 | |
59 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |