Module 10 // Visual Layer

Power BI
& Tableau

> Notebooks explore. Dashboards explain. Two tools, one mission: turn modeled data into visuals the rest of the org can act on.

Why BI Tools Exist

SQL and pandas answer questions for analysts. BI tools answer questions for everyone else — finance, ops, sales, leadership. Power BI and Tableau both turn a warehouse of clean data into interactive dashboards with filters, drill-downs, and scheduled refreshes.

They share the same mental model: connect → model → measure → visualize → publish. The differences are in syntax, ecosystem, and licensing.

Microsoft

Power BI

Tight integration with the Microsoft stack — Excel, Azure, Fabric, Teams. DAX is the formula language; Power Query (M) handles ETL. Strong for enterprises already on Office 365.

  • Power Query — connect & shape
  • Data Model — relationships & measures
  • DAX — calculated columns & KPIs
  • Report View — visuals & slicers
  • Power BI Service — publish & share
Salesforce

Tableau

Best-in-class visual analytics. Drag-and-drop is faster for free-form exploration; calculated fields use Tableau's own expression language. Excellent storytelling and chart variety.

  • Data Source — connect & join
  • Worksheets — single visuals
  • Calculated Fields — LOD expressions
  • Dashboards — assemble worksheets
  • Tableau Cloud / Server — publish & share

The BI Workflow

  1. Connect — to a database, warehouse, CSV, API, or Excel file.
  2. Model — define relationships between fact and dimension tables (star schema is the gold standard).
  3. Measure — write reusable calculations (DAX measures, Tableau calculated fields).
  4. Visualize — pick the chart that answers the question, not the one that looks coolest.
  5. Interact — add filters, slicers, parameters, drill-throughs, tooltips.
  6. Publish — schedule refreshes and share dashboards via the cloud service.

Star Schema: The Foundation

Both tools love a star schema — one central fact table (events, transactions) joined to multiple dimension tables (date, product, customer, region). Avoid snowflakes and many-to-many relationships unless you really need them.

model.sql
-- FACT
sales_fact (
  sale_id       BIGINT PK,
  date_key      INT  FK -> date_dim.date_key,
  product_key   INT  FK -> product_dim.product_key,
  customer_key  INT  FK -> customer_dim.customer_key,
  region_key    INT  FK -> region_dim.region_key,
  quantity      INT,
  amount        DECIMAL(12,2)
)

-- DIMENSIONS
date_dim     (date_key, date, year, quarter, month, weekday, is_weekend)
product_dim  (product_key, sku, name, category, price)
customer_dim (customer_key, customer_id, name, segment, signup_date)
region_dim   (region_key, region, country, sub_region)

DAX vs Tableau Calculations

Both languages express the same ideas — totals, ratios, time intelligence, running sums — with different syntax. Side-by-side reference for the patterns you'll write every week.

measures.dax
-- POWER BI / DAX

Total Revenue = SUM(Orders[Amount])

Order Count = COUNTROWS(Orders)

Avg Order Value =
DIVIDE([Total Revenue], [Order Count])

Revenue PY =
CALCULATE(
  [Total Revenue],
  SAMEPERIODLASTYEAR('Date'[Date])
)

YoY % =
DIVIDE(
  [Total Revenue] - [Revenue PY],
  [Revenue PY]
)

Running Total =
CALCULATE(
  [Total Revenue],
  FILTER(
    ALLSELECTED('Date'[Date]),
    'Date'[Date] <= MAX('Date'[Date])
  )
)

% of Region Total =
DIVIDE(
  [Total Revenue],
  CALCULATE(
    [Total Revenue],
    ALLEXCEPT(Orders, Orders[Region])
  )
)
calcs.tableau
// TABLEAU CALCULATED FIELDS

// Total Revenue
SUM([Amount])

// Order Count
COUNT([Sale ID])

// Avg Order Value
SUM([Amount]) / COUNT([Sale ID])

// Revenue PY  (offset by 1 in date partition)
LOOKUP(SUM([Amount]), -1)

// YoY %
( SUM([Amount]) - LOOKUP(SUM([Amount]), -1) )
/ LOOKUP(SUM([Amount]), -1)

// Running Total
RUNNING_SUM(SUM([Amount]))

// % of Region Total — LOD expression
SUM([Amount])
/ { FIXED [Region] : SUM([Amount]) }

// Customer lifetime revenue, ignoring view filters
{ FIXED [Customer ID] : SUM([Amount]) }

Power Query (M) vs Tableau Prep

Both tools have a low-code ETL layer for cleaning before modeling. Power Query uses M; Tableau Prep uses a visual flow. Same operations either way: filter, pivot, unpivot, group, join, append.

transform.m
// Power Query M — clean a sales export
let
    Source       = Csv.Document(File.Contents("orders.csv"), [Delimiter=","]),
    Promoted     = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed        = Table.TransformColumnTypes(Promoted, {
                       {"order_date", type date},
                       {"amount",     type number},
                       {"region",     type text}
                   }),
    NoNulls      = Table.SelectRows(Typed, each [amount] <> null),
    Trimmed      = Table.TransformColumns(NoNulls, {{"region", Text.Trim}}),
    AddedYear    = Table.AddColumn(Trimmed, "year", each Date.Year([order_date]), Int64.Type)
in
    AddedYear

🧪 Hands-On Lab: Build Your First Sales Dashboard

Time to wire it all together. You'll load a small star schema, model the relationships, write three measures, and assemble a one-page executive dashboard. Same lab works in both Power BI Desktop and Tableau Public — pick your tool.

Estimated time: 45–60 minutes. Prerequisites: Power BI Desktop (free) or Tableau Public (free) installed.

Step 1 — Download the Sample Data

Save each block below as a CSV file in a folder called bi-lab/. These four files form a tiny but realistic star schema: one fact table (sales) and three dimensions (date, product, region).

sales_fact.csv
sale_id,date_key,product_key,region_key,quantity,amount
1001,20250102,P01,R_EU,2,240.00
1002,20250102,P03,R_US,1,89.50
1003,20250103,P02,R_EU,4,180.00
1004,20250104,P01,R_APAC,1,120.00
1005,20250105,P04,R_US,3,615.00
1006,20250107,P02,R_EU,2,90.00
1007,20250108,P05,R_US,1,980.00
1008,20250109,P03,R_APAC,5,447.50
1009,20250110,P01,R_EU,1,120.00
1010,20250112,P04,R_US,2,410.00
1011,20250113,P02,R_EU,3,135.00
1012,20250115,P05,R_APAC,1,980.00
1013,20250118,P03,R_US,4,358.00
1014,20250120,P01,R_EU,2,240.00
1015,20250122,P04,R_APAC,1,205.00
1016,20250125,P02,R_US,6,270.00
1017,20250128,P05,R_EU,1,980.00
1018,20250130,P03,R_APAC,2,179.00
1019,20250202,P01,R_US,3,360.00
1020,20250205,P04,R_EU,2,410.00
date_dim.csv
date_key,date,year,quarter,month,month_name,weekday,is_weekend
20250102,2025-01-02,2025,Q1,1,January,Thursday,No
20250103,2025-01-03,2025,Q1,1,January,Friday,No
20250104,2025-01-04,2025,Q1,1,January,Saturday,Yes
20250105,2025-01-05,2025,Q1,1,January,Sunday,Yes
20250107,2025-01-07,2025,Q1,1,January,Tuesday,No
20250108,2025-01-08,2025,Q1,1,January,Wednesday,No
20250109,2025-01-09,2025,Q1,1,January,Thursday,No
20250110,2025-01-10,2025,Q1,1,January,Friday,No
20250112,2025-01-12,2025,Q1,1,January,Sunday,Yes
20250113,2025-01-13,2025,Q1,1,January,Monday,No
20250115,2025-01-15,2025,Q1,1,January,Wednesday,No
20250118,2025-01-18,2025,Q1,1,January,Saturday,Yes
20250120,2025-01-20,2025,Q1,1,January,Monday,No
20250122,2025-01-22,2025,Q1,1,January,Wednesday,No
20250125,2025-01-25,2025,Q1,1,January,Saturday,Yes
20250128,2025-01-28,2025,Q1,1,January,Tuesday,No
20250130,2025-01-30,2025,Q1,1,January,Thursday,No
20250202,2025-02-02,2025,Q1,2,February,Sunday,Yes
20250205,2025-02-05,2025,Q1,2,February,Wednesday,No
product_dim.csv
product_key,sku,name,category,unit_price
P01,SKU-001,Aurora Headphones,Audio,120.00
P02,SKU-002,Pulse Cable,Accessories,45.00
P03,SKU-003,Nova Speaker,Audio,89.50
P04,SKU-004,Vertex Keyboard,Peripherals,205.00
P05,SKU-005,Quantum Monitor,Displays,980.00
region_dim.csv
region_key,region,country,sub_region
R_EU,EU,Germany,DACH
R_US,US,United States,North America
R_APAC,APAC,Japan,East Asia

Step 2 — Load the Data

Power BI
  1. Open Power BI Desktop → Home → Get Data → Text/CSV.
  2. Select sales_fact.csv → Transform Data.
  3. In Power Query, verify column types (integers, decimals, dates).
  4. Repeat for the three dimension files.
  5. Click Close & Apply.
Tableau
  1. Open Tableau → Connect → Text File → pick sales_fact.csv.
  2. Drag the other three CSVs onto the canvas.
  3. Tableau auto-detects join keys — verify each link.
  4. Switch to a Sheet tab to start building.

Step 3 — Model the Relationships

Build a star schema: sales_fact in the center, dimensions around it. All relationships are many-to-one, single-direction filter (dim → fact).

relationships.txt
sales_fact[date_key]    →  date_dim[date_key]      (many-to-one)
sales_fact[product_key] →  product_dim[product_key] (many-to-one)
sales_fact[region_key]  →  region_dim[region_key]   (many-to-one)

✓ All single-direction (dim filters fact, not the reverse)
✓ Mark date_dim as the official Date Table (Power BI: right-click → Mark as date table)

Step 4 — Write the Core Measures

Three measures cover 80% of executive questions: headline number, average, and growth.

lab.dax
-- Power BI / DAX

Total Revenue =
SUM(sales_fact[amount])

Avg Order Value =
DIVIDE(
  [Total Revenue],
  COUNTROWS(sales_fact)
)

Revenue MoM % =
VAR Curr = [Total Revenue]
VAR Prev =
  CALCULATE(
    [Total Revenue],
    DATEADD(date_dim[date], -1, MONTH)
  )
RETURN DIVIDE(Curr - Prev, Prev)
lab.tableau
// Tableau Calculated Fields

// Total Revenue
SUM([amount])

// Avg Order Value
SUM([amount]) / COUNT([sale_id])

// Revenue MoM %  (table calc, compute by Month)
( ZN(SUM([amount]))
  - LOOKUP(ZN(SUM([amount])), -1) )
/ ABS(LOOKUP(ZN(SUM([amount])), -1))

Step 5 — Build the Dashboard (4 Visuals)

Follow this layout. Top row = KPIs, bottom row = breakdowns. Z-pattern reading order.

KPI 1
Total Revenue
Card visual
KPI 2
Avg Order Value
Card visual
KPI 3
MoM Growth %
Card with arrow
CHART 1
Revenue Trend
Line chart — date on X, Total Revenue on Y
CHART 2
Revenue by Region & Category
Stacked bar — region on Y, category as color

Visual-by-visual instructions

  1. KPI Cards (top row) — Power BI: drag Total Revenue, Avg Order Value, Revenue MoM % each to a Card visual. Tableau: create three single-number worksheets, then arrange on a Dashboard.
  2. Revenue Trend (bottom-left) — Line chart. X-axis: date_dim[date]. Y-axis: Total Revenue. Format: smooth line, hide gridlines.
  3. Revenue by Region & Category (bottom-right) — Stacked bar. Axis: region_dim[region]. Values: Total Revenue. Legend/Color: product_dim[category]. Sort descending by total.

Step 6 — Add Interactivity

  • Slicer / Filter: add date_dim[month_name] as a slicer (Power BI) or quick filter (Tableau). All visuals should respond.
  • Cross-filter: clicking a region in the bar chart should filter the line chart. In Power BI it's automatic; in Tableau set the dashboard action to Filter on Select.
  • Tooltip: add Avg Order Value to the line chart tooltip for context on hover.

Step 7 — Publish & Share

  • Power BI: Home → Publish → choose a workspace in Power BI Service. Set a daily refresh schedule.
  • Tableau: Server → Publish Workbook to Tableau Public or Cloud. Copy the share link.

✅ Lab Success Checklist

  • All four CSVs loaded with correct data types
  • Star schema with three many-to-one relationships
  • Three measures evaluate without errors
  • Dashboard renders three KPI cards + two charts
  • Month slicer filters every visual
  • Clicking a region cross-filters the trend line
  • Published to the cloud with a working share link

🚀 Stretch Goals

  • Add a Top 3 Products table sorted by revenue.
  • Build a YoY % measure once you have a second year of data.
  • Create a map visual using region_dim[country].
  • Add a parameter to switch the headline KPI between Revenue, Quantity, and AOV.
  • Apply a custom theme matching your company brand colors.

Choosing the Right Chart

The chart should serve the question. Match the visual to what the reader needs to decide.

  • Trend over time → line chart (or area for cumulative).
  • Compare categories → bar chart, sorted descending.
  • Part of a whole → stacked bar (almost never a pie).
  • Distribution → histogram or box plot.
  • Two numeric variables → scatter plot.
  • Geographic → filled map or symbol map.
  • Single KPI → big number with sparkline and YoY delta.

Dashboard Design Principles

  • One question per dashboard — "How is revenue trending?" not "everything about sales".
  • Top-left = most important — readers scan in a Z-pattern.
  • KPIs on top, detail below — headline numbers, then the breakdown.
  • Limit colors — color encodes meaning, not decoration. 2–3 hues max.
  • Consistent date grain — don't mix daily and monthly without labels.
  • Whitespace is free — cramming kills comprehension.
  • Mobile preview — half your audience opens it on a phone.

Performance Tips

  • Import > DirectQuery when the dataset fits in memory — much faster.
  • Reduce cardinality — drop unused columns, especially high-cardinality text and timestamps to the second.
  • Aggregate at the source — let SQL do the heavy lifting; pull pre-aggregated fact tables when possible.
  • Avoid bidirectional filters in DAX unless absolutely required — they slow query plans.
  • Use measures, not calculated columns — measures evaluate at query time on aggregated data.

Choosing Between Them

  • Pick Power BI if your org runs on Microsoft 365 / Azure / Fabric, your team knows Excel formulas, and per-user licensing cost matters.
  • Pick Tableau if visual exploration speed is the priority, you need advanced chart types out of the box, or you're already on Salesforce.
  • Both connect to the same warehouses (Snowflake, BigQuery, Postgres, Redshift). Your SQL and modeling skills transfer cleanly between them.

Learning Path

  1. Master one tool deeply before learning the other — concepts transfer faster than syntax.
  2. Build a star schema from a real dataset (Kaggle, your own data).
  3. Recreate a published dashboard you admire — reverse-engineer it.
  4. Learn the formula language: DAX (Power BI) or Calculated Fields + LOD (Tableau).
  5. Publish to the cloud service and set a refresh schedule.
  6. Practice removing things from your dashboard. Restraint is the hardest skill.