Replace values

[Anonymous]

Hi,

Background

I have two spreadsheets. Table 1 is a 'master' document and table 2 is a change log that records any changes to the master document.

All rows have a unique code number that links the two spreadsheets. E.g. If there is a R01 in the master document changes will be recorded in the change log next to R01.

Problem

I am able to identify through DAX expression whether there is any changes recorded in the change log but need to be able to replace the values in 'master document' with the changes recorded in the change log.

 

E.g. if the risk description in table 1 is changed in table 2 I need a forumla that identifies 1.) there is a change, and 2.) that the risk description in table 1 should reflect that change

[ryan_mayu · Accepted solution]

if you created relationship between two tables you can try this

Column = if(RELATED('Table 2'[Risk description change])="",'Table 1'[Risk description],RELATED('Table 2'[Risk description change]))

if don't have the relationship, you can try this

Column 2 =

var _update=maxx(FILTER('Table 2','Table 1'[Risk ID]='Table 2'[Risk ID ]),'Table 2'[Risk description change])

return if(_update="",'Table 1'[Risk description],_update)

pls see the attachment below

[Anonymous]

Sorry this just gives lots of errors. Can you recommend a DAX code?

[ryan_mayu · Accepted solution]

if you created relationship between two tables you can try this

Column = if(RELATED('Table 2'[Risk description change])="",'Table 1'[Risk description],RELATED('Table 2'[Risk description change]))

if don't have the relationship, you can try this

Column 2 =

var _update=maxx(FILTER('Table 2','Table 1'[Risk ID]='Table 2'[Risk ID ]),'Table 2'[Risk description change])

return if(_update="",'Table 1'[Risk description],_update)

pls see the attachment below

[Anonymous]

This works great-thank you

[ryan_mayu · Accepted solution]

you are welcome

[ryan_mayu]

@Anonymous 

could you pls provide the sample data(not the table visual) of the two tables that you mentioned and the expected output?

[Anonymous]

Table 1

Risk ID	Risk description
R01	There is a risk that the puppy won't have a nice temperment
R02	There is a risk that the puppy will make a mess
R03	There is a risk that the puppy will chew expensive shoes
R04	There is a risk that the puppy won't be potty trained
R05	There is a risk that the puppy won't be able to be trained

Table 2

Risk  ID	Risk description change
R01	Cavoodle puppy might not have a nice temperment

[ryan_mayu]

what's the definition of change? 

We have records in table 2?

why in your screenshot , the status of R02 is also change?

[Anonymous]

Definition of change if if content in table 2 is different to content in table 1 there is a an entry in the change log, which means the content in table 1 needs to be updated. 

So ideal situation in the tables above would be that either table 1 or perhaps a new table would look like this:

Risk ID	Risk description
R01	Cavoodle puppy might not have a nice temperment
R02	There is a risk that the puppy will make a mess
R03	There is a risk that the puppy will chew expensive shoes
R04	There is a risk that the puppy won't be potty trained
R05	There is a risk that the puppy won't be able to be trained

[ryan_mayu]

@Anonymous 

you can do this in PQ.

pls see the attachment below