topic Compare columns based on two different table in DAX Commands and Tips

Compare columns based on two different table

SBC — Thu, 03 Nov 2022 06:00:43 GMT

Hi,

How to compare columns in two different table ,I have tried with full outer join but not working as expected.

Please check sample data provided below input of two tables and output expected.

Example:

table1:

member	deal	qty	date
T90	A20	10.5	10/18/2020
T90	A21	68.55	10/3/2020
T90	A21	68.26	10/3/2020
T90	A22	17.41	10/9/2020

table2:

Member	Deal	Qty	Date
T90	A21	68.55	2020-10-03
T90	A21	68.26	2020-10-03
T90	A22	17.41	2020-10-09

Expected output:

tbl1.deal	tbl2.Deal	Matching Deal	tbl1.member	tbl2.Member	Matching Member	tbl1.date	tbl2.Date	Matching Date	tbl1.qty	tbl2.Qty	Matching Qty
A20		No	T90		No	10/18/2020		No	10.5		No
A21	A21	Yes	T90	T90	Yes	10/3/2020	10/3/2020	Yes	68.55	68.55	Yes
A21	A21	Yes	T90	T90	Yes	10/3/2020	10/3/2020	Yes	68.26	68.26	Yes
A22	A22	Yes	T90	T90	Yes	10/9/2020	10/9/2020	Yes	17.41	17.41	Yes

Thanks,

SBC

Re: Compare columns based on two different table

AntrikshSharma — Thu, 03 Nov 2022 07:02:36 GMT

SBC Do this in PowerQuery and use 2 columns as a key when merging queries.

let Source = Table.NestedJoin ( T1, { "member ", "deal " }, T2, { "member ", "Deal " }, "T2", JoinKind.FullOuter ), ExpandedT2 = Table.ExpandTableColumn ( Source, "T2", { "member ", "Deal ", "Qty", "Date" }, { "member ", "Deal ", "Qty.1", "Date.1" } ) in ExpandedT2

If you want a full outer join with DAX you can read this and modify yourself: https://www.antmanbi.com/post/full-outer-join-in-dax-in-power-bi-and-ssas-tabular

Re: Compare columns based on two different table

SBC — Thu, 03 Nov 2022 07:22:17 GMT

Hi AntrikshSharma ,

Thanks for replying the query,I have already tried that full outer join faced issue in matching the data.

"10/03/2022 " "qty" is existed in both table same data but when we apply conditional it is displaying "NO" insted of showing "Yes"

If it works for you please share PBIX file will be helpful.

Thanks,

SBC

Re: Compare columns based on two different table

SBC — Fri, 04 Nov 2022 09:45:21 GMT

Thanks sharma 😊

Thanks,

SBC