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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ray_Brosius
Helper III
Helper III

Need to find and count rows in one table that are not present in another table

I am trying to compare Snapshots for sales activity history.  We have created daily snapshots and then made a copy of the snapshot table. The user can filter both tables independently to select the two Snapshot Dates they want to compare.

Below is an example.. we can count the number of deals that start in P1 and Status and then the Number of items in P1 and Status.

The challenge is to count the number of "NEW" items in P2 table.  That is they were created after the user selected snapshot date  for P1.   So id 63636 is NEW in P2 based on the fact that it was created after the P1 Date and is in the P2 Date snapshot.  We want to count these items and also sum the amount of New Items.

 

Having trouble getting the DAX correct to first find the "New" items and then count them.

 

Ray_Brosius_1-1689597237649.png

 

 

2 REPLIES 2
Ray_Brosius
Helper III
Helper III

Appreciate the quick response.  however, the countrows function does not work.. or at least I can't make it work. but let me provide some more details.

 

 

Below is a screenshot of what we are trying to do.  

We have a daily snapshot table in our Database.  WE read that in and then make a COPY of the table. Probably not the most efficient but we don't have a HUGE dataset.  WE then allow the user in reports to Select two SNAPshots that they want to compare.  

We then need to identify the items in filtered Snapshot 2 that are NOT in the filtered Snapshot 1.  We might just count them or we might calcuate(sum,avg etc) the values in other columns.  We consider these items to be "NEW" in that they did not exist on the date of filtered Snapshot1 but do exist in Snapshot 2.  

 

This example does not depict all the "Columns/Attributes" that are captured in our snapshots.  We will provide slicers/filters for the user to be able to dig down into specifics..

Ray_Brosius_0-1689770505553.png

 

amitchandak
Super User
Super User

@Ray_Brosius , Assume your date are joined with tables nad filtering those

New id in Table 2, Plot measure with if of Table 2

 

countrows(filter(Table2, not(Table2[Id] in allselected(Table1[id]) )))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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