cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saipawar
Helper IV
Helper IV

How to create a flag while reconciling records from 2 tables

Hi

 

I have two data tables 

Table 1 - Let's call it "Pre"

Pre Transaction ID Amount
110
215
330

 

Table 2 - Let's call it "Post"

Post Transaction IDAmount
110
215
475

 

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)

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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 ],
    " | "
)

Screenshot 2021-09-20 220253.png

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

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 ],
    " | "
)

Screenshot 2021-09-20 220253.png

View solution in original post

amitchandak
Super User
Super User

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!