Adding columns from related table

[Anonymous]

I have two table “Document Register” & Workflow”.  What I want is

IF Document Number and Rev(columns) in document register table is matching with document number and Rev(columns) in workflow table then add columns (Date due, Date finished) from workflow table to Document Register table .

Please note both tables are related with one to Many relationship

[v-lili6-msft · Accepted solution]

hi @Anonymous 

First, Revision from document table and Revision table from WF table are different data type, you need to keep them same data type.

Second, both tables are related with one to Many relationship, for document Number and Revision from document table = Document Number & Revision table from WF table, it may have one or more date value(Date due, Date finished) from WF table for each document Number and Revision, so just try this formula logic as mwegener: 

Completed Date = MAXX(FILTER(RELATEDTABLE(WF), WF[Revision] = 'aconex vw_Document'[Revision]),WF[Date due])

Regards,

Lin

[mwegener]

Hi @Anonymous ,

with PowerQuery use "Merge Queries"

with DAX RELATED for the many to one side

https://docs.microsoft.com/en-us/dax/related-function-dax

                RELATEDTABLE for the one to many side

https://docs.microsoft.com/en-us/dax/relatedtable-function-dax

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Not Working, Again i want to add columns based on excat match of values in both table

[mwegener]

Hi @Anonymous ,

could you share a sample PBIX File?

[Anonymous]

Shared using dropbox

[mwegener]

The URL is missing.

[Anonymous]

https://www.dropbox.com/s/gvgtvtcck903vam/SD%20Analysis%20-%20TP9.pbix?dl=0 

[mwegener]

Hi  @Anonymous .

did I understand you correctly that you want to put the column from the WF table into the document table?

Like this?

Completed Date = MAXX(RELATEDTABLE(WF),WF[Completed Date])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Yes but based on condition

If document Number and Revision from document table = Document Number & Revision table from WF table then

add date columns from WF table to Document table

[v-lili6-msft · Accepted solution]

hi @Anonymous 

First, Revision from document table and Revision table from WF table are different data type, you need to keep them same data type.

Second, both tables are related with one to Many relationship, for document Number and Revision from document table = Document Number & Revision table from WF table, it may have one or more date value(Date due, Date finished) from WF table for each document Number and Revision, so just try this formula logic as mwegener: 

Completed Date = MAXX(FILTER(RELATEDTABLE(WF), WF[Revision] = 'aconex vw_Document'[Revision]),WF[Date due])

Regards,

Lin

[mwegener · Accepted solution]

Hi @Anonymous ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi  @Anonymous ,

You could do it like this, but your data types do not match.

Completed Date = MAXX(FILTER(RELATEDTABLE(WF), WF[Revision] = 'aconex vw_Document'[Revision]),WF[Completed Date])

WF - Revison = 0 (Integer)

Document - Revisoin = C0 (Text)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

how to share please give me ur email id