Home Forums Power Pivot Display rows that do not exist in related table?!

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 8 years, 3 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #769

    zackgregory12
    Participant
    • Started: 3
    • Replies: 1
    • Total: 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

     

    #784

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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.
    #796

    zackgregory12
    Participant
    • Started: 3
    • Replies: 1
    • Total: 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

    #799

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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/

     

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power Pivot’ is closed to new topics and replies.