#08 | Excel & Power BI Masterclass | DAX Fundamentals, Columns, Tables, Measures & Examples

Tech-focused summary of the video (Excel + Power BI DAX fundamentals)

1) What DAX is and where it runs

• DAX = Data Analysis Expressions (framed as the “engine” of Power BI/DAX).
• DAX adds new calculated information to data already in the model (not used to describe importing from external sources).
• DAX is a library of functions/operators, similar in spirit to Excel formulas.
• The video emphasizes that DAX exists in the Microsoft SQL Server / Power BI ecosystem, and is used inside Power BI / Power Pivot modeling.

2) Core DAX concepts: output types (Columns, Measures, Tables)

The instructor highlights that DAX outputs in three main objects:

• Calculated columns Added to a table; evaluated row-by-row.

• Measures Evaluated on demand in visuals.

• Calculated tables Created from DAX expressions.

Also emphasized:

• Syntax style: specify the object/function (column/measure/table), use operators (e.g., =), and reference table + column using bracket-like notation such as table[column].

3) Calculated columns: how they’re built (and practical math examples)

The tutorial demonstrates calculated columns such as:

• Total Quantity Sold / Total Sales
  • Concept: multiply Quantity by Unit Price
• Total Cost
  • Concept: multiply Quantity by Cost
• Profit
  • Concept: Profit = Sales − Total Cost (profit can be negative)
• Profit Margin
  • Concept: Profit Margin = Profit ÷ Sales
  • Error handling guidance: use DIVIDE (instead of /) so divide-by-zero returns a safe fallback (e.g., 0).

Additional modeling/UI details:

• Calculated columns display differently than imported columns in visuals (icon/indicator and formatting behavior).
• Formatting tip: adjust decimal places and percentage formatting.

4) Row context vs filter context (the evaluation “engine”)

A central theme is evaluation context.

• Row context (“raw context”)
  • Think row-by-row calculations: each row’s computation uses its own row values.
• Filter context (“filter context”)
  • Think filter first, then compute: restrict the dataset (e.g., by category/country), then aggregate (e.g., SUM).

5) Calculated tables: building new tables for analysis

The instructor includes exercises and reasoning for calculated tables, including support for:

• Profitability analysis
• Segmentation (separate views per customer type)
• Time intelligence (e.g., MTD, YTD—promised for later lessons)
• Budgeting / forecasting tables

Behaviors taught:

• Calculated tables can be clones/replicas of existing tables using a “copy” pattern.
• Demonstrated filtering inside a calculated table:
  • Use FILTER() to output a table with only matching rows (e.g., “Canada Sales”).
  • Key idea: filter functions produce tables, so you must filter the correct table/columns—rather than attempting to filter an intermediate column output in the wrong place.

6) Use of DAX aggregations to summarize: SUMMARIZE

The video introduces SUMMARIZE (spelled “sumarize” in subtitles) to build an annual sales summary table by grouping:

• Region (or alternative groupings such as country)
• Date granularity (e.g., group by year)
• Aggregated Sales (e.g., SUM of a sales amount field)

7) Measures: benefits, evaluation behavior, and use in visuals

Key measure (“major”) characteristics:

• Measures return single results per filter context.
• They’re not stored as static physical values like columns; they’re calculated on demand (“on the fly”).
• Measures are described as lightweight and fast for report usage.
• Measures are reusable across multiple visuals (“can be placed anywhere”).

Implicit vs explicit measures:

• Implicit measures: automatically created by Power BI when numeric fields are added to a visual.
• Explicit measures: created manually with DAX.

8) Measures implemented in the exercise: totals and per-unit/per-box metrics

In a hands-on section (Chocolate dataset / AdventureWorks-like concept), the instructor builds measures including:

• Total Amount
  • Explicit measure wrapping the underlying Amount
• Total Boxes / Shipment Count
  • Conceptually: count rows
• Amount Per Shipment
  • Metric: Total Amount ÷ Shipment Count (via DIVIDE)
• Amount Per Box
  • Total Amount ÷ Total Boxes (also using DIVIDE for safety)

Visualization/UX steps:

• Add slicers (e.g., product category slider).
• Show tables/cards updating based on slicer selections.
• Build charts (column/bar/line) using measures.
• Apply Power BI formatting adjustments (labels, sizes, drill-down hierarchy: year/quarter/month).

9) Data modeling workflow (star schema emphasis)

The later part covers modeling steps:

• Import multiple tables from Excel into Power BI (e.g., Calendar, Sales, Geography, Product, Person).
• Create relationships, including:
  • Calendar[Date] linked to Sales[OrderDate] (date dimension relationship)
  • Location/geography linking concept to form a star schema
• Use Power Query steps such as removing duplicates using the correct keys.

10) Exercises and where they appear in the workflow

The video includes explicit practice items:

• Exercise: Create a Calculated Column
  • Profit, profit margin, total sales, etc.
• Exercise: Create a Calculated Table
  • A Canada-specific table via FILTER
  • An annual sales summary table via SUMMARIZE
  • Clone/draft approach for safe experimentation
• Exercise: Measures
  • Create: Total Amount, Shipment Count, Amount Per Shipment, Amount Per Box
  • Add slicers and build visuals driven by those measures

---

Main speakers / sources (as implied by subtitles)

• Professor Mustafa (main instructor)
• Professor Enas / Professor Zannas / Professor Salah El-Din (other instructors/participants)
• Engineer (often responding/confirming)
• Students/participants mentioned:
  • Mustafa / Khaled / Abdel-Razek / Abu Talal / Mohamed

Datasets/context referenced

• AdventureWorks (Microsoft sample dataset name)
• “Othom Chocolate” (YouTube-channel-inspired sample dataset referenced)