Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.