cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

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.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

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

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

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.

View solution in original post

Highlighted
Resolver I
Resolver I

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors