Course (Part 368)

Course: Microsoft Power BI: PL-300 Certification Prep (Data Analyst)

Lesson: Getting Started with Power BI DAX

Topic: Introduction to core DAX concepts, calculated tables, summarization, ordering, and query techniques

Learning Outcomes

• Explain what DAX is and where it is used (Power BI, Excel, SSAS).
• Create a basic calculated table (calendar) using CALENDAR/MIN/MAX.
• Summarize model data using SUMMARIZECOLUMNS and add measures safely with DIVIDE.
• Write DAX queries with ORDER BY and START AT to control sort and paging.
• Distinguish active vs inactive relationships and use USERELATIONSHIP when needed.
• Understand RLS basics (static and dynamic) and deployment considerations.

Conceptual Explanation

Core concept

DAX is a functional language designed for relational data; it powers calculated columns, measures, and calculated tables in Power BI. Use calculated tables for data modeling tasks such as building a date dimension, and use measures for dynamic aggregations that respond to filter context.

Key functions and patterns

• CALENDAR: build continuous date tables spanning MIN and MAX of a date column.
• SUMMARIZECOLUMNS: group data by columns and define row-level expressions/measures.
• DIVIDE: safe division that handles zero denominators.
• ORDER BY / START AT: explicit ordering and paging in DAX queries.
• USERELATIONSHIP: activate inactive relationships inside CALCULATE.

Representative DAX snippets

-- Calculated calendar table
Dim_Calendar = CALENDAR(
  MIN('Fact_StrategyPlan'[Datekey]),
  MAX('Fact_StrategyPlan'[Datekey])
)
-- Summarize by month & category (filter Clothing)
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
-- Query with paging
EVALUATE
'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

Bilingual (EN → বাংলা)

Important Notes

• DAX queries do not inherit the report visual sort order—use ORDER BY in your query when needed.
• Use DIVIDE(numerator, denominator, 0) to avoid division-by-zero errors.
• Prefer calculated tables for model structure (calendar, lookup tables) and measures for on-the-fly aggregations.
• Test roles and RLS in Power BI Desktop (View as Roles) and assign users in the Service.
• Enable Incremental Refresh and leverage query folding for large sources to improve performance.

Exercises

True / False

1. True / False: SUMMARIZECOLUMNS will automatically honor the sorting defined in the Power BI report visual.

   Answer

   False. DAX queries do not automatically use the report visual sort order; include ORDER BY in the DAX query to set sort.

2. True / False: DIVIDE handles division by zero if you pass an alternate result.

   Answer

   True. DIVIDE(numerator, denominator, alternateResult) returns alternateResult when denominator is zero or blank.

Fill in the Blanks

1. Fill: To create a calendar table spanning the datekey range, you use __________.

   Answer

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

2. Fill: To safely compute average profit per order use __________ around the division.

   Answer

   DIVIDE([Total Sales Profit], [Orders], 0)

Multiple Choice (MCQ)

1. Which DAX function groups and returns a table that you can use in EVALUATE?

   • A) CALCULATE
   • B) SUMMARIZECOLUMNS
   • C) DIVIDE
   • D) USERELATIONSHIP
   Answer

   Correct: B) SUMMARIZECOLUMNS. It groups data by specified columns and can be used with EVALUATE to return tabular results.

2. Which clause lets you start showing query results from a specific key?

   • A) ORDER BY
   • B) START AT
   • C) FILTER
   • D) TOPN
   Answer

   Correct: B) START AT.

Matching

Descriptive

1. Explain why you would create a dedicated Date table rather than relying on auto date tables.

   Answer

   A dedicated Date table provides full control: custom columns (fiscal periods, week numbers), consistent joins across facts, better performance, and predictable behavior for time intelligence. Auto date tables are hidden and limited; a custom Dim_Calendar supports sorting, hierarchies, and calculations.

2. When should you use USERELATIONSHIP in a measure?

   Answer

   Use USERELATIONSHIP when the model has multiple relationships between two tables (for example OrderDate and ShipDate to Date) and you need to evaluate an expression using the inactive relationship temporarily inside CALCULATE.

Creative

Design a short DAX query to show monthly orders and average profit per order for category "Clothing" and ensure months sort chronologically.

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],0)
)
ORDER BY 'Date'[Month of Year] ASC

Activities, Vocabulary, Homework, Practice Task

Activities

1. Activity: Create Dim_Calendar in your model and add Month Name & Month Number columns.
Answer

DAX: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]),MAX('Fact_StrategyPlan'[Datekey])) Then add calculated columns for Month Name (FORMAT([Date],"MMMM")) and Month Number (MONTH([Date])).

2. Activity: Write a SUMMARIZECOLUMNS query to present Orders and Avg Profit per Order for "Clothing".
Answer

See the DAX example above in Creative answer.

Vocabulary

• DAX: Data Analysis Expressions
• CALENDAR: creates date ranges
• SUMMARIZECOLUMNS: group + aggregate
• DIVIDE: safe division
• USERELATIONSHIP: enable inactive relationship
• START AT: paging start in DAX query

Homework

1. Implement a calculated calendar and add Quarter and Fiscal Year columns.
2. Build a measure for Average Profit per Order using DIVIDE and show it in a summary by month.

Practice Task

Using your dataset: create a report page that shows a small multiples line chart of monthly sales for each product category, and add a tooltip page that shows Orders and Avg Profit per Order for hovered points.

Further reading & resources: Power BI documentation on DAX, SUMMARIZECOLUMNS, CALENDAR, DIVIDE, USERELATIONSHIP, and Power Query (M).