topic Re: Create a new table from another and add new calculations from rows to columns in DAX Commands and Tips

Create a new table from another and add new calculations from rows to columns

Anonymous — Fri, 21 Oct 2022 16:01:16 GMT

Hi, I need to create a new set of measures, but they depend of other values in a preexisting source of data. In SQL I managed this by adding CASE sentences to transform a row into a column and then I operated the columns, but in DAX I'm not sure about what is the best option, so I'm guessing that is through a new table with the SWITCH function involved. Let me explain my need:

Thanks in advance

Re: Create a new table from another and add new calculations from rows to columns

Greg_Deckler — Fri, 21 Oct 2022 17:28:38 GMT

Anonymous You could do this in PQ:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg41VNJRAmMDA6VYHZiIEQjDRYygasxQREDYBC5iDFVjChKJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customers = _t, Labels = _t, Inputs = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customers", type text}, {"Labels", Int64.Type}, {"Inputs", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Labels", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Labels", type text}}, "en-US")[Labels]), "Labels", "Inputs", List.Sum), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Label1"}, {"2", "Label2"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Label2"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Output", each [Label1] - [Label2]) in #"Added Custom"

and here is a DAX solution as well:

Table3 = ADDCOLUMNS( ADDCOLUMNS( DISTINCT('Table'[Customers]), "Label1", SUMX(FILTER('Table',[Labels] = 1 && [Customers] = EARLIER([Customers])),[Inputs]), "Label2", SUMX(FILTER('Table',[Labels] = 2 && [Customers] = EARLIER([Customers])),[Inputs]) ), "Output",[Label1] - [Label2] )

PBIX is attached below signature.

Re: Create a new table from another and add new calculations from rows to columns

tamerj1 — Fri, 21 Oct 2022 19:00:53 GMT

@Hi Anonymous

please try

Desired = GENERATE ( VALUES ( Source[Customers] ), VAR Label1 = CALCULATE ( SUM ( Source[Inputs] ), Source[Labels] = 1 ) VAR Label2 = CALCULATE ( SUM ( Source[Inputs] ), Source[Labels] = 2 ) VAR Outputs = Label1 - Label2 RETURN ROW ( "Label1", Label1, "Label2", Label2, "Outputs", Outputs ) )