change column name for one of the inputs of a table created by Union Summarize

[dapperscavenger]

I have created a table from 4 other tables using union and summarize.  In this table the Site is where shipments are coming from, and the Region is where the orders are going to.

DDFlows = UNION(
        SUMMARIZECOLUMNS (
    'Table1'[Order],
    'Table3'[Site],
    'Table1'[SITE_NAME],
    'Table4'[REGION],
    'Table3'[Movement Type],
    'Table1'[PROCESS_TYPE],
    'Table2'[SHIPPED_QUANTITY],
    FILTER ( 'Table3', 'Table3'[Movement Type] = "Direct Sites"),
    FILTER ( 'Table1', 'Table1'[PROCESS_TYPE] = "demand")
),
        SUMMARIZECOLUMNS (
    'Table1'[Order],
    'Table3'[Site],
    'Table1'[SITE_NAME],
    'Table4'[REGION],
    'Table3'[Movement Type],
    'Table1'[PROCESS_TYPE],
    'Table2'[SHIPPED_QUANTITY],
    FILTER ( 'Table3', 'Table3'[Movement Type] = "Direct Sites"),
    FILTER ( 'Table1', 'Table1'[PROCESS_TYPE] = "supply")
)
)

However, in the second summarize, I want to use the Site column as the region. This is because we have some orders that go from one site to another site.  Is this possible?

[v-janeyg-msft]

Hi, @dapperscavenger 

I'm a bit confused, no matter what the column names are in two summarizecolumns tables, The column names in the return table will match the column names in table_expression1(your first summarizecolumns table). So I want to know how the current result is different from what you want.  Can you show the two tables separately and the final result table?

UNION function (DAX) - DAX | Microsoft Docs

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Docs

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dapperscavenger · Accepted solution]

It was confusing.  It took me some time but I worked it out, and I did need to use SELECTCOLUMNS and UNION to get what I needed.

Essentially I had data across too many different tables that I needed to pull together.  But I am going to have a chat with my team about cleaning up the schema because I shouldn't have to create a table like this in dax!

DDFlows = 
// ----------------------------- Sum QTYs on orders ----------------------------- // 

var T_QTY =  SELECTCOLUMNS(
    'kpi delivery_line',
    "KEY", 'kpi delivery_line'[HEADER_ID] & "",
    "QTY", CALCULATE(sum('kpi delivery_line'[SHIPPED_QUANTITY]))
    )

// ----------------------- Define orders from production to warehouses  ------------------------ // 

var TS_OrdersSupply =  CALCULATETABLE(SELECTCOLUMNS( 
    'kpi delivery',
    "KEY",   'kpi delivery'[ID] & "",
    "SHIP FROM", 'kpi delivery'[SHIP_FROM_SITE_NAME]&"",
    "SHIP TO", 'kpi delivery'[SHIP_TO_SITE_NAME]&"",
    "Date", 'kpi delivery'[ACTUAL_SHIP_DATE]
    ),
    FILTER('kpi delivery', 'kpi delivery'[PROCESS_TYPE] = "supply"),
    FILTER('kpi delivery', 'kpi delivery'[ACTUAL_SHIP_DATE] <> BLANK())
) 

var JS1 = NATURALINNERJOIN ( T_QTY, TS_OrdersSupply )

var TS_SitesTO =  CALCULATETABLE(SELECTCOLUMNS(
    'kpi plant_group_map',
    "SHIP TO", [SHIP_FROM_LOC_NAME]&"",
    "Destination",'kpi plant_group_map'[Site L2]&""
    ),
    FILTER('kpi plant_group_map', 'kpi plant_group_map'[Movement Type] = "Indirect Sites" && 'kpi plant_group_map'[Exclusion] = "Include")
    )

var JS2 = NATURALINNERJOIN(JS1, TS_SitesTO)

var TS_SitesFROM =  CALCULATETABLE(SELECTCOLUMNS(
    'kpi plant_group_map',
    "SHIP FROM", [SHIP_FROM_LOC_NAME]&"",
    "Source",'kpi plant_group_map'[Site L2] &""
    ),
    FILTER('kpi plant_group_map', 'kpi plant_group_map'[Movement Type] = "Direct Sites" && 'kpi plant_group_map'[Exclusion] = "Include")
    )

var JS3 = NATURALINNERJOIN(JS2, TS_SitesFROM)

// ----------------------------- Define orders from prod & whs to regions  ----------------------------- //

var TD_OrdersDemand =  CALCULATETABLE(SELECTCOLUMNS( 
    'kpi delivery',
    "KEY",   'kpi delivery'[ID] & "",
    "SHIP FROM", 'kpi delivery'[SHIP_FROM_SITE_NAME]&"",
    "SHIP TO", 'kpi delivery'[SHIP_TO_SITE_NAME]&"",
    "Date", [ACTUAL_SHIP_DATE]
    ),
    FILTER('kpi delivery', 'kpi delivery'[PROCESS_TYPE] = "demand"),
    FILTER('kpi delivery', 'kpi delivery'[ACTUAL_SHIP_DATE] <> BLANK())
) 

var JD1 = NATURALINNERJOIN ( T_QTY, TD_OrdersDemand )

var TD_RegionsTO =  CALCULATETABLE(SELECTCOLUMNS(
    'kpi ship_to_map',
    "SHIP TO", 'kpi ship_to_map'[SHIP TO]&"",
    "Destination", 'kpi ship_to_map'[REGION]
    ))

 var JD2 =  NATURALINNERJOIN ( TD_RegionsTO, JD1 )

var TD_SitesFROM =  CALCULATETABLE(SELECTCOLUMNS(
    'kpi plant_group_map',
    "SHIP FROM", [SHIP_FROM_LOC_NAME]&"",
    "Source",'kpi plant_group_map'[Site L2] &""
    ),
    FILTER('kpi plant_group_map', 'kpi plant_group_map'[Exclusion] = "Include")
    )


var JD3 = NATURALINNERJOIN ( TD_SitesFROM, JD2 )

return

UNION(SELECTCOLUMNS(JD3, "Source", [Source], "Destination" , [Destination], "QTY", [QTY], "Date", [Date]), SELECTCOLUMNS(JS3, "Ship From", [Source], "Ship To" , [Destination], "QTY", [QTY], "Date", [Date]))

[v-janeyg-msft]

Hi, @dapperscavenger 

Good, If your problem has been solved, you can mark your answer as solution to close the thread. Thank you.

Best Regards,

Community Support Team _ Janey

[amitchandak]

@dapperscavenger , Union will not bother unless the data type is different(so you can use site in region) . But if want to take two regions, then you might have to use selectcolumns to rename

[dapperscavenger]

If I try to use the Site straight up, it throws me an error.  So I need to somehow rename Site as Region in that particular column:

 SUMMARIZECOLUMNS (
    'dctdg8f3n26_kpi delivery'[DELIVERY_ID],
    'dctdg8f3n26_kpi plant_group_map'[Site L2],
    'dctdg8f3n26_kpi delivery'[SHIP_TO_SITE_NAME],
    'dctdg8f3n26_kpi plant_group_map'[Site L2],
    'dctdg8f3n26_kpi plant_group_map'[Movement Type],
    'dctdg8f3n26_kpi delivery'[PROCESS_TYPE],
    'dctdg8f3n26_kpi delivery_line'[SHIPPED_QUANTITY],
    FILTER ( 'dctdg8f3n26_kpi plant_group_map', 'dctdg8f3n26_kpi plant_group_map'[Movement Type] = "Direct Sites"),
    FILTER ( 'dctdg8f3n26_kpi delivery', 'dctdg8f3n26_kpi delivery'[PROCESS_TYPE] = "supply")
)

In SQL I'd say something like [Site] AS Region but not sure how to do that in dax.