Quick recap of Import, Modeling, DAX0%
Project requirements for Intermediate Level

Quick recap of Import, Modeling, DAX

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Quick Recap of Import, Modeling & DAX Foundations

Welcome to Power BI for Intermediate. In Level 1 (Beginner), you learned the fundamentals of Power BI Desktop: navigating the interface, importing simple Excel sheets, performing basic transformations in Power Query, building fundamental visualizations, and writing elementary DAX formulas such as SUM, AVERAGE, and COUNTROWS.

Before tackling enterprise data models and complex calculation patterns, let us consolidate the core architectural pillars of Microsoft Power BI.

1. The Three Storage Engines & Connectivity Modes

Power BI Desktop offers three distinct data ingestion modes, each suited to different enterprise workload requirements:

Storage ModeMechanismProsCons / ConstraintsBest Use Case
Import Mode (Default)In-memory columnar VertiPaq engine compression.Blazing fast analytical speed; full DAX expressiveness.1 GB dataset size limit per model in Power BI Pro; data freshness requires scheduled refresh.90% of business reporting dashboards (< 20M rows).
DirectQueryNo data is cached locally; DAX queries are converted into native SQL and sent live to source.Real-time freshness; overcomes the 1 GB dataset size limit.Slower visual response times; DAX limitations; high load on source database.Real-time IoT monitoring; multi-billion row enterprise data warehouses (Snowflake, BigQuery).
Dual Mode / CompositeTables can be configured as Import or DirectQuery depending on granularity.Combines speed for aggregates with live drill-down to transactions.Increased model complexity; requires careful relationship management.Large-scale financial auditing and enterprise telemetry.

2. The VertiPaq Columnar Engine: Why Column Structure Matters

Traditional relational databases (OLTP) like PostgreSQL or MySQL store records row-by-row:

Output
Row-Oriented Storage (OLTP):
[Row 1: ID=101, Customer="Aarav", State="DL", Amount=450]
[Row 2: ID=102, Customer="Pooja", State="MH", Amount=1200]
[Row 3: ID=103, Customer="Aarav", State="DL", Amount=350]

Power BI uses VertiPaq, a columnar in-memory database engine:

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart
Step 1
Customer Column: ["Aarav", "Pooja", "Aarav"]
Step 2
Dictionary Encoded: 0: "Aarav", 1: "Pooja"
Step 3
[0, 1, 0]

VertiPaq Compression Rules:

  1. 1
    Value Encoding: Subtraction of minimum values to store smaller integers.
  2. 2
    Dictionary Encoding: Replaces high-cardinality string values with compact integer IDs.
  3. 3
    Run-Length Encoding (RLE): Compresses consecutive identical values (e.g., ["DL", "DL", "DL", "DL"] stored as (DL, 4)).

3. Calculated Columns vs. Calculated Measures

One of the most critical conceptual milestones for an intermediate analyst is understanding when to use a Calculated Column versus a Measure:

Output
Calculated Columns vs. Measures
|
+----------------------------+----------------------------+
| |
Calculated Column Calculated Measure
- Evaluated during data refresh - Evaluated dynamically at query time
- Stored permanently in RAM/Model - Consumes 0 permanent storage memory
- Operates in Row Context (row-by-row) - Operates in Filter Context
- Used for Slicers, Axes, Groupings - Used in Values bucket of visuals

Practical DAX Example

DAX
-- CALCULATED COLUMN (Creates permanent storage in table for each row)
Orders[GrossMarginAmount] = Orders[Sales] - Orders[Cost]
 
-- CALCULATED MEASURE (Calculates dynamically on the fly based on slicers and visual filters)
Total Profit Margin % =
DIVIDE(
[Total Profit],
[Total Sales],
0
)

4. Evaluation Context Foundations

DAX evaluates expressions in two distinct contexts:

  1. 1
    Row Context:
  • Exists natively in calculated columns and DAX iterators (SUMX, FILTER, AVERAGEX).
  • Knows only the current row's values. It does NOT know what other rows exist in the table.
  1. 1
    Filter Context:
  • Created by report visuals, slicers, matrix row/column headers, and the CALCULATE function.
  • Restricts the subset of data passed to measures.
Interactive Quiz

Multiple Choice Questions

Question 1
What is the primary operational difference between Power BI Import Mode and DirectQuery?
Question 2
Why should high-cardinality numerical calculations (such as sales margin percentage) be created as Measures rather than Calculated Columns?
Question 3
Which compression technique used by VertiPaq replaces repeated text strings with compact integer reference pointers?
Question 4
What type of evaluation context exists natively when calculating a row-by-row formula in a Calculated Column?
Question 5
What will occur if a report visual contains a measure using DIVIDE([Total Profit], [Total Sales], 0) when [Total Sales] evaluates to zero?
Next Lesson

Project requirements for Intermediate Level

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
NoneProject requirements for Intermediate Level

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Next
PrevNext