Append two tables using DAX

To solve the issue described (appending two tables using DAX in Power BI), here’s how you can approach it:

Problem Description

1. Table 1: Contains existing rows with ID, SID, Hier1, and Hier2.

2. Table 2: Contains new rows that need to be appended to Table 1.

3. The ID column in the resulting table should maintain the same value (UN1) for all appended rows as per the existing structure in Table 1.

Solution Using DAX

You cannot directly append tables using DAX as DAX is for creating calculated columns and measures. However, you can achieve this by creating a calculated table that appends the two tables together.

Steps:

1. Create a Calculated Table:

Use the UNION function in DAX to combine Table1 and Table2.

FinalTable = 

UNION(

    Table1,

    ADDCOLUMNS(

        Table2,

        "ID", "UN1", -- Assign the fixed ID for rows from Table2

        "SID", BLANK() -- Optional: Handle any missing columns

    )

)

2. Explanation of the Formula:

• UNION: Combines rows from Table1 and Table2.

• ADDCOLUMNS: Adds the ID column with a fixed value of UN1 for rows in Table2 if it’s not already there.

3. Load Resulting Table:

This will generate a combined table (FinalTable) in your Power BI model, which includes all rows from both tables, with the required ID formatting.

4. Custom Adjustments:

• If Table2 already has the ID column and you want to overwrite it, use RENAMECOLUMNS before applying UNION.

• Ensure column names and data types in Table1 and Table2 align.

Note

If row-level security or complex filtering is involved, consider combining this approach with measures or calculated columns for dynamic logic.

Let me know if you need further clarifications!