Need Help , lookup value from another table

[Sachy123]

In my scenario, I have two tables Contract Hierarchy ID ParentID ChildID 1 1 6 2 1 7 3 2 8 4 4 10 5 2 9 6 4 11 7 5 12 8 3 13 10 5 14 and ContractDetails ID Contractnumber 1 ABC1234 2 ABC1235 3 ABC1236 4 ABC1237 5 ABC1238 6 ABC1239 7 ABC1240 8 ABC1241 9 ABC1242 10 ABC1243 11 ABC1244 12 ABC1245 13 ABC1246 14 ABC1247 I have related the tables on ParentContractID, so I was able to use the related function ParentContractNumber = RELATED(ContractDetails[Contractnumber]) to lookup value in the Parent Hierarchy Question.. How can I lookup related ChildContractNumber?

[MFelix]

Hi @Sachy123, One point that I think I missed on your question you want to get the related ChildContractNumber or the parent ID on the ContractDetails? And what is the purpose? If you just need to create a table visual with that information having the relationship active between both tables is enough to make it work, you just need to use the fields where the ParentID is unique and then the ChildID field. Can you share additional information. MFelix

[Sachy123]

I would like to add two columns Parent Number Child Number in the ContractHierarchy Table.

[MFelix]

Hi @Sachy123, Regarding the post of images is an error on the website, microsoft is working on it,

I also cannot do it but to my knowledge it will be solve soon. In concern to your question the issue is with you relationships you need to have a single relationship between both tables and with bidirectionality active it will give you blank values.

I have made your model but a single relationship between both table with a many to one between ParentID and ContractDetails ID. (I also tried it with no relationship and it worked ok. Create the following two columns:

ParentIDContratNumber = LOOKUPVALUE(ContractDetails[ContractNumber];ContractDetails[ID];ContractHierarchy[ParentID]) 

ChildIDContractNumber = LOOKUPVALUE(ContractDetails[ContractNumber];ContractDetails[ID];ContractHierarchy[ChildID])

If you have the active relationship you can use the RELATED syntax for the column that is related in this case the ParentContractNumber.

A second Option is to use the query editor and in this case make two merge one with the link to parentId and another with ChildId and no need to make relationship between the tables.

Code is below:

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzJDcBACAN74b2PeO/Ugui/jRiLPCzNYGR3gzVlWzS3XnZkg5SXK5ukDB7pqvKV7b+E9BDzAV16iTmGIeVC1dMiPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ParentID = _t, ChildID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ParentID", Int64.Type}, {"ChildID", Int64.Type}}), 
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ParentID"},#"ContractDetails (MLanguage)",{"ID"},"ContractDetails (MLanguage)",JoinKind.LeftOuter),
    #"Expanded ContractDetails (MLanguage)" = Table.ExpandTableColumn(#"Merged Queries", "ContractDetails (MLanguage)", {"ContractNumber"}, {"ContractDetails (MLanguage).ContractNumber"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded ContractDetails (MLanguage)",{{"ContractDetails (MLanguage).ContractNumber", "ParentIDContractNumber"}}), 
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns",{"ChildID"},#"ContractDetails (MLanguage)",{"ID"},"ContractDetails (MLanguage)",JoinKind.LeftOuter), 
    #"Expanded ContractDetails (MLanguage)1" = Table.ExpandTableColumn(#"Merged Queries1", "ContractDetails (MLanguage)", {"ContractNumber"}, {"ContractDetails (MLanguage).ContractNumber"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded ContractDetails (MLanguage)1",{{"ContractDetails (MLanguage).ContractNumber", "ChildIDContractNumber"}})
 in
    #"Renamed Columns1" 

It should give the expected result, check PBIX file attach, the MLANGUAGE codification tables are the ones that uses the query editor the other are for the DAX with relationship active.

Regards,

MFelix

[Sachy123]

I am now really puzzled,

because it gives you the results and to me not!!

[MFelix · Accepted solution]

Hi @Sachy123,

In my example I have the relationship with without bidirectionalty, using the both way filtering on relationships works in "stranges" ways.

Regards,

MFelix

[Sachy123]

I see! its strange anyways.. thanks!!

[v-yuta-msft]

@Sachy123,

Change the direction of relationship between ContractDetails and ContractHierarchy from both to single and check again.

Regards,

Jimmy Tao

[Sachy123]

I want to attach the pbix file, how can I do that?

[Sachy123]

PBX file

[Sachy123]

somehow I am not able to post images here.. so I posted it on stackoverflow https://stackoverflow.com/questions/54118654/how-to-lookup-values-from-another-table-with-inactive-r... you can see the images here..

[MFelix]

Hi @Sachy123 Try the LOOKUPVALUE formula should be something like this: ParentContractNumber = LOOKUPVALUE( Contract[Hierarchy]; Contract[ParentID]; ContractDetails[ContractNumber]) This works like VLOOKUP on excel. Regards, MFelix

[Sachy123]

So this formula c = LOOKUPVALUE(ContractHierarchy[ParentContractNumber],ContractDetails[ID],ContractHierarchy[ChildID]) fetches me nothing.. 😞 I need still the contract number of child contract in the hierarchy table..

[Sachy123]

** EDIT ** I used the following formula, but its still fetching me nothing = LOOKUPVALUE(ContractDetails[ContractNumber],ContractDetails[ID],[ChildID])