Create UNION keeping DAX calculated fields (ways to perform this)

[Anonymous]

Hello,

I just created a report in which I get data from multiple tables with different data structure. I needed to create some calculated fields in each table and after that to perform UNION in order to create a single master table from which I created my visuals. 

I had to perform UNION in DAX (and not in query editor) so I could keep the DAX calculated fields after the UNION. UNION in DAX required to add a big amount of columns in each table and arrange all these columns in the same order so all columns had a correct match in UNION. 

The issue in this is that, now I have a very sensitive report when I need to make changes in its structure. For example, when I need to add a new column I need to add this column to all tables maintain the same order in all tables and then use the new column to the report.

Is there any other way to perform UNION keeping the DAX formulas without have all this trouble when my data structure changes?

[v-lili6-msft]

HI, @Anonymous 

When you use UNION function to create the table, 

Use SELECTCOLUMNS expression instead of a tanle name

for example:

Table = UNION(Table1,SELECTCOLUMNS(Table3,"A",[A],"B",[B],"C",[C]))

Best Regards,

Lin

[Anonymous]

Hello Li,

Many thanks for your suggestions. I tried to apply the solution you suggested. However, this solution still requires all the tables to have the same number of columns as I get the following message "Each table argument of 'UNION' must have the same number of columns.

Please see my example below: 

As you can see some fields exist in both tables and some others don't. My task is to append the data of Table 2 just below of the data of Table 1 and the fields that do not exist in Table 1 or 2 to be blank automatically.

test teble = UNION(SELECTCOLUMNS('Table1'; "d"; [d]; "pi"; [pi]; "LA"; [LA]; "ba"; [ba]; "ve"; [ve]; "SO"; [SO]; "O"; [O]; "AV"; [AV]; "P"; [P]; "B"; [B]; "C"; [C]; "R"; [R]);
SELECTCOLUMNS('Table 2'; "d"; [d]; "LA"; [LA]; "pr"; [pr]; "ba"; [ba]; "BB"; [BB]; "F"; [FV]; "O"; [O]; "AV"; [AV]; "B"; [B]; "C"; [C]; "HA"; [HA]; "P"; [P]; "R"; [R]; "CO"; [CO]; "D"; [D]; "S"; [S]; "Con"; [Con)])
)

As I mentioned in the first message I would like to avoid the process of adding blank columns manually in all tables in order to meet the requirement to have all tables with the same amount of columns and also to have the trouble of sorting them correctly.

What do you think?

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

First, Each table argument of 'UNION' must have the same number of columns.

Second, for your requirement, you could add blank columns in SELECTCOLUMNS expression not in table.

For example:

SELECTCOLUMNS('Table',"a",[a],"b",[b],"blank c",BLANK())

Best Regards,
Lin

[Anonymous]

This is working fine! Many thanks for your help