Forum Discussion
Need Help , lookup value from another table
13 Replies
- 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..
- ** EDIT ** I used the following formula, but its still fetching me nothing = LOOKUPVALUE(ContractDetails[ContractNumber],ContractDetails[ID],[ChildID])
- 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
- 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-relationship you can see the images here..
- I would like to add two columns Parent Number Child Number in the ContractHierarchy Table.
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
