Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dario729
Frequent Visitor

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

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:

 

Sin título.png

 

Thanks in advance

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

@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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.