Forum Discussion
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 rel. order# field. What DAX is best?
And then I thought I could make a new table with a line grouping order#, earliest time, latest time, profit and product so I can make a little gantt chart showing when I make the most money with different products.
Thoughts?
Thanks for your help.
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
2 Replies
- Hi kylee_anne,
If this is purely a data-cleaning step, the “best” place is actually Power Query before the data hits the model. But if you want a quick model-side fix, a calculated column approach is good patern here.🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster. 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
