topic Re: X-Lookup two columns within difference tables and confirm match in DAX Commands and Tips

X-Lookup two columns within difference tables and confirm match

khisla — Wed, 26 Feb 2025 08:58:13 GMT

Hello Power BI Helpers-

Need some help matching customer order from table 1 to customer returns from table 2.

Match needs to take into consideration the combination of two columns - Order # column and SKU # column.

Expected result is to mark in Table 1 would be shown in an additional column if the combination was found or was not found in Table #2 represented by a 1 or 0.

Example

Table 1- Order Information

Date	Order ID	Product	SKU
January 1, 2025	AL12345	Pens	LXM_920
January 2, 2025	AL56789	Pencils	UVX_556
January 10, 2025	AL09876	Erasers	EDC_111
January 10, 2025	AL09876	Erasers	EDC_113

Table 2 - Returns

Date	Order Number	Product	Item ID
January 12, 2025	AL12399	Pens	LXM_920
January 22, 2025	AL56789	Pencils	UVX_556
January 15, 2025	AL09876	Erasers	EDC_113

Expceted Result

Date	Order ID	Product	SKU	Match
January 1, 2025	AL12345	Pens	LXM_920	0
January 2, 2025	AL56789	Pencils	UVX_556	1
January 10, 2025	AL09876	Erasers	EDC_111	0
January 10, 2025	AL09876	Erasers	EDC_113	1

Thanks in advance.

Re: X-Lookup two columns within difference tables and confirm match

Sahir_Maharaj — Wed, 26 Feb 2025 09:04:03 GMT

Hello khisla,

Can you please try this approach:

Match = VAR Found = LOOKUPVALUE( 'Returns'[Item ID], 'Returns'[Order Number], 'Order Information'[Order ID], 'Returns'[Item ID], 'Order Information'[SKU] ) RETURN IF(NOT(ISBLANK(Found)), 1, 0)

Re: X-Lookup two columns within difference tables and confirm match

ThxAlot — Wed, 26 Feb 2025 16:53:42 GMT

The solution is very straightforward and also scalable if more columns should be incorporated.

Re: X-Lookup two columns within difference tables and confirm match

khisla — Tue, 04 Mar 2025 08:46:18 GMT

Thanks you so much!!

This worked perfectly.