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

[Dario729]

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

[tamerj1 · Accepted solution]

@Hi @Dario729 

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 )
)

[Greg_Deckler]

@Dario729 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.