Introduction
Imagine, your company hired a Chief Happiness Officer (CHO). Their job: keep the office running smoothly. Coffee stocked, ping-pong table used, no-show meetings under control.
They ask for a dashboard. Your first instinct? KPI cards. One per metric, previous value, delta, nice arrow. Clean. Visual. The CHO loves it.
Until there are more metrics. Suddenly “glanceable” means scanning every single card to find the one that’s having an issue. The wall that looked like a solution starts feeling like a wall.
So you reach for a matrix. Tighter, more compact. And it’s better – everything on one screen, status icons lined up in a column. But the rows are still in arbitrary order, and a warning in row 7 is just as easy to miss as a card buried in the corner.
This week you’ll fix that: worst rows first, a headline that counts what’s broken, and a “new” flag for problems that just appeared.
Dataset
The dataset is a single Excel file found on Github here: link
Requirements
- Load and explore the data model
KPI Config has one row per KPI: Category, KPI Name, Target, Unit, Threshold_Red, Threshold_Amber, Trend_Good. Target and Unit are display only. Trend_Good tells the status measure which direction hurts. “Up” means higher is better, so falling below a threshold is bad. “Down” means lower is better, so rising above it is bad. The fact table has one row per KPI per reading, with Date and Value. For this quick challenge, there is no date dimension.
- Build the foundation measures
Latest Value shows each KPI’s readings on its most recent date. Previous Value finds the date just before that and does the same.
Build the Status measure
Use SWITCH() on the Trend_Good direction, comparing [Latest Value] to the config table thresholds via SELECTEDVALUE. Returns “Critical”, “Warning”, or “OK”. Add the icons via conditional formatting or a dedicated icon measure with emojis.
- Build the matrix
Add a matrix visual with Category and KPI Name on rows, and Target, Latest Value, Status with standard icons and a sparkline as values. This is your “before” – functional but unsorted and too crowded.
- Build Severity and sort the matrix
Use SWITCH([Status], “Critical”, 2, “Warning”, 1, 0), so that status becomes a number. Sort the matrix by severity descending.
- Build Severity Prev and Severity Is New
Severity Prev applies the same threshold logic to [Previous Value]. Severity Is New is just IF([Severity] > [Severity Prev], “new”, BLANK()). If a KPI’s score got worse since the last snapshot, it gets flagged.
- Build the Exception Headline measure & panel
One measure for the summary panel. Count the KPIs by severity and the number of worsened metrics. Return “All clear” if all three are zero, otherwise for example “0 Critical, 4 Warning (2 new ⚑)”. Btw, the flag is UNICHAR(9873).
The headline says how many things are broken. The panel says which. A table visual beside the matrix: KPI Name, Severity Is New (as a flag icon), and the sort measure. Visual filter on the table, greater than or equal to 1, so the OK rows drop out. Sort descending. Then bind the visual’s title to [Exception Headline] via fx.
- Bonus: Add an informative tooltip
Build a tooltip page with a bar chart of the KPI’s daily values. Use a bar color measure to color bars by status tier: dark for OK, pink for Warning, red for Critical. Add reference lines for both thresholds. Attach the tooltip page to the matrix rows. Now hovering over any row shows the full history: where the value has been, where the thresholds are, and whether things are trending in the right direction.
Share
After you finish your workout, share on BlueSky or LinkedIn using the hashtags #WOW2026 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko (on BlueSky). And tag me, Diana Ackermann on LinkedIn.
Solution
coming soon