The DAX union statement behaves weird when applied to more than 2 tables. Below 5 tables have the same list of columns in the same order, and still union doesn't behave as expected. Example code below for the work-around which is a bunch of SELECTCOLUMNS statements.
1. this works: UNION(f_aux1_ETR_IT_act, f_aux1_ETR_IT_etc) works as expected. But for 3 tables it doesn't.
2. nesting unions only taking union of 2 tables at a time, didn't work either
3. workaround is below, using explicit selectcolumns in the union. Cumbersome
f_act_etc = -- UNION mixes columns when using more than 2 tables, also when nesting UNIONs, only work-around was this
UNION(
SELECTCOLUMNS(f_aux1_ETR_IT_act,
"amount" ,[amount],
"amount (md)", [amount (md)],
"amount type", [amount type],
"Budget Code", [Budget Code],
"IT_Concepts_Lvl3a_NEW", [IT_Concepts_Lvl3a_NEW],
"OBS_FEATURE", [OBS_FEATURE],
"OBS_RESOURCE_NAME", [OBS_RESOURCE_NAME],
"OBS_RESOURCE_TYPE", [OBS_RESOURCE_TYPE],
"po_number", [po_number],
"supplier_id", [supplier_id],
"value_date", [value_date]
),
SELECTCOLUMNS(f_aux1_ETR_IT_etc,
"amount", [amount],
"amount (md)", [amount (md)],
"amount type", [amount type],
"Budget Code", [Budget Code],
"IT_Concepts_Lvl3a_NEW", [IT_Concepts_Lvl3a_NEW],
"OBS_FEATURE", [OBS_FEATURE],
"OBS_RESOURCE_NAME", [OBS_RESOURCE_NAME],
"OBS_RESOURCE_TYPE", [OBS_RESOURCE_TYPE],
"po_number", [po_number],
"supplier_id", [supplier_id],
"value_date", [value_date]
),
SELECTCOLUMNS(f_aux2_t_fcst_act,
"amount", [amount],
"amount (md)", [amount (md)],
"amount type", [amount type],
"Budget Code", [Budget Code],
"IT_Concepts_Lvl3a_NEW", [IT_Concepts_Lvl3a_NEW],
"OBS_FEATURE", [OBS_FEATURE],
"OBS_RESOURCE_NAME", [OBS_RESOURCE_NAME],
"OBS_RESOURCE_TYPE", [OBS_RESOURCE_TYPE],
"po_number", [po_number],
"supplier_id", [supplier_id],
"value_date", [value_date]
),
SELECTCOLUMNS(f_aux2_t_fcst_etc,
"amount", [amount],
"amount (md)", [amount (md)],
"amount type", [amount type],
"Budget Code", [Budget Code],
"IT_Concepts_Lvl3a_NEW", [IT_Concepts_Lvl3a_NEW],
"OBS_FEATURE", [OBS_FEATURE],
"OBS_RESOURCE_NAME", [OBS_RESOURCE_NAME],
"OBS_RESOURCE_TYPE", [OBS_RESOURCE_TYPE],
"po_number", [po_number],
"supplier_id", [supplier_id],
"value_date", [value_date]
),
SELECTCOLUMNS(f_aux3_ETC_softw_etc,
"amount", [amount],
"amount (md)", [amount (md)],
"amount type", [amount type],
"Budget Code", [Budget Code],
"IT_Concepts_Lvl3a_NEW", [IT_Concepts_Lvl3a_NEW],
"OBS_FEATURE", [OBS_FEATURE],
"OBS_RESOURCE_NAME", [OBS_RESOURCE_NAME],
"OBS_RESOURCE_TYPE", [OBS_RESOURCE_TYPE],
"po_number", [po_number],
"supplier_id", [supplier_id],
"value_date", [value_date]
)
)