joining tables on multiple columns

[Anonymous]

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 in either a column called "Order Number" or a column called "Reference" in transport costs.

This is the same in transactional data.

I want to know if there is a way to merge the two tables, so that if we were to call the columns in Transports Costs A & B, and the column in Transactional Data C & D, that I could create a merge where BI would search A in C, then A in D, then B in C then B in D, and if ANY of these are true then to inner join the rest of the data.

Here is my example:

GL CODE	DATE	CONSIGNMENT/REFERENCE	ORDER NUMBER	CHARGE	FUEL	SUBTOTAL	GST	TOTAL	CARRIER
3800 0000 1190 1000	14/01/2018	I3439822	5540097201802	$                   38.00	$           2.70	$           40.70	$             4.07	$                                           44.77	MAIN100 ChemCouriers - Mainfreight

Transaction Type	Exp & Cust Sales Qty	Exp & Cust Sales Val	Stock On Hand	Transaction	Reference 1	Reference 2
GRI	3	6047.61	6	7954473	7954058	T692507

In this example, I would want BI to search "ORDER NUMBER" in both "Reference 1" and "Reference 2", and then to search "CONSIGNMENT/REFERENCE" in both "Reference 1" and "Reference 2", and if any of these are TRUE then to merge that row, and otherwise to leave it blank. The hiearchy would be Order Number first then if it can't find a match to search through Consignment/Reference second.

Thanks for your time.

[v-yulgu-msft]

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]

@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.