Display rows that do not exist in related table?!

[zackgregory12Participant Started: 3Replies: 1Total: 4]

The scenario is simple:

I have two tables called “Sales Today” and “Sales Yesterday”. “Yesterday” is a cumulative running list of all transactions before today. The “Today” table is everything in the “Yesterday” table PLUS the new data (new row entities added each day). In other words, this source data file has to be overwritten each day and there is no simple way to quickly distinguish today’s new rows from all of the historical rows.

I need to compare the two tables based on the unique key and simply determine what new rows have been added for “today”. For simplicity’s sake, picture two tables with 2 columns: (User ID, User Name). I need to compare Today’s UserID values to Yesterday’s UserID values and simply determine what new rows were added to the “Today” table that don’t exist in the “Yesterday” table.

For the end result, I would like to somehow display only the rows that exist in “Today” that do not exist in “Yesterday” (only display the new rows).

If DAX can’t quite narrow all the way down to displaying only rows, then a simple flag mechanism such as IF(Today.value does not exist in Yesterday_table, 1, 0).

Thank you for your help!

Zack

Data Analytics Manager

Hewlett-Packard

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Zack,

Easily done through Power Pivot.

If you have Excel 2013, attached is a workbook that demonstrates two techniques:

Pivot Table

Linkback Table

If you have Excel 2010, let me know and I will attach an Excel 2010 workbook.

 

Attachments:

You must be logged in to view attached files.

[zackgregory12Participant Started: 3Replies: 1Total: 4]

Ha, wow. That seems so simple!

It’s amazing how a simple DAX formula can eliminate several macros and extremely long formulas, haha.

Thanks a lot for taking the time to help out a stranger. I’m a newbie to DAX, so I’m still getting used to everything.

Thank you again, I sincerely appreciate it.

Zack Gregory

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Zack,

Since you are new to Power Pivot and DAX, I would like share a few ideas that made my Power Pivot journey interesting:

“DAX Formulas for Power Pivot” by Rob Collie is a great book for starters and a good reference for more experienced users.

If you want to look into examples of advanced DAX, you could look into this website:

http://www.daxpatterns.com/patterns/

And when your DAX formulas extend beyond a couple of functions, there is this site that I use to make my longer DAX functions more readable:

http://www.daxformatter.com/

 

[Author]

Posts