Tiers with SWITCH(TRUE())
SWITCH(TRUE(), test1, result1, test2, result2, else) returns the result of the first test that is true. It is the readable way to write nested IFs, and the order of the tests matters.
Put the strictest test first. If “at least 10,000” came before “at least 45,000”, every large customer would stop at the first match.
Your task
Write Discount Tier: 10% for a Sales Amount of at least 45,000, 5% for at least 10,000, otherwise 0.
About this exercise
You practise banding a measure with SWITCH and TRUE. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Discount Tier, fills a Power BI-style matrix grouped by Customer[Customer]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount. Reference them in square brackets, for example [Sales Amount].
Where you start
Discount Tier =The result to build
| Customer | Discount Tier |
|---|---|
| Anna Berger | 5.0% |
| Cycles Marchand | 10.0% |
| Emma Clarke | 0.0% |
| Fietsen De Vries | 5.0% |
| Jonas Weber | 5.0% |
| Léa Moreau | 0.0% |
| Northside Bikes | 10.0% |
| Oliver Hughes | 0.0% |
| Sanne Bakker | 0.0% |
| Velo Haus | 10.0% |
| Total | 10.0% |
Last updated 10 November 2026
Opening the exercise…