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]))
Mitrano, 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.