Course (Part 751)

Course: Microsoft Power BI Desktop: Advanced DAX for Data Analysis

Lesson

Topic: Getting Started — Introduction to DAX (Data Analysis Expressions) in Power BI

Introductory Composition

Welcome to the Getting Started chapter for Power BI where we introduce the essentials of DAX (Data Analysis Expressions) and practical techniques for building robust data models. In this session you will learn how to create calculated tables such as a calendar table using the CALENDAR function and why a dedicated date table matters for time intelligence. You will also see how to summarize and aggregate data using SUMMARIZECOLUMNS, combine grouping with filters, and compute measures like totals and averages. DAX queries allow you to return tables directly (via EVALUATE) and to control ordering with ORDER BY and pagination with START AT. A key point is that DAX queries do not inherit visual sort order from Power BI—ordering must be specified explicitly in the query. We will demonstrate best practices such as using variables to simplify formulas, protecting calculations with IFERROR, and minimizing expensive calculated columns in favor of measures. The course also covers VertiPaq concepts like columnar storage and encoding to help you optimize model size and query performance. You will learn relationship functions (RELATED, RELATEDTABLE), how to activate alternate relationships with USERELATIONSHIP, and how to control filter propagation with REMOVEFILTERS, KEEPFILTERS, CROSSFILTER and TREATAS. Practical examples include summarizing orders by month and product category and calculating average profit per order using DIVIDE to avoid divide-by-zero errors. By the end of this chapter, you will be comfortable creating date tables, writing basic DAX queries, and understanding when to use SUM vs SUMX and when to apply iterator functions like AVERAGEX. These fundamentals will prepare you to implement advanced DAX patterns, reason about evaluation context, and tune performance in subsequent chapters.

Learning Outcomes

• Understand and create calculated tables (calendar) using CALENDAR/CALENDARAUTO.
• Summarize data using SUMMARIZECOLUMNS and write basic DAX queries with EVALUATE.
• Control ordering and pagination in queries with ORDER BY and START AT.
• Use measures, variables (VAR), and iterator functions (SUMX, AVERAGEX) effectively.
• Apply filter-management functions: REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIPS, CROSSFILTER, ALLSELECTED, TREATAS.
• Follow best practices: comment code, use a dedicated measure table, and implement error handling (IFERROR, ISBLANK).

Conceptual Explanation

DAX is a formula language that operates over tabular models. Core building blocks include calculated columns, measures, and calculated tables. A calculated table (example) for a date dimension:

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

To summarize rows and compute per-row measures in a query use SUMMARIZECOLUMNS; to return results from the model use EVALUATE. Example summary with a filter and safe division:

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

Important engine concepts — Formula Engine executes expressions; Storage (VertiPaq) stores compressed columnar data. Use iterator functions (SUMX, AVERAGEX) when the expression needs per-row evaluation; use SUM for a simple column aggregation. Manage filter context explicitly: CALCULATE modifies filters, USERELATIONSHIP activates alternate relationships, REMOVEFILTERS resets filters, and KEEPFILTERS refines them. For advanced cross-table filtering without relationships, TREATAS applies values from one table as filters in another. Comment your DAX, prefer measures over calculated columns where possible, and use a dedicated measure table to keep the model tidy.

Answer — Quick examples & tips

• Calendar example: Dim_Calendar = CALENDAR(MIN(...), MAX(...))
• Summary example: use SUMMARIZECOLUMNS with measures and DIVIDE.
• Sort in queries: use ORDER BY inside the DAX query.
• Start at a row: use START AT "SO43661" for pagination in queries.

Bilingual (EN → বাংলা)

EN: Create a calendar with CALENDAR(MIN(...), MAX(...)) and summarize using SUMMARIZECOLUMNS. DAX queries require explicit ordering via ORDER BY. Use variables (VAR) to simplify complex logic.

বাংলা: একটি ক্যালেন্ডার তৈরি করতে ব্যবহার করুন CALENDAR(MIN(...), MAX(...)) এবং সারাংশ তৈরির জন্য SUMMARIZECOLUMNS। DAX কুয়েরিগুলোতে স্পষ্টভাবে সাজানোর জন্য ORDER BY প্রয়োজন। জটিল লজিক সহজ করতে ভ্যারিয়েবল (VAR) ব্যবহার করুন।

Answer — Key terms translated

English	বাংলা
Calendar table	ক্যালেন্ডার টেবিল
Measure	মেজার/পর্যবেক্ষণ মান
Filter context	ফিল্টার প্রসঙ্গ
Summarize	সারসংক্ষেপ/সমষ্টি

Important Notes

• DAX queries do not inherit the visual sort order—always use ORDER BY inside queries.
• Prefer measures over calculated columns for aggregations to save memory and improve performance.
• Use the DIVIDE() function for safe division to avoid divide-by-zero errors.
• Document complex DAX with comments (// or /* ... */).
• Create a dedicated measure table in the model to centralize measures and improve discoverability.

Answer — Rationale

Ordering: queries are evaluated independently of report visuals, so ORDER BY is necessary. Measures are computed at query time and are usually more efficient; calculated columns increase storage. DIVIDE provides an alternate result when denominator is zero. Comments aid team maintenance.

Exercises

True / False

1. DAX queries automatically respect the sort order set in Power BI visuals.
   Answer

   False — specify ORDER BY in the DAX query.

2. SUMX iterates a table row-by-row and can evaluate an expression per row.
   Answer

   True — SUMX is an iterator function.

3. CALCULATE cannot change which relationship is active.
   Answer

   False — CALCULATE with USERELATIONSHIP can activate an inactive relationship for the calculation.

Fill in the Blanks

1. To create a basic date table from the min to the max date use: _____.
Answer

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

2. Use _____ to safely divide two measures avoiding divide-by-zero.
Answer

DIVIDE(numerator, denominator, alternateResult)

Multiple Choice Questions

1. Which function returns unique values from a column?
   • A. VALUES
   • B. DISTINCT
   • C. BOTH A & B
   Answer

   C — VALUES and DISTINCT both return unique values (VALUES can return BLANKs in some contexts).

2. To apply filters from one table to unrelated table columns you would use:
   • A. REMOVEFILTERS
   • B. TREATAS
   • C. CROSSFILTER
   Answer

   B — TREATAS applies the values of one table/column as filters to another column.

Matching

Match function to purpose:

Function	Purpose
USERELATIONSHIP	Activate an alternate relationship for a calculation
SUMMARIZECOLUMNS	Group and aggregate data with optional filters
CONCATENATEX	Concatenate text values from rows into a single string

Answer

See table above — each function aligned with its purpose; use these functions when you need their specific behavior in DAX.

Descriptive

Describe in a short paragraph how you would calculate a 3-month moving average for Total Sales using DAX.

Answer

Define a measure for Total Sales (e.g., Total Sales = SUM(Sales[SalesAmount])), then create a moving average measure using AVERAGEX over a date range generated by DATESINPERIOD: MovingAverage3M = AVERAGEX(DATESINPERIOD(Dim_Calendar[Date], LASTDATE(Dim_Calendar[Date]), -3, MONTH), [Total Sales]). Use this measure in time series visuals.

Creative

Propose one small project (2–3 visuals) that demonstrates DAX skills learned in this lesson.

Answer

Project: "Clothing Category Performance Dashboard" — Visual 1: Line chart of monthly Total Sales with 3-month moving average (use Dim_Calendar and MovingAverage measure). Visual 2: Table summarizing Month, Orders, Avg Profit per Order with filter for Clothing using SUMMARIZECOLUMNS and DIVIDE. Add a card showing Sales % vs Target using TREATAS to apply target table values. Include slicer for Year using Dim_Calendar.

Activities, Vocabulary, Homework, Practice Task

Activities

1. Create a calendar table (CALENDAR) and add Year, Month Name, Month Number columns.
2. Write a DAX query using SUMMARIZECOLUMNS to show monthly orders and average profit for "Clothing".
3. Build a measure that calculates Sales % of Target using TREATAS.

Answer

See examples in conceptual explanation and earlier DAX snippets for implementation hints.

Vocabulary

CALENDARCreates a table of continuous dates. SUMMARIZECOLUMNSGroups and aggregates data with filter options. DATESINPERIODGenerates a contiguous date range relative to a given date. DIVIDESafe division function handling divide-by-zero. VERTIPAQPower BI storage engine using columnar compression.

Homework

1. Create a dedicated measure table and move three existing measures into it.
2. Implement a 4-5-4 calendar for a sample year and summarize monthly sales.
3. Write a DAX query that returns Sales Orders starting at a chosen order ID and order results chronologically.

Answer

Follow examples in course materials: use DATATABLE or a small helper table for measures; implement 4-5-4 with calculated columns for week-of-month; use EVALUATE ... ORDER BY ... START AT for the query.

Practice Task

Implement a Power BI page that shows:

• A calendar slicer (Dim_Calendar).
• A line chart with Total Sales and 3-month moving average.
• A table summary for chosen product category using SUMMARIZECOLUMNS.

Answer

Use measure Total Sales, Moving Average with DATESINPERIOD and AVERAGEX, and SUMMARIZECOLUMNS for the table. Apply category filter with FILTER or slicer.

End of lecture sheet — practice the DAX examples in Power BI Desktop, use DAX Studio to run queries, and consult the course resources for further reading.