How to create a flag while reconciling records from 2 tables

[saipawar]

Hi

I have two data tables 

Table 1 - Let's call it "Pre"

Pre Transaction ID	Amount
1	10
2	15
3	30

Table 2 - Let's call it "Post"

Post Transaction ID	Amount
1	10
2	15
4	75

Output = 

1) List and count of missing IDs that are present in 'Pre' but not in 'Post'.In this case, count = 1 (unmatched ID = 3)

2) List and count of missing IDs that are present in 'Post' but not in 'Pre'. In this case, count = 1 (unmatched ID = 4)

3)Count of Post transaction IDs that have been matched to Pre transaction IDS. In this case, count = 2 (matched ID = 1 ,2)

[CNENFRNL · Accepted solution]

Simple and intuitive enough,

IN PRE NOT IN POST = 
CONCATENATEX(
    EXCEPT(
        DISTINCT( PRE[Pre Transaction ID ] ),
        DISTINCT( 'POST'[Post Transaction ID] )
    ),
    PRE[Pre Transaction ID ],
    " | "
)

IN POST NOT IN PRE = 
CONCATENATEX(
    EXCEPT(
        DISTINCT( 'POST'[Post Transaction ID] ),
        DISTINCT( PRE[Pre Transaction ID ] )
    ),
    'POST'[Post Transaction ID],
    " | "
)

PRE & POST = 
CONCATENATEX(
    INTERSECT(
        DISTINCT( PRE[Pre Transaction ID ] ),
        DISTINCT( 'POST'[Post Transaction ID] )
    ),
    PRE[Pre Transaction ID ],
    " | "
)

[amitchandak]

@saipawar , Try measure like , but you need a common ID dimension for that

Only in pre = countx(values(ID[ID]), if(not(isblank(_pre)) && isblank(_post)))

Only in post = countx(values(ID[ID]), if(not(isblank(_post)) && isblank(_pre)))

Common = countx(values(ID[ID]), if(not(isblank(_pre)) && not(isblank(_post))))

refer for common dimension

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19