8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.11 DAX Calculated Columns
In short: A calculated column is created in the model with DAX.
A calculated column is created in the model with DAX. It is computed row by row at refresh and stored.
Steps in Power BI
- Go to Table view (or Report view) and select the table in the Data pane, e.g. Orders.
- Table tools › New column (or right-click the table › New column).
- Type the formula in the formula bar, e.g.
Net Amount = Orders[Amount] - Orders[Discount]› press Enter. - Set the format in Column tools (Data type, Format, ₹ currency, decimal places, Data category, Sort by column).
Net Amount = Orders[Amount] - Orders[Discount]
Category = RELATED(Product[Category]) // Orders is on the many side
Store City = RELATED(DarkStore[City])
Delivery Speed Band =
SWITCH(
TRUE(),
ISBLANK(Orders[Delivery Time Mins]), "Not delivered",
Orders[Delivery Time Mins] <= 10, "0-10 min",
Orders[Delivery Time Mins] <= 15, "11-15 min",
Orders[Delivery Time Mins] <= 30, "16-30 min",
"30+ min"
)
Delivery Speed Band Sort =
SWITCH(Orders[Delivery Speed Band], "0-10 min", 1, "11-15 min", 2, "16-30 min", 3, "30+ min", 4, 5)
Is Late = IF(Orders[Delivery Time Mins] > 15, "Late", "On time")
Order Value Band =
VAR v = Orders[Amount]
RETURN IF(v < 199, "Below ₹199", IF(v < 499, "₹199–498", "₹499 and above"))
-- date parts (in the Date table)
Month Name = FORMAT('Date'[Date], "mmm")
Month Number = MONTH('Date'[Date])
Year Month = FORMAT('Date'[Date], "yyyy-mm")
Is Weekend = IF(WEEKDAY('Date'[Date], 2) >= 6, TRUE(), FALSE())
-- one-side table: count related rows
Customer Orders = CALCULATE(DISTINCTCOUNT(Orders[Order ID]))
Friends, then select Delivery Speed Band › Column tools › Sort by column › Delivery Speed Band Sort, so the bands appear in logical order in charts.
Why CALCULATE in Customer Orders?
In a calculated column there is a row context (ओळ संदर्भ – सध्या कोणत्या ओळीवर हिशोब चालू आहे) but no filter context (गाळणी संदर्भ – सध्या कोणते फिल्टर लागू आहेत). CALCULATE performs context transition. It turns the current Customer row into a filter on Orders, so each customer gets their own count (Module 13.3).
Ravindra Bagale's Tip
Remember one thing: writing SUM(Orders[Amount]) in a calculated column gives the grand total on every row, and using RELATED from the one side (in DarkStore) fails. Calculated columns work row by row: use RELATED from the many side, and RELATEDTABLE or CALCULATE from the one side. Don't add dozens of calculated columns to a large fact table, because each one costs memory. Never forget this.
Ravindra Bagale's Tip – मराठी
एक गोष्ट लक्षात ठेवा: calculated column मध्ये SUM(Orders[Amount]) लिहिलं तर प्रत्येक row वर grand total येतो, आणि one side कडून (DarkStore मध्ये) RELATED वापरलं तर fail होतं. Calculated columns row by row चालतात: many side कडून RELATED, आणि one side कडून RELATEDTABLE किंवा CALCULATE वापरा. मोठ्या fact table मध्ये डझनभर calculated columns जोडू नका, कारण प्रत्येक column memory खातो. हे अजिबात विसरू नका.
Ravindra Bagale's Tip – हिंदी
एक बात याद रखो: calculated column में SUM(Orders[Amount]) लिखने से हर row पर grand total आता है, और one side से (DarkStore में) RELATED लगाने पर fail होता है. Calculated columns row by row चलते हैं: many side से RELATED, और one side से RELATEDTABLE या CALCULATE इस्तेमाल करो. बड़ी fact table में दर्जनों calculated columns मत जोड़ो, क्योंकि हर एक memory खाता है. यह बिल्कुल मत भूलना.
Practice task
Create Day Part in Orders from Order Hour: 6–11 "Morning", 12–16 "Afternoon", 17–21 "Evening", else "Night". Use SWITCH(TRUE()) and add a sort column.
Checkpoint — calculated column
After creating Net Amount, sort the table by Net Amount and confirm four fictional rows match Amount − Discount by hand. If every row shows the same huge total, you used SUM without context transition.
Fix common mistakes (8.11)
| Wrong result | Reason | Correction |
|---|---|---|
| RELATED fails from DarkStore to Orders | RELATED walks many, then one from the many side | From one side use RELATEDTABLE / CALCULATE |
SUM(Amount) in column = grand total |
No filter context | Use row columns or CALCULATE([Total Sales]) |
About this data: This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Think like a professional (8.11)
Prefer Power Query for static bands when no relationship is required. Keep fact tables thin. Interview: explain why CALCULATE appears in Customer Orders on the Customer table (context transition).
Loading DAX practice tasks...
- Line value (Easy): Calculated column on Orders: Line Value = Qty × UnitPrice.
- Net value after discount (Easy): Calculated column on Orders: Net Value = Qty × UnitPrice − Discount.
- Category on each line (Easy): Calculated column on Orders: Category = the Category of the product on the row.
- Order year (Easy): Calculated column on Orders: Order Year = the year of OrderDate.
- Line cost (Medium): Calculated column on Orders: Line Cost = Qty × the product CostPrice.
- Line profit (Medium): Calculated column on Orders: Line Profit = Qty × (UnitPrice − product CostPrice).
- Big or small basket (Medium): Calculated column on Orders: Basket = "Big" when Qty is 3 or more, else "Small".
- Store label (Medium): Calculated column on Store: Store Label = StoreName, a space, a dash, a space, then City. Example: Kothrud Hub - Pune.
- Speed band (Hard): Calculated column on Orders: Speed = "Cancelled" if DeliveryMins is blank, "Fast" if 10 or less, else "Normal".
- Margin percent on Product (Hard): Calculated column on Product: Margin % = (MRP − CostPrice) ÷ MRP. Use DIVIDE.
- Order lines per product (Hard): Calculated column on Product: Lines = number of Orders rows for that product.
Revision card