Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

joining tables on multiple columns

I have two tables , one for transacational data and one for transport costs.    I want to merge them on a common "Order Number". However , due to data issues, the Order Number maybe be contained i...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

You could create a calculated table with below DAX to merge these two tables.

merge table =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN ( transacational, 'Transports Costs' ),
        "Check1", IF (
            [ORDER NUMBER] = transacational[Reference 1],
            1,
            IF (
                'Transports Costs'[ORDER NUMBER] = transacational[Reference 2],
                1,
                IF (
                    'Transports Costs'[CONSIGNMENT/REFERENCE] = transacational[Reference 1],
                    1,
                    IF (
                        'Transports Costs'[CONSIGNMENT/REFERENCE] = transacational[Reference 2],
                        1,
                        0
                    )
                )
            )
        )
    ),
    [Check1] = 1
)

Best regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-yulgu-msft

 

When I try to run this, I end up getting a "Working On It" for far longer than this should take - while I can do a single Merge Query in 3 minutes, this one has been running for 2 hours and still isn't completed. Is this normal?

This has been up for 2 hours.