Fact vs. dimension
The fact table (Sales) holds transactions: one row per event, with numeric values and foreign keys. Dimension tables (Products, Calendar, Stores, Customers) describe things: one row per product, per date, per store. Facts are long and skinny; dimensions are short and wide. Filters live on dimensions, math lives on facts.
Why a star, not one big sheet
A single flat sheet repeats "Contoso Ltd, Electronics, North Region" on every row — bloated, slow, error-prone. The star stores each attribute once and connects via keys. Power Pivot's engine compresses columns, so 10M+ rows stay responsive in a workbook that VLOOKUP would choke on.
Relationships replace VLOOKUP
A 1-to-many relationship means filters flow from the "1" side (dimension) to the "many" side (fact). Select "Bikes" in a slicer and every Sales row for bikes is filtered — no lookup columns. Toggle a relationship off above and watch the bars flatten: no relationship, no filter propagation.
DAX in three measures
Total Sales := SUM(Sales[Amount])
Avg Sale := AVERAGE(Sales[Amount])
YoY % := DIVIDE([Total Sales] - CALCULATE([Total Sales], DATEADD(Calendar[Date],-1,YEAR)), CALCULATE([Total Sales], DATEADD(Calendar[Date],-1,YEAR)))
Measures recalculate inside whatever filter context the pivot cell provides — that's the whole magic.
Filter context
Every pivot cell asks: "which rows survive the current slicers, rows, and columns?" Then the measure runs on the survivors. CALCULATE() is DAX's power tool because it modifies that context — shifting dates for YoY, removing filters for share-of-total, and more.
The EXCEL101 stack
Power Query cleans and loads data (typed steps, refreshable). Power Pivot models it (star schema + measures). DAX computes. Dashboard design presents: one screen, top-left KPI first, slicers left, no chart junk. Master the pipeline and a monthly report becomes a one-click refresh.