Forum Discussion
Solved
Dynamic column creation
Hi All, Is there any way to dynamically create calculated columns for each of the values stored in the list/table. I have a list/table with values A,B,C, I need to create columns - A with filter...
Hi Kavitha10 ,
You could do it using dax expression:
Measure = CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Date]>=MAX('Table2'[Start Date])&&'Table1'[Date]<=MAX('Table2'[End Date])&&'Table1'[Company Name]=MAX('Table1'[Company Name])))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
In additon to my earlier response. Completely solving the problem in M is more easy than constructing the extended table with empty columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUNdQ1MjAyBDENDJRidaDCFrpGMHEjJHEjY4S4MVTcCaTeFGGOCZK4ua4xTNgUKuwMMsYIYYwZSDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Date = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
#"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Grouped Rows" = Table.Group(#"Inserted End of Month", {"Company Name", "Start of Month", "End of Month"}, {{"Total Amount", each List.Sum([Amount]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Company Name"]), "Company Name", "Total Amount", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A", "B", "C"})
in
#"Replaced Value"