Forum Discussion

kylee_anne's avatar
kylee_anne
Icon for Helper II rankHelper II
2 months ago
Solved

DAX to use

Hi DAX experts, I'm using Power BI to help with my trading metrics so I need to modify data.  If the trade type is a close trade, I need to replace what's in the Order # column with the data in the ...
Need Help
  • Rupa01's avatar
    Rupa01
    2 months ago

    Hi kylee_anne, 

    You can't directly replace or overwrite an existing column using DAX. Instead, you will need to create a new calculated column and use that in your model/visuals.
    Final Order # =
IF (
    'Table'[TYPE] = "Close Trade"
        && NOT ISBLANK ( 'Table'[REL. ORDER#] )
        && 'Table'[REL. ORDER#] <> "-",
    'Table'[REL. ORDER#],
    'Table'[ORDER#]
)

    Alternative - Power Query - Recommended if you want to truly replace the column.

    Create a new Column ORDER#_new as below, then remove original ORDER# column and rename the new column to ORDER#

    if [TYPE] = "Close Trade"
   and [#"REL. ORDER#"] <> null
   and Text.Trim([#"REL. ORDER#"]) <> ""
   and Text.Trim([#"REL. ORDER#"]) <> "-"
then [#"REL. ORDER#"]
else [#"ORDER#"]

     

    For creating a Gantt chart -

    Create a summarized DAX table as below - 

    Order Summary =
SUMMARIZE (
    Trades,
    Trades[ORDER#],
    Trades[PRODUCT],
    "Start Time", MIN ( Trades[DATE/TIME] ),
    "End Time", MAX ( Trades[DATE/TIME] ),
    "Profit", SUM ( Trades[PROFIT] )
)

    Alternative - Power Query - Recommended for creating a summarized table

    Duplicate the Main table and Group by ORDER# and PRODUCT. Than, add below aggregations -
    Start Time = Min of DATE/TIME
    End Time = Max of DATE/TIME
    Profit = Sum of PROFIT

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer
Rupa01's avatar
Rupa01
Icon for Solution Sage rankSolution Sage
2 months ago

Hi kylee_anne, 

You can't directly replace or overwrite an existing column using DAX. Instead, you will need to create a new calculated column and use that in your model/visuals.
Final Order # =
IF (
    'Table'[TYPE] = "Close Trade"
        && NOT ISBLANK ( 'Table'[REL. ORDER#] )
        && 'Table'[REL. ORDER#] <> "-",
    'Table'[REL. ORDER#],
    'Table'[ORDER#]
)

Alternative - Power Query - Recommended if you want to truly replace the column.

Create a new Column ORDER#_new as below, then remove original ORDER# column and rename the new column to ORDER#

if [TYPE] = "Close Trade"
   and [#"REL. ORDER#"] <> null
   and Text.Trim([#"REL. ORDER#"]) <> ""
   and Text.Trim([#"REL. ORDER#"]) <> "-"
then [#"REL. ORDER#"]
else [#"ORDER#"]

 

For creating a Gantt chart -

Create a summarized DAX table as below - 

Order Summary =
SUMMARIZE (
    Trades,
    Trades[ORDER#],
    Trades[PRODUCT],
    "Start Time", MIN ( Trades[DATE/TIME] ),
    "End Time", MAX ( Trades[DATE/TIME] ),
    "Profit", SUM ( Trades[PROFIT] )
)

Alternative - Power Query - Recommended for creating a summarized table

Duplicate the Main table and Group by ORDER# and PRODUCT. Than, add below aggregations -
Start Time = Min of DATE/TIME
End Time = Max of DATE/TIME
Profit = Sum of PROFIT

 

💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer