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.
Hi all,
I have 2 tables. The one table presents the sumproduct relation between different components.
Component ID | Component ID 2 | Factor |
NC 3 | NC 1 | 14 |
NC 3 | NC 2 | 19 |
NC 4 | NC 2 | 15 |
NC 4 | NC 3 | 16 |
NC 4 | NC 1 | 9 |
NC 5 | NC 2 | 17 |
NC 5 | NC 3 | 9 |
NC 5 | NC 4 | 14 |
For example NC3 = 14*NC1 + 19NC2. The table runs from the top down i.e. NC1 will not have a dependancy relationship to NC(x+1).
In addition I have an input table with multiple sets of input values for NC1 & NC.
Component ID | Input value 1 | Input Value 2 |
NC 1 | 200000 | 20 |
NC 2 | 100000 | 50 |
The requirement is to build a combined factor for each component:
For example:
NC4 = 15*NC2 + 16*NC3+ 9*NC1 = 15* NC2 + 16*(14*NC2 + 19*NC1) + 9 *NC1
The combined factor will then be multipled by the different input values in the input table.
Many thanks, T
Solved! Go to Solution.
I believe there should be a more elegant solution
but this might work as well
The idea is to mege Child ID with Parent ID to get Grand Children and repeat this merge until there are no children other than "NC 1" and NC 2"
Please see the attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component ID 2"},#"Changed Type",{"Component ID"},"Changed Type",JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Component ID 2", "Factor"}, {"Component ID 3", "Factor3"}), #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Component ID", Order.Ascending}}), #"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Component ID 3"},#"Sorted Rows",{"Component ID"},"Sorted Rows",JoinKind.LeftOuter), #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Component ID 2", "Factor"}, {"Component ID 4", "Factor4"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Sorted Rows",{{"Factor", "Factor2"}}), #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Component ID 2", "Component ID 3", "Component ID 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Factor2", type text}, {"Factor3", type text}, {"Factor4", type text}}, "en-US"),{"Factor2", "Factor3", "Factor4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"), #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Merged", Order.Ascending}, {"Merged.1", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each List.RemoveItems(Text.Split([Merged],","),{" ",""})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.FromText(List.RemoveItems(Text.Split([Merged.1],","),{" ",""}))), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Accumulate([Custom.1],1,(state, current) => Number.From(state) * Number.From(current))), #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Factor product"}}), #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each List.Intersect({[Custom],{"NC 1","NC 2"}})), #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom.2", each Text.Combine(List.Transform(_, Text.From)), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged", "Merged.1", "Custom", "Custom.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Component ID", "Custom.2", "Factor product"}), #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Grand Children"}}) in #"Renamed Columns2"
Hello @tonie_tollig
You can use this M solution which uses a recursive function...Hopefully it will work with your entire list.
Please see attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}), mytable=Table.Buffer(ChangedType), mygrandchildren={"NC 1","NC 2"}, otherchildren=List.Distinct(List.RemoveItems(mytable[Component ID 2],mygrandchildren)), myfunction=(SourceTable)=> let Mvar= each + 1, myoutput=Table.NestedJoin(SourceTable,{"Component ID 2"},SourceTable,{"Component ID"},"Step",JoinKind.LeftOuter), myexpand=Table.ExpandTableColumn(myoutput, "Step", {"Component ID 2", "Factor"}, {"Grand Children", "Factor2"}), myexpand1=Table.AddColumn(myexpand, "Factor3", each [Factor] * (if [Factor2]=null then 1 else [Factor2]), type number), myexpand2=Table.AddColumn(Table.FromColumns({myexpand1[Component ID],myexpand1[Grand Children],myexpand1[Factor3],myexpand1[Component ID 2]},{"Component ID","Component ID 2","Factor","Component ID s"}),"GC 1",each if [Component ID 2]=null then [Component ID s] else [Component ID 2]), myexpand3=Table.FromColumns({myexpand2[Component ID],myexpand2[GC 1],myexpand2[Factor]},{"Component ID","Component ID 2","Factor"}) in if List.ContainsAny(myexpand3[Component ID 2],otherchildren) then @ myfunction(myexpand3) else myexpand3, FinalTable=myfunction(mytable), #"Sorted Rows" = Table.Sort(FinalTable,{{"Component ID", Order.Ascending}, {"Factor", Order.Ascending}}) in #"Sorted Rows"
PowerBI doesn't really respect the order of the data, so you will have to work around to mimic the top-down logic of your data
the problem would be trivial if you could transform Table1 to only input measures, i.e.
Component ID | Component ID 2 | Input | Factor |
NC 3 | NC 1 | NC 1 | 14 |
NC 3 | NC 2 | NC 2 | 19 |
NC 4 | NC 2 | NC 2 | 15 |
NC 4 | NC 3 | NC 1 | 16 |
NC 4 | NC 3 | NC 2 | 16 |
NC 4 | NC 1 | NC 1 | 9 |
NC 5 | NC 2 | NC 2 | 17 |
NC 5 | NC 3 | NC 1 | 9 |
NC 5 | NC 3 | NC 2 | 9 |
NC 5 | NC 4 | NC 2 | 14 |
NC 5 | NC 4 | NC 1 | 14 |
NC 5 | NC 4 | NC 2 | 14 |
NC 5 | NC 4 | NC 1 | 14 |
in order to do that you will need recursion which is available in M. Is that a viable option for you?
I'm not sure you could achieve what you need just with DAX
I would prefer to do it in DAX because its more transparent as part of a bigger modelling project. If not possible in DAX, I have the option to do it in M.
I am still relatively new to DAX/M. Will it be possible to give me some pointers?
Tks
T
hmm, I think I misunderstood the problem, basically I thought that
NC3 = NC2+NC1
rather than
NC3= 14*NC1+19*NC2
then it was just a simple SUMX with RELATED in DAX
you could expand the list the way I showed with this function
(lst as list) => [ fnGetComponentsList = (lst as list)=> let basic = {"NC 1", "NC 2"}, tbl = Table1, col1 = "Component ID", col2 = "Component ID 2", Zip = List.Zip({Table.Column(tbl,col1), Table.Column(tbl,col2)}), SelectRelevant = List.Select(Zip, each List.Contains(lst, _{0}) or List.Contains(basic, _{0})), GetComp = List.Transform(SelectRelevant, each _{1}), Output = List.Combine({GetComp, List.Intersect({lst, basic})}) in Output, fnGetInputIDs = (y as list)=> if fnGetComponentsList(y) = y then y else @fnGetComponentsList(fnGetComponentsList(y)), Output = @fnGetInputIDs(lst) ][Output]
but it's missing the factor multiplication
you could add Factor there, but it's becoming more complex that way and I don't think that's a good solution
maybe @Greg_Deckler, @Zubair_Muhammad or @ImkeF can help you, I'm at a loss here
I believe there should be a more elegant solution
but this might work as well
The idea is to mege Child ID with Parent ID to get Grand Children and repeat this merge until there are no children other than "NC 1" and NC 2"
Please see the attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component ID 2"},#"Changed Type",{"Component ID"},"Changed Type",JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Component ID 2", "Factor"}, {"Component ID 3", "Factor3"}), #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Component ID", Order.Ascending}}), #"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Component ID 3"},#"Sorted Rows",{"Component ID"},"Sorted Rows",JoinKind.LeftOuter), #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Component ID 2", "Factor"}, {"Component ID 4", "Factor4"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Sorted Rows",{{"Factor", "Factor2"}}), #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Component ID 2", "Component ID 3", "Component ID 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Factor2", type text}, {"Factor3", type text}, {"Factor4", type text}}, "en-US"),{"Factor2", "Factor3", "Factor4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"), #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Merged", Order.Ascending}, {"Merged.1", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each List.RemoveItems(Text.Split([Merged],","),{" ",""})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.FromText(List.RemoveItems(Text.Split([Merged.1],","),{" ",""}))), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Accumulate([Custom.1],1,(state, current) => Number.From(state) * Number.From(current))), #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Factor product"}}), #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each List.Intersect({[Custom],{"NC 1","NC 2"}})), #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom.2", each Text.Combine(List.Transform(_, Text.From)), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged", "Merged.1", "Custom", "Custom.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Component ID", "Custom.2", "Factor product"}), #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Grand Children"}}) in #"Renamed Columns2"
Thank you for the effort.
It is unfortunately a very customised solution. I will eventually have an entire list of NCs with different recursive relationships which will dynamically change.
Hello @tonie_tollig
You can use this M solution which uses a recursive function...Hopefully it will work with your entire list.
Please see attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}), mytable=Table.Buffer(ChangedType), mygrandchildren={"NC 1","NC 2"}, otherchildren=List.Distinct(List.RemoveItems(mytable[Component ID 2],mygrandchildren)), myfunction=(SourceTable)=> let Mvar= each + 1, myoutput=Table.NestedJoin(SourceTable,{"Component ID 2"},SourceTable,{"Component ID"},"Step",JoinKind.LeftOuter), myexpand=Table.ExpandTableColumn(myoutput, "Step", {"Component ID 2", "Factor"}, {"Grand Children", "Factor2"}), myexpand1=Table.AddColumn(myexpand, "Factor3", each [Factor] * (if [Factor2]=null then 1 else [Factor2]), type number), myexpand2=Table.AddColumn(Table.FromColumns({myexpand1[Component ID],myexpand1[Grand Children],myexpand1[Factor3],myexpand1[Component ID 2]},{"Component ID","Component ID 2","Factor","Component ID s"}),"GC 1",each if [Component ID 2]=null then [Component ID s] else [Component ID 2]), myexpand3=Table.FromColumns({myexpand2[Component ID],myexpand2[GC 1],myexpand2[Factor]},{"Component ID","Component ID 2","Factor"}) in if List.ContainsAny(myexpand3[Component ID 2],otherchildren) then @ myfunction(myexpand3) else myexpand3, FinalTable=myfunction(mytable), #"Sorted Rows" = Table.Sort(FinalTable,{{"Component ID", Order.Ascending}, {"Factor", Order.Ascending}}) in #"Sorted Rows"
Thank you very much. I will test this!
Covering 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 |
---|---|
114 | |
105 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |