Lookupvalue using different table based on date order entered in Dynamics 365

[barbforsman]

We had a salesperson, CW here until the end of 2017.  She did not enter her own orders in Dynamics 365, so I used a table, Account Rep to lookup the value of who created the order and pull CS in as the Account Rep on the order.

GF replaced her at the beginning of this year.  I've tried multiple ways to pull the correct data and seem unable to do so.  In my latest attempt, I created a new table, Account Rep18 with his info and used this formula:

Acct Rep3 = IF(salesorders[createdon].[Date] < 1/1/2018,
LOOKUPVALUE('Account Rep'[Account Rep],'Account Rep'[Owner ID Value],salesorders[createdby.ownerid]),
LOOKUPVALUE('Account Rep2018'[Account Rep18],'Account Rep2018'[Owner ID Value],salesorders[_ownerid_value])
)

An added twist to the story is that CW accounts and contacts have been reassigned to GF in CRM, so old orders were also reassigned in that process.  I thought I could use Created By for orders entered <1/1/2018, but that didn't seem to work either.

Any help will be much appreciated!!

I've uploaded a scrubbed file here.

[Anonymous · Accepted solution]

This sounds like something that should be handled in Query Editor.  If you use DAX to solve this problem, you're only creating new tables and adding to the size of the data model.  This will decrease performance.

I suggest you map out exactly which changes need to occur, and use a combination of 1-time editing of flat files and then Query Editor to finish cleaning up the data.

This way you can either remove CW from past data, or ensure that GF is correctly applied moving forward.

Either way, using Query Editor will ensure that only 1 table is loaded to the data model, which will make everything run smoother.

[barbforsman]

Thank you - this gave me enough to go on to solve the issue.