Union Table with different data

[Jorgast]

Hello Fellow Power BI friends,

I am trying to join 2 tables together. The data is so large that I know I have duplicate account numbers and cases. I created an account number table to join the sales and case tables. I made sure to filter out any duplicate or blank tables. The issue is, that I am trying to create a table report that uses the account number from the account table and then pull data from the sales and case tables. I can pull data from one table or the other but not both. I was looking online an found Union code “Test Union = FILTER(DISTINCT(UNION(DISTINCT(CASE[ACCT#]),DISTINCT('Sales’ [ACCT]))),NOT(ISBLANK(CASE[ACCT#])))”. I added the table and joined it to the Sales and Case table (1:Many – 1 way) but I am still unable to create a table report. Is there something wrong with the way I setup my table and connections? I can only do (1:Many – 2 way) for only 1 of the table connections.

Sales Table from Sales System

1. Account number
2. Name of sales agent
3. Name of the customer
4. Amount of the Sale
5. Address
6. City
7. State
8. Zip

Case Table from SalesForce

1. Account number
2. Case number
3. Sale type
4. Sale subtype
5. Sales agent

Account Number Table

• Account number based on cases table

[v-shex-msft]

Hi @Jorgast,

I check you table structure and found only two column in these tables can use to compare.(account number and sales agent)

You can use variable to store calculate table with except function with select column function to get distinct records, then merge these to one.

Sample table:

Result =
VAR sale_distint =
    CALCULATETABLE (
        'Sales',
        EXCEPT (
            SELECTCOLUMNS (
                'Sales',
                "Account number", [Account Number],
                "Name of Sales agent", [Name of Sales agent]
            ),
            SELECTCOLUMNS (
                'Case',
                "Account Number", [Account Number],
                "Name of Sales agent", [Sales agent]
            )
        )
    )
VAR case_distint =
    CALCULATETABLE (
        'Case',
        EXCEPT (
            SELECTCOLUMNS (
                'Case',
                "Account Number", [Account Number],
                "Name of Sales agent", [Sales agent]
            ),
            SELECTCOLUMNS (
                'Sales',
                "Account Number", [Account Number],
                "Name of Sales agent", [Name of Sales agent]
            )
        )
    )
RETURN
    UNION (
        SELECTCOLUMNS (
            sale_distint,
            "Account Number", [Account Number],
            "Name of Sales agent", [Sales agent]
        ),
        SELECTCOLUMNS (
            case_distint,
            "Account Number", [Account Number],
            "Name of Sales agent", [Sales agent]
        )
    )

Regards,
Xiaoxin Sheng

[Jorgast]

@v-shex-msft

How would i merge the 2 tables together?

[v-shex-msft · Accepted solution]

Hi @Jorgast,

Dax function not support to merge different tables who has different column amounts/name.

You need to select column and modify column name to keep them has same structure, then you can union these modify tables.

Regards,

Xiaoxin Sheng