Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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 ],
" | "
)
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! |
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 ],
" | "
)
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! |
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |