Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Fix DAX engine behaviour for union of more than 2 tables.

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]
        )
    )
Status: New