Power BI Data Modeling and DAX Fundamentals

A chart that sums up a column anyone can build. A metric that calculates year-over-year growth, adjusts for returns, compares actual to target, and updates based on whatever the user filters — that requires data modeling and DAX. This topic teaches you how to structure multiple tables into a proper data model and how to write DAX formulas that produce the intelligent, dynamic calculations that make Power BI reports genuinely useful.

What Is a Data Model?

A data model defines how multiple tables relate to each other. Without a proper model, Power BI cannot aggregate data correctly across tables — a sales total might count every product line separately instead of rolling up per customer, or a filter on one table might not affect another table at all. A properly built model makes every visual work correctly and every filter propagate where it should.

Star Schema: The Best Practice Model Shape

The star schema is the gold-standard data model shape for Power BI. It has one central table of facts (the measurements — sales amounts, quantities, scores) surrounded by dimension tables (the context — who, what, when, where).

STAR SCHEMA DIAGRAM

                  ┌────────────────┐
                  │  DIM_Date      │
                  │  DateKey       │
                  │  Date          │
                  │  Month         │
                  │  Quarter       │
                  │  Year          │
                  └───────┬────────┘
                          │
┌────────────┐            │            ┌─────────────────┐
│DIM_Customer│            │            │  DIM_Product    │
│CustomerKey │            │            │  ProductKey     │
│Name        │────────────┤            │  ProductName    │
│Region      │            │            │  Category       │
│Segment     │            │            │  SubCategory    │
└────────────┘      ┌─────▼──────┐     └────────┬────────┘
                    │ FACT_Sales │              │
┌────────────┐      │ SalesKey   │──────────────┘
│ DIM_Channel│      │ DateKey    │
│ ChannelKey │      │ CustomerKey│
│ ChannelName│──────│ ProductKey │
│ Type       │      │ ChannelKey │
└────────────┘      │ Quantity   │
                    │ SalesAmount│
                    │ Discount   │
                    └────────────┘

Center = Fact table (one row per transaction)
Surrounding = Dimension tables (one row per unique entity)
Lines = Relationships

The fact table is tall (millions of rows, one per event) and narrow (only keys and measurements). Dimension tables are short (thousands of rows, one per unique item) and wide (many descriptive columns). This separation keeps the model efficient and makes DAX calculations predictable.

Creating Relationships in Power BI

In Power BI Desktop, click the Model view icon on the left sidebar (it looks like three connected boxes). This shows all your tables as cards connected by lines. Drag a column from one table to the matching column in another table to create a relationship.

RELATIONSHIP PROPERTIES

Cardinality:
  Many to One (*:1) — many rows in Fact match one row in Dimension
  One to One (1:1)  — each row in both tables matches exactly one row
  Many to Many (*:*) — avoid when possible; requires careful setup

Cross filter direction:
  Single — filters flow one way only (from Dimension to Fact)
  Both   — filters flow both ways (needed for some many-to-many scenarios)

Best practice: Use Many to One relationships with Single direction.
This gives Power BI the clearest path for filter propagation.

What Is DAX?

DAX stands for Data Analysis Expressions. It is the formula language used in Power BI (and Excel Power Pivot and Analysis Services) to create calculated columns and measures. DAX looks somewhat like Excel formulas — it uses functions with parentheses and arguments — but it operates on entire tables and columns rather than individual cells.

Calculated Columns vs. Measures: The Most Important Distinction

CALCULATED COLUMN vs. MEASURE

CALCULATED COLUMN:
  Computed row by row, stored in the table
  Calculated once when data refreshes
  Visible in the Fields panel under the table
  Good for: labeling rows, creating categories, helper fields

  Example: Full Name = DIM_Customer[FirstName] & " " & DIM_Customer[LastName]
  Each customer row gets a Full Name value stored permanently.

MEASURE:
  Calculated on demand based on the current filter context
  Not stored — recomputed each time a visual requests it
  Displayed in the Fields panel with a calculator icon
  Good for: KPIs, totals, ratios, comparisons, anything that changes with filters

  Example: Total Sales = SUM(FACT_Sales[SalesAmount])
  The total updates based on whatever the user has filtered.

Always prefer measures over calculated columns for aggregations. A measure recalculates correctly when the user changes a slicer — a calculated column does not. Most DAX you write in Power BI will be measures.

Creating Your First Measures

Basic Aggregation Measures

Right-click a table in the Fields panel and choose New Measure. The formula bar opens. Type your DAX formula and press Enter.

BASIC AGGREGATION MEASURES

Total Sales = SUM(FACT_Sales[SalesAmount])
Total Quantity = SUM(FACT_Sales[Quantity])
Average Order Value = AVERAGE(FACT_Sales[SalesAmount])
Number of Orders = COUNTROWS(FACT_Sales)
Distinct Customers = DISTINCTCOUNT(FACT_Sales[CustomerKey])
Max Sale = MAX(FACT_Sales[SalesAmount])
Min Sale = MIN(FACT_Sales[SalesAmount])

IF in DAX

Profit Margin Category =
IF(
    [Profit Margin %] >= 0.30, "High",
    IF( [Profit Margin %] >= 0.15, "Medium", "Low" )
)

CALCULATE: The Most Powerful DAX Function

CALCULATE evaluates a measure inside a modified filter context. It is the function that makes DAX powerful — it lets you override or add filters programmatically, regardless of what the user has filtered in the report.

CALCULATE SYNTAX:
CALCULATE( Expression, Filter1, Filter2, ... )

EXAMPLES:

High Priority Sales =
CALCULATE(
    SUM(FACT_Sales[SalesAmount]),
    DIM_Product[Category] = "Electronics"  // override: only electronics
)
→ Always shows electronics sales, even if user filters to another category.

Sales Last Year =
CALCULATE(
    SUM(FACT_Sales[SalesAmount]),
    SAMEPERIODLASTYEAR(DIM_Date[Date])    // time intelligence modifier
)
→ Shows the same period from last year, for any date range the user selects.

YoY Growth % =
DIVIDE(
    [Total Sales] - [Sales Last Year],
    [Sales Last Year],
    0   // return 0 if Sales Last Year is blank (avoids divide-by-zero)
)

Time Intelligence Functions

Time intelligence is one of DAX's greatest strengths. These functions automatically adjust date filters to calculate period-over-period comparisons, running totals, and moving averages — things that require complex SQL but a single DAX function call.

TIME INTELLIGENCE FUNCTIONS

SAMEPERIODLASTYEAR:
  Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DIM_Date[Date]))
  → Same days/months last year

TOTALYTD (Year-to-date total):
  Sales YTD = TOTALYTD([Total Sales], DIM_Date[Date])
  → Cumulative total from Jan 1 to the selected date

TOTALQTD, TOTALMTD:
  Sales QTD = TOTALQTD([Total Sales], DIM_Date[Date])
  Sales MTD = TOTALMTD([Total Sales], DIM_Date[Date])

DATEADD (shift by any period):
  Sales 3M Ago = CALCULATE([Total Sales], DATEADD(DIM_Date[Date], -3, MONTH))

DATESINPERIOD (rolling window):
  Sales Last 30 Days =
  CALCULATE(
      [Total Sales],
      DATESINPERIOD(DIM_Date[Date], LASTDATE(DIM_Date[Date]), -30, DAY)
  )

IMPORTANT: Time intelligence only works if you have a proper 
Date dimension table with continuous dates and a relationship to your fact table.

Creating a Date Table

Time intelligence functions require a Date dimension table that contains every calendar date (no gaps) in your data's date range. Power BI can generate one automatically or you can create it with DAX.

AUTO-GENERATE DATE TABLE WITH DAX:

Date Table = CALENDARAUTO()
→ Scans all date columns in your model and creates a continuous 
  date table from the earliest to the latest date found.

Or specify the range:
Date Table = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))

Then add columns to the date table:
Year = YEAR(DIM_Date[Date])
Month Number = MONTH(DIM_Date[Date])
Month Name = FORMAT(DIM_Date[Date], "MMMM")
Quarter = "Q" & QUARTER(DIM_Date[Date])
Week Number = WEEKNUM(DIM_Date[Date])
Weekday = FORMAT(DIM_Date[Date], "dddd")
Is Weekend = IF(WEEKDAY(DIM_Date[Date], 2) >= 6, TRUE, FALSE)
Fiscal Year = IF(MONTH(DIM_Date[Date]) >= 4, YEAR(DIM_Date[Date]) + 1, YEAR(DIM_Date[Date]))

After creating the Date table, mark it as a Date Table: right-click the table in the Fields panel → Mark as date table → select the Date column. This unlocks all time intelligence functions in your measures.

FILTER and ALL: Controlling Context

ALL — remove all filters from a table or column:

% of Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(FACT_Sales))
)
→ Shows each category's sales as a percentage of grand total,
  regardless of what filters are applied.

ALLSELECTED — remove report filters but keep slicer selections:
% of Slicer Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALLSELECTED(DIM_Product))
)
→ Percentage of the sliced total, not the grand total.

FILTER — apply a programmatic filter:
High Value Customers =
CALCULATE(
    DISTINCTCOUNT(FACT_Sales[CustomerKey]),
    FILTER(FACT_Sales, FACT_Sales[SalesAmount] > 100000)
)

RANKX: Ranking Items

Top N Rank =
RANKX(
    ALL(DIM_Product[ProductName]),  // rank across all products
    [Total Sales],                   // by this measure
    ,                                // (use default value)
    DESC,                            // largest = rank 1
    Dense                            // no gaps in ranking (1,2,3 not 1,2,4)
)

Use in a table visual to show every product's sales rank.
Then filter the table to show only Rank <= 10 for a Top 10 products table.

Variables in DAX

Variables make complex DAX formulas readable and efficient by storing intermediate results. Instead of repeating the same sub-expression multiple times, define it once in a variable and reference the variable.

EXAMPLE WITHOUT VARIABLES (hard to read, inefficient):

Profit Margin % =
DIVIDE(
    SUM(FACT_Sales[SalesAmount]) - SUM(FACT_Sales[Cost]),
    SUM(FACT_Sales[SalesAmount]),
    0
)

SAME FORMULA WITH VARIABLES (clean and clear):

Profit Margin % =
VAR TotalSales = SUM(FACT_Sales[SalesAmount])
VAR TotalCost  = SUM(FACT_Sales[Cost])
VAR GrossProfit = TotalSales - TotalCost
RETURN
    DIVIDE(GrossProfit, TotalSales, 0)

Variables are evaluated once, then reused.
RETURN specifies what the measure outputs.

Data Model Best Practices

DO:
  ✓ Use star schema — one fact table, multiple dimension tables
  ✓ Create a dedicated Date dimension table, mark it as date table
  ✓ Prefer Many-to-One relationships with Single cross-filter direction
  ✓ Write measures in a dedicated "Measures" table for organization
  ✓ Use variables for complex DAX formulas
  ✓ Name measures with spaces and proper capitalization: "Total Revenue YTD"

AVOID:
  ✗ Snowflake schemas (chained dimension tables) — create complexity
  ✗ Bidirectional relationships everywhere — causes ambiguous filter paths
  ✗ Calculated columns for aggregations — use measures instead
  ✗ Importing unnecessary columns — more columns = larger file size
  ✗ Storing data in the model that could stay in the source

Key Points

  • Build a star schema: one fact table with measurements surrounded by dimension tables with descriptive attributes. Fact tables are tall and narrow; dimension tables are short and wide.
  • Create relationships in Model view by dragging a key column from one table to the matching column in another. Use Many-to-One, Single-direction relationships.
  • Calculated columns are computed per row and stored in the table. Measures are computed dynamically based on the filter context — always prefer measures for aggregations.
  • CALCULATE is the most powerful DAX function — it evaluates a measure inside a modified filter context. Combine it with time intelligence functions for period-over-period analysis.
  • Time intelligence functions (SAMEPERIODLASTYEAR, TOTALYTD, DATEADD) require a proper Date dimension table marked as a date table.
  • ALL removes filters from a table or column — use it in percentage-of-total calculations. ALLSELECTED removes report filters but preserves slicer selections.
  • Use VAR ... RETURN in complex DAX to store intermediate values — makes formulas readable and prevents repeated calculations.
Previous lessons
Back to courses
Next lessons

Leave a Comment