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
Anonymous
Not applicable

Deleted/Added Records from previous dataset

Pros, apparently a simple scenerio but didn't find any source. 

 

I populate a table with a dataset I get every month. I need two measure which calculate newly added and newly deleted records. 

what I mean is that a record did exist in previous month but now it does not; and similarly added were the onese found for the first time in the current dataset. Notice that ID 10 does not exist in February as well as March, however February should be the only month where it should be counted.

Capture.PNG

2 REPLIES 2
justinh
Advocate IV
Advocate IV

Use Merge As New, but choose which merge type. You'll have to do it twice to get the two seperate tables you want. If Table 1 was your old table and Table 2 is your new table, you'll want to choose something like "Rows Only in First Table" to get the deleted rows and "Rows Only In Second Table" to get the newly added rows. 

lbendlin
Super User
Super User

Use the EXCEPT() DAX function

 

Let's say a is the current snapshot and b the previous one.

EXCEPT(a,b)  will give you all the rows that are in a but not in b (ie additions), EXCEPT(b,a) will give you all rows that are in B but not in a (ie deletions)

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.

Top Solution Authors
Top Kudoed Authors