Ravindra BagaleCourses & study guides

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.11 DAX Calculated Columns

By Last updated:

A calculated column is created in the model with DAX. It is computed row by row at refresh and stored.

Steps in Power BI

  1. Go to Table view (or Report view) and select the table in the Data pane, e.g. Orders.
  2. Table tools › New column (or right-click the table › New column).
  3. Type the formula in the formula bar, e.g. Net Amount = Orders[Amount] - Orders[Discount] › press Enter.
  4. 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

Ek goshta lakshat theva: 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. Lakshat theva, calculated columns work row by row: use RELATED from the many side, RELATEDTABLE or CALCULATE from the one side. Don't add dozens of calculated columns to a large fact table, karan each one costs memory. Bilkul visru naka.

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.