Lighthouse

Level 3 · Quantity bands with SWITCH(TRUE())

From Nicole Adams, Fulfillment Manager

“Lines with lots of units go on a pallet, small ones go in a box. Can you band every order line: under 3 units is Small, 3 to 5 is Medium, more than 5 is Large? And some lines have 0 or negative quantities, which are data errors. Put those in their own Check band so I can send them to IT.”

Your goal

Add a calculated column called Quantity Band to order_items: "Check" when quantity is 0 or less, "Small" when it is below 3, "Medium" from 3 to 5, and "Large" above 5. The matrix groups the given measures Order Lines and Units by your column.

Add the column Quantity Band to order_itemsnot done yet
The Total row right toonot done yet

Your calculated column

DAX

Visual: A matrix: rows = your new column on order_items, values = [Order Lines], [Units]
Measures in the report: Order Lines = COUNTROWS(order_items)Units = SUM(order_items[quantity])

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…

Reference solution

VisualData view of order_items: your column appears in the visual once you press Check

The visual before your answer
order_item_id	order_id	product_id	quantity	unit_price	discount_pct
1	1,001	121	9	31.99	0
2	1,001	154	3	11.99	0
3	1,002	154	4	11.99	0
4	1,002	194	1	149	0
5	1,002	193	2	19.99	0
6	1,002	196	2	25.99	0
7	1,002	156	1	53.99	0
8	1,003	134	1	92.99	10

Mote checks your result

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

The lesson

Two bands need one IF. Four bands need either three IFs nested inside each other, or a SWITCH that reads like a list. Both work; the list is easier to read and to change.

What you are working in

This is a practice report built for this site; Power BI Desktop runs only on Windows. Your column is added to order_items, and the matrix groups Order Lines (COUNTROWS(order_items)) and Units (SUM(order_items[quantity])) by it.

In Power BI Desktop: select order_items in the Data pane, Table tools → New column, type the formula (Shift+Enter starts a new line in the formula bar), and press Enter.

Nested IF

Quantity Band =
IF(order_items[quantity] <= 0, "Check",
    IF(order_items[quantity] < 3, "Small",
        IF(order_items[quantity] <= 5, "Medium", "Large")))

Each IF handles one test and passes the rest to the next one. It works, but by the fourth band the brackets are hard to count.

SWITCH(TRUE(), …)

SWITCH compares one value against a list of cases. Give it TRUE() as the value, and each case becomes a test: the first test that equals TRUE wins.

Quantity Band =
SWITCH(
    TRUE(),
    order_items[quantity] <= 0, "Check",
    order_items[quantity] < 3,  "Small",
    order_items[quantity] <= 5, "Medium",
    "Large"
)

The last argument, without a test, is the default: whatever passed no test. Here that is every quantity above 5.

Order is the logic

Because the first TRUE test wins, each test only sees the rows the earlier ones let through. The < 3 test doesn’t need to say “and above 0”, because rows at 0 or below already have their label. Put the tests in the wrong order and the bands change silently: test < 3 first and every negative quantity becomes Small.

Read the ticket’s boundaries carefully too. “3 to 5 is Medium” includes both 3 and 5, so the test is <= 5, and “more than 5” is the default.

Bad rows deserve their own band

Harbor & Pine’s order lines include a handful of quantities of 0 or below, left by a broken import. A Check band keeps them visible instead of letting them hide inside Small, so someone can fix them at the source. Notice that the Units total in the matrix includes them, negatives and all.

Common mistakes

The bad-row test last. Too late: Small has already taken those rows.
< 5 for “3 to 5”. Lines of exactly 5 units would become Large. (No line here has 5, but the next import might.)
Missing default. Without a final value, rows that pass no test get a blank band.

Level 3 · Quantity bands with SWITCH(TRUE())

Your goal

Your calculated column

Reference solution

Mote checks your result

What Nicole gets, and why

The lesson

What you are working in

Nested IF

SWITCH(TRUE(), …)

Order is the logic

Bad rows deserve their own band

Common mistakes

Lantern lit!

Level 3 · Quantity bands with SWITCH(TRUE())

The request

Your goal

Your calculated column

Reference solution

Mote checks your result

What Nicole gets, and why

The lesson

What you are working in

Nested IF

SWITCH(TRUE(), …)

Order is the logic

Bad rows deserve their own band

Common mistakes

Cookie choices