Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
I am now really puzzled,
because it gives you the results and to me not!!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI see! its strange anyways.. thanks!!
Change the direction of relationship between ContractDetails and ContractHierarchy from both to single and check again.
Regards,
Jimmy Tao
I want to attach the pbix file, how can I do that?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |