DAX Solution: one source table and with a target tables. Identify the transactions differences

Question

Need DAX solution to solve the below problemComparing one source table and two target tables. Identify the transactions difference between both directions and report them. All tables are associated with SVC_ID.&nbsp;Need to generate table visuals, to show Transactions present in the Source table and not in target tables, and vice-versa. All comparisons are based on the SVC_ID. Please see the expected outcome for what I am looking for.&nbsp;Much appreciate any solutions, I was able to do this in Power query using Left Anti join, looking for a solution using the DAX approach.&nbsp;&nbsp;Table1: Source&nbsp;&nbsp;&nbsp;&nbsp;SVC_IDTxnIdTargetsystemDateDispatchIDS11AAlpha5/15/202323S11BBeta5/15/202324S22ABeta5/15/202388S33ABeta5/15/202389S44AAlpha5/15/202354S55AAlpha5/15/202366&nbsp;Table2: Alpha&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SVC_IDTxnIdTargetsystemDateSvcreponseSvCDescCommentsS11AAlpha5/15/2023200GoodProcessedS44AAlpha5/15/2023500FailreprocessS66AAlpha5/15/2023200GoodProcessed&nbsp;Table3:&nbsp; Beta&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SVC_IDTxnIdTargetsystemDateSvcreponseSvCDescRef-idS11BBeta5/15/2023200GoodAz89S77ABeta5/15/2023500FailAz890S88ABeta5/15/2023400FailAz9278&nbsp;Expected Outcome:#1&nbsp;Txns in Table1 (Source)but not in Table2(Alpha)SVC_IDTxnIdDateDispatchID55A5/15/202366&nbsp;#2Txns in Table2 (Alpha)but not in Table1(Source)&nbsp;SVC_IDTxnIdDateSvcreponseSvCDescComments66A5/15/2023200GoodProcessed&nbsp;&nbsp;===============================================#3Txns in Table1 (Source)but not in Table3(Beta)SVC_IDTxnIdDateDispatchID22A5/15/20238833A5/15/202389&nbsp;#4&nbsp;Txns in Table3 (beta)but not in Table1(Source)&nbsp;SVC_IDTxnIdDateSvcreponseSvCDescRef-id77A5/15/2023500FailAz89088A5/15/2023400FailAz9278&nbsp;

greg_deckler · Answer

LeeSun&nbsp;You can do all of these things with a Complex Selector.&nbsp;The Complex Selector - Microsoft Power BI Community

Forum Discussion

DAX Solution: one source table and with a target tables. Identify the transactions differences

1 Reply

Related content

DAX SOLUTION NEEDED

Most Recent Transaction

Predictive analytics solution

Modeling Solution?

Power BI backup solution

Table1: Source
SVC_ID	TxnId	Targetsystem	Date	DispatchID
S1	1A	Alpha	5/15/2023	23
S1	1B	Beta	5/15/2023	24
S2	2A	Beta	5/15/2023	88
S3	3A	Beta	5/15/2023	89
S4	4A	Alpha	5/15/2023	54
S5	5A	Alpha	5/15/2023	66

Table2: Alpha
SVC_ID	TxnId	Targetsystem	Date	Svcreponse	SvCDesc	Comments
S1	1A	Alpha	5/15/2023	200	Good	Processed
S4	4A	Alpha	5/15/2023	500	Fail	reprocess
S6	6A	Alpha	5/15/2023	200	Good	Processed

Table3: Beta
SVC_ID	TxnId	Targetsystem	Date	Svcreponse	SvCDesc	Ref-id
S1	1B	Beta	5/15/2023	200	Good	Az89
S7	7A	Beta	5/15/2023	500	Fail	Az890
S8	8A	Beta	5/15/2023	400	Fail	Az9278

Txns in Table1 (Source)but not in Table2(Alpha)
SVC_ID	TxnId	Date	DispatchID
5	5A	5/15/2023	66

Txns in Table2 (Alpha)but not in Table1(Source)
SVC_ID	TxnId	Date	Svcreponse	SvCDesc	Comments
6	6A	5/15/2023	200	Good	Processed

Txns in Table1 (Source)but not in Table3(Beta)
SVC_ID	TxnId	Date	DispatchID
2	2A	5/15/2023	88
3	3A	5/15/2023	89

Txns in Table3 (beta)but not in Table1(Source)
SVC_ID	TxnId	Date	Svcreponse	SvCDesc	Ref-id
7	7A	5/15/2023	500	Fail	Az890
8	8A	5/15/2023	400	Fail	Az9278