9. Lighthouse

Level 7 · Fix it: every product is high demand

From Daniel Brooks, Inventory Planner

“Someone added a Demand Band column to products: High demand at 500 units ordered or more, Normal below. Every one of our 120 products says High demand, including a dresser we sold 8 of. Can you fix the column so each product is judged on its own units?”

Your goal

The starter holds the broken calculated column on products. Fix Demand Band so each product gets "High demand" when its own units ordered (the sum of order_items[quantity] for that product, every order status) are 500 or more, and "Normal" otherwise. The matrix groups the Products count by the new column.

  • Add the column Demand Band to products
  • The Total row right too

Your calculated column

DAX
Visual
A matrix: rows = your new column on products, values = [Products]
Measures in the report
Products = COUNTROWS(products)
Data pane: tables and columns
  • order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
  • orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
  • products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
  • customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
  • returns return_id, order_item_id, returned_on, reason, refund_amount
  • Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

    VisualData view of products: your column appears in the visual once you press Check
    product_idnamecategorysubcategoryunit_priceunit_cost
    101Vale Velvet LoveseatFurnitureSofas1,659
    102Alder Oak Coffee TableFurnitureTables919503.5
    103Orchard Rattan Dining ChairFurnitureChairs789429.87
    104Upland Oak BookcaseFurnitureStorage429214.7
    105Avery Leather Bed FrameFurnitureBeds449232.7
    106Northfield Rattan SectionalFurnitureSofas22979.35
    107Birch Teak Side TableFurnitureTables16954.61
    108Ashford Leather Dining ChairFurnitureChairs369139.71

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    This bug has a signature: the same number on every row. Behind it sits the distinction that causes most DAX confusion, between row context and filter context.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your calculated column on every row of products and then groups the product count by its values. In Power BI Desktop you would select the column in Data view, edit its formula in the formula bar, or create one with Table tools → New column. A calculated column is computed at refresh and stored in the model.

    Two contexts, two jobs

    • Filter context is the set of filters that decide which rows are visible: a matrix row, a slicer, a CALCULATE filter. Aggregations such as SUM, COUNTROWS and AVERAGE obey it.
    • Row context means “there is a current row”. It exists in calculated columns and inside iterators such as SUMX and FILTER. It lets you read products[unit_price] for this row. It does not filter anything.

    The broken column mixes them up:

    Demand Band = IF(SUM(order_items[quantity]) >= 500, "High demand", "Normal")

    The column runs once per product with a row context. There is no filter context at all, so SUM sees all 30,000 order lines and returns 60,535 on every row. Every product passes 500.

    Context transition

    CALCULATE converts the current row into an equivalent filter. That is context transition:

    Demand Band = IF(CALCULATE(SUM(order_items[quantity])) >= 500, "High demand", "Normal")

    For the dresser’s row, CALCULATE filters products to the dresser, the relationship carries that filter to order_items, and the sum is 8. Every measure call does the same, because a measure is wrapped in an invisible CALCULATE: with a measure Units Ordered = SUM(order_items[quantity]), the column IF([Units Ordered] >= 500, …) would also work.

    The other way to write it follows the relationship by hand:

    Demand Band = IF(SUMX(RELATEDTABLE(order_items), order_items[quantity]) >= 500, "High demand", "Normal")

    RELATEDTABLE returns the order lines of the current product.

    The mirror-image bug

    In a measure, writing a bare column such as products[unit_price] fails, because a measure has filter context but no current row: which product’s price? A measure needs an aggregation (SUM, MAX) or an iterator. Columns have a row and no filters; measures have filters and no row.

    Common mistakes

    • Fixing the threshold instead of the formula. Raising 500 to 60,000 hides the bug; every product still gets the same number.
    • Calculated columns for numbers people slice. A column is computed once, at refresh. A slicer on year does not change it. Use a column for a fixed label like this band, a measure for totals.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.