Course (Part 749)

Course: Microsoft Power BI: Beginner to Advanced Intelligence

Lesson

Topic: Power BI DAX Fundamentals — Calculated Tables, Summaries, Queries, and Practical Practices

Learning Outcomes

• Understand what DAX is and where it is used in Power BI.
• Create calculated tables (calendar table example) and know why a date dimension matters.
• Summarize data using SUMMARIZECOLUMNS and compute robust measures (Orders, Avg Profit per Order).
• Write DAX queries (EVALUATE) and control ordering (ORDER BY) and pagination (START AT).
• Handle errors and blanks with DIVIDE, IFERROR/COALESCE and manage filter contexts with CALCULATE/ALL/ALLEXCEPT/REMOVEFILTERS.

Conceptual Explanation

DAX (Data Analysis Expressions) is a formula language created for data modeling and analytics in Power BI and related Microsoft tools. Its primary artifacts are calculated columns, measures, and calculated tables. Calculated tables let you derive new tables from existing data; for time intelligence you typically create a calendar table using CALENDAR(MIN(...), MAX(...)). Aggregation and grouping in queries usually use SUMMARIZECOLUMNS (or SUMMARIZE) combined with measures. For per-row math or protections against divide-by-zero use DIVIDE; to handle missing data use COALESCE or IF(ISBLANK(...)). DAX queries (EVALUATE ...) return tables and must explicitly specify ORDER BY if you need a particular sort; Power BI visual sort order is not applied. START AT can be used to display rows beginning at a specific key. CALCULATE changes filter context and, together with USERELATIONSHIP or CROSSFILTER, lets you use alternate relationships in calculations. For advanced filtering control, functions like ALL, ALLEXCEPT, and REMOVEFILTERS are essential. Use variables (VAR ... RETURN ...) to simplify and optimize complex expressions. Finally, combine clean data preparation (Power Query) with DAX measures for best performance and clarity.

Bilingual (EN → বাংলা)

EN: Create a calendar table with DAX: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])).

বাংলা: DAX দিয়ে ক্যালেন্ডার টেবিল তৈরি করুন: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))। এটি সময়ভিত্তিক বিশ্লেষণের জন্য প্রয়োজনীয়।

Important Notes

• Use Power Query for heavy cleaning. Use DAX for model-level calculations and measures.
• DAX queries do not inherit visual sort order — include ORDER BY in your DAX queries.
• Prefer measures to repeated expressions; use VAR for readability and performance.
• Protect against errors with DIVIDE and handle blanks with COALESCE / ISBLANK.
• Document renamed/deleted fields; deleting referenced measures breaks visuals.

Exercises

True / False

1. SUMMARIZECOLUMNS automatically inherits the sort order from Power BI visuals.
Answer

False — DAX queries do not inherit visual sort order. Use ORDER BY explicitly.

2. DIVIDE(x, y) is safer than x / y when y might be zero.
Answer

True — DIVIDE includes an alternate result parameter and avoids division-by-zero errors.

3. CALCULATE can change filter context and apply new filters.
Answer

True — CALCULATE evaluates an expression in a modified filter context.

Fill in the Blanks

1. To create a continuous date table from a fact table you use the DAX function ________.
Answer

CALENDAR

2. Use ________ to concatenate multiple row values into a single string separated by a delimiter.
Answer

CONCATENATEX

3. To remove all filters from a column inside CALCULATE use ________.
Answer

ALL (or REMOVEFILTERS)

Multiple Choice (MCQ)

1. Which function returns a table grouped by columns and additional measures?
   • A. FILTER
   • B. SUMMARIZECOLUMNS
   • C. DIVIDE
   • D. SELECTCOLUMNS
Answer

B. SUMMARIZECOLUMNS — groups by columns and can add measures.

2. Which DAX expression handles division safely?
   • A. /
   • B. DIVIDE()
   • C. QUOTIENT()
   • D. SAFE_DIV()
Answer

B. DIVIDE()

3. To keep a filter on Category but remove all other filters you use:
   • A. ALL()
   • B. ALLEXCEPT()
   • C. REMOVEFILTERS()
   • D. VALUES()
Answer

B. ALLEXCEPT() — removes all filters except for specified columns.

Matching

Match the DAX function to its primary purpose:

Descriptive

Describe the difference between a calculated column and a measure and when you would use each.

Creative

Design a 3-card layout for a Wealth dashboard (title and three measures). Provide the DAX for each measure.

Activities, Vocabulary, Homework, Practice Task

Activities

1. Create a calendar table and add Month and Year columns.
Answer

Use Dim_Calendar = CALENDAR(MIN(...), MAX(...)), then add Month = FORMAT(..., "MMMM") and Year = YEAR(...).

2. Write a measure: Average Profit per Order using DIVIDE.
Answer

Avg Profit per Order = DIVIDE([Total Sales Profit], [Orders])

Vocabulary

• Context — The set of filters that affects a calculation.
Answer

Context can be row context (calculated columns) or filter context (measures, slicers).

• Measure — A dynamic aggregation calculated at query time.
Answer

Measures react to report filters and are not stored in the model as values.

Homework

1. Build a page with: a calendar slicer, a summary table (Month × Category), and three cards (Total Sales, Orders, MoM Growth). Document DAX measures used.
Answer

Suggested measures: Total Sales = SUM('Fact_Sales'[SalesAmount]), Orders = COUNTROWS('Fact_Sales'), MoM Growth = DIVIDE([Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH(Dim_Calendar[Date])), CALCULATE([Total Sales], PREVIOUSMONTH(Dim_Calendar[Date])), 0).

Practice Task

Given a 'Sales' table and 'Product' table, create a query that shows month, product category, orders and avg profit per order for "Clothing" and sort by month.

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

Further reading: Microsoft DAX reference, Power Query best practices, and the course exercises. Happy analyzing!