Course (Part 738)

Course: Complete Introduction to Microsoft Power BI

Power BI DAX — Lecture Sheet

Lesson

Topic: Introduction to Power BI DAX — Calculated Columns, Measures, and Calculated Tables

Learning Outcomes

• Understand what DAX is and where it is used (Power BI, Excel, Analysis Services).
• Differentiate between calculated columns, measures and calculated tables.
• Create a calendar (date) table with DAX.
• Summarize data using SUMMARIZECOLUMNS and write basic EVALUATE queries.
• Order query results explicitly and use START AT for targeted retrieval.

Conceptual Explanation

DAX (Data Analysis Expressions) is a columnar formula language optimized for relational in-memory models. It allows model authors to define calculated columns, measures, and calculated tables that extend the data model and enable advanced analysis. Calculated columns compute a value for each row and are stored in the model; measures are evaluated at query time and adapt to filter context in visuals; calculated tables are new tables created by DAX expressions.

Key DAX building blocks:

• CALCULATED COLUMN: Row-by-row expressions. Example: Total Sales = 'Sales'[Quantity] * 'Sales'[Unit Price]
• MEASURE: Aggregation evaluated by query context. Example: Total Sales Profit = SUM('Sales'[Sales Amount]) - SUM('Sales'[Cost Amount])
• CALCULATED TABLE: New table expression. Example: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))

Summarization and queries:

EVALUATE
SUMMARIZECOLUMNS(
  'Date'[Month Name],
  'Date'[Month of Year],
  'Product'[Category],
  FILTER( VALUES('Product'[Category]), [Category] = "Clothing" ),
  "Orders", [Orders],
  "Avg Profit per Order", DIVIDE( [Total Sales Profit], [Orders] )
)
ORDER BY 'Date'[Month of Year] ASC

Notes on ordering: DAX queries using EVALUATE do not inherit the report visual sort; include ORDER BY explicitly. Use START AT to begin results at a particular key:

EVALUATE
'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

Bilingual (EN → বাংলা)

Important Notes

• Prefer measures for calculations that must react to filters and visuals.
• Use DIVIDE(a,b) instead of a/b to avoid division-by-zero errors.
• Create a dedicated date table (Dim_Calendar) and relate it to fact tables for time intelligence.
• DAX queries in the query pane require ORDER BY when a specific sort is needed.
• Calculated columns increase model size; use them judiciously.

Exercises → True / False / Fill in the Blanks / MCQ / Matching / Descriptive / Creative

True / False

1. Measures are computed and stored per row in the data model.
Answer

False — measures are computed on demand and evaluated in the filter context; calculated columns are stored per row.

2. The CALENDAR function can be used to build a continuous date table.
Answer

True — CALENDAR(MIN(...), MAX(...)) creates a continuous date column between two dates.

3. SUMMARIZECOLUMNS automatically respects the sort order defined in Power BI visuals.
Answer

False — DAX queries do not inherit visual sort; you must include ORDER BY explicitly in the query.

Fill in the Blanks

1. A DAX function that safely divides two numbers and handles divide-by-zero is _______.
Answer

DIVIDE()

2. To create a calendar table spanning dates in Fact_StrategyPlan, use _______ in DAX.
Answer

Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))

Multiple Choice Questions (MCQ)

1. Which DAX function groups columns and returns a table for use with EVALUATE?
   • A. FILTER
   • B. SUMMARIZECOLUMNS
   • C. CALCULATE
   • D. VALUES
Answer

B. SUMMARIZECOLUMNS

2. To force a DAX query result to start at a specific key you use:
   • A. LIMIT
   • B. OFFSET
   • C. START AT
   • D. BEGIN
Answer

C. START AT

Matching

Match the term (left) to its purpose (right):

Descriptive

Explain when you would use a calculated column vs a measure. (2–4 sentences)

Creative

Design a small report idea (title, two visuals, one slicer) that uses a Dim_Calendar and SUMMARIZECOLUMNS.

Activities, Vocabulary, Homework, Practice Task

Activities

1. Create Dim_Calendar using your fact table dates; add Year, Month and Quarter columns.
Answer

DAX example:

Dim_Calendar =
ADDCOLUMNS(
  CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])),
  "Year", YEAR([Date]),
  "Month Name", FORMAT([Date],"MMMM"),
  "Month of Year", MONTH([Date]),
  "Quarter", "Q" & QUARTER([Date])
)

2. Write a measure Total Sales and use it in a card visual.
Answer

Measure:

Total Sales = SUM('Sales'[SalesAmount])

Vocabulary

• Filter context — set of filters applied to a measure evaluation.
• Row context — context of the current row when evaluating calculated columns.
• Context transition — how CALCULATE changes filter context.

Homework

1. Download the training dataset and import into Power BI Desktop.
2. Create Dim_Calendar and relate it to your fact table.
3. Build a SUMMARIZECOLUMNS query that returns Orders and Avg Profit per Order by month for Clothing.

EVALUATE
SUMMARIZECOLUMNS(
  'Dim_Calendar'[Month Name],
  'Dim_Calendar'[Month of Year],
  FILTER( VALUES('Product'[Category]), [Category] = "Clothing" ),
  "Orders", [Orders],
  "Avg Profit per Order", DIVIDE( [Total Sales Profit], [Orders] )
)
ORDER BY 'Dim_Calendar'[Month of Year] ASC

Practice Task

Implement a DAX query that returns the first 50 sales orders starting at "SO43661" ordered ascending by Sales Order.

EVALUATE
'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

EVALUATE
TOPN(50, 'Sales Order', 'Sales Order'[Sales Order], ASC)

References & Further Reading: Power BI documentation, DAX guide by Microsoft, community blogs and DAX reference pages.