Course (Part 744)

Course: DAX Functions in Power BI

Setting up robust data models with DAX in Power BI begins with intentional table design, effective use of calculated tables, and clear understanding of contexts. A foundational step is creating a comprehensive date dimension — for example, using CALENDAR to span the minimum and maximum dates from a fact table — which enables reliable time intelligence and consistent joins across measures. Calculated columns and explicit measures extend model capabilities by embedding row-wise logic and reusable aggregations; DIVIDE and VAR patterns help make these calculations safe and readable. SUMMARIZECOLUMNS and EVALUATE support server-side summarization and tabular queries, but you must explicitly specify ORDER BY and START AT when query ordering or pagination is important. Row context, filter context, and query context are distinct concepts that determine how expressions evaluate; mastering their differences prevents many common DAX errors. Filter functions such as FILTER, CALCULATE, and ALL let you refine evaluation sets and control whether summary measures respect or ignore existing filters. Iterating functions like SUMX and AVERAGEX perform row-by-row computations essential for computed totals and custom aggregations. Using variables (VAR/RETURN) improves performance and maintainability by avoiding repeated subexpressions and clarifying logic. Practical utilities include RANKX for dynamic ranking, COUNTX/COUNTROWS for counts, and SUMMARIZE or ADDCOLUMNS for constructing analytical tables. Best practice favors explicit, documented measures over implicit calculations to ensure reusability, testability, and collaboration. Finally, testing with DAX queries, creating small calculated tables, and writing unit-test-like checks in measures help validate assumptions before deploying reports to stakeholders.

Lesson

Topic: Setting up Data Models with DAX in Power BI

Learning Outcomes

• Understand how to create and enhance calculated tables (e.g., date/calendar tables).
• Differentiate row, filter, and query contexts and apply them correctly.
• Summarize data with SUMMARIZECOLUMNS and craft DAX queries using EVALUATE, ORDER BY, and START AT.
• Use iterating functions, variables, and filter functions to build robust measures and columns.
• Apply best practices for explicit measures, error handling, and performance.

Conceptual Explanation

DAX is a formula language that operates inside the tabular model. Calculated tables, created with functions like CALENDAR and ADDCOLUMNS, provide stable dimensions for time intelligence and join operations.

Example: creating a calendar table using the date range of a fact table:

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

SUMMARIZECOLUMNS can group by columns and include measures; remember to use FILTER inside it to restrict categories or ranges.

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])
)

DAX query results do not inherit Power BI visual sort orders; include ORDER BY and optionally START AT for pagination:

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

Use VAR / RETURN to store intermediate results and to improve readability and performance. Iterating functions like SUMX evaluate expressions row-by-row within a table context.

Bilingual (EN → বাংলা)

EN: Create a date table using CALENDAR(MIN(...), MAX(...)) to enable time intelligence.

Answer

বাংলা: টাইম ইন্টেলিজেন্স সক্ষম করতে CALENDAR(MIN(...), MAX(...)) ব্যবহার করে একটি ডেট টেবিল তৈরি করুন।

EN: SUMMARIZECOLUMNS groups data and computes aggregates while allowing filters.

Answer

বাংলা: SUMMARIZECOLUMNS ডেটা গ্রুপ করে এবং ফিল্টার প্রয়োগ করে অ্যাগ্রিগেট গণনা করে।

EN: Distinguish row, filter and query context to avoid evaluation errors.

Answer

বাংলা: ইভ্যালুয়েশন ভুল এড়াতে রো, ফিল্টার ও কুয়েরি কনটেক্সট পৃথকভাবে বুঝুন।

Important Notes

• Always add an explicit date table instead of relying on implicit dates.
• Use DIVIDE to handle potential division-by-zero errors.
• Prefer explicit measures for reusability and clarity.
• When writing DAX queries for export or analysis, always include ORDER BY to guarantee order.
• Use ALL when you need to ignore existing filters for a calculation baseline.

Exercises

True / False

1. SUMMARIZECOLUMNS automatically respects report visual sort order.
   Answer

   False — DAX queries ignore report visual sort; use ORDER BY.

2. CALCULATED COLUMNS are evaluated during data refresh and stored in the model.
   Answer

   True — calculated columns are computed when data is processed.

3. VAR declarations always improve performance.
   Answer

   False — VAR improves readability and can reduce repeated work, but performance gains depend on usage.

4. RANKX can produce ties if DENSE is used.
   Answer

   True — DENSE ranking assigns the same rank to ties without gaps.

Fill in the Blanks

1. To create a date table spanning your fact table, use: Dim_Calendar = _______(MIN('Fact'[Date]), MAX('Fact'[Date])).
   Answer

   CALENDAR

2. Use _______([Total Sales Profit], [Orders]) to safely divide in DAX.
   Answer

   DIVIDE

3. To remove filters from a column inside CALCULATE, use the ______ function.
   Answer

   ALL

4. SUMX is an example of an __________ function (one word).
   Answer

   iterating

Multiple Choice Questions (MCQ)

1. Which function creates a single-column table of dates?
   • A. DATEADD
   • B. CALENDAR
   • C. DATESINPERIOD
   Answer

   B. CALENDAR

2. Which clause controls pagination in a DAX query?
   • A. OFFSET
   • B. START AT
   • C. LIMIT
   Answer

   B. START AT

3. To compute row-by-row products of quantity and price, which function is most appropriate?
   • A. SUM
   • B. SUMX
   • C. AVERAGE
   Answer

   B. SUMX

4. Which function would you use to ignore existing filters on Product?
   • A. FILTER
   • B. ALL
   • C. VALUES
   Answer

   B. ALL

Matching

Term	Definition
1. VAR	A. Removes filters
2. ALL	B. Iterates over rows
3. SUMX	C. Store an expression result
4. SUMMARIZECOLUMNS	D. Group and aggregate by columns

Answer

1 → C, 2 → A, 3 → B, 4 → D

Descriptive

1. Explain the difference between row context and filter context in a short paragraph.
   Answer

   Row context evaluates expressions for each individual row (typical in calculated columns or when using iterators). Filter context determines which subset of the data a measure sees and is influenced by visuals, slicers, or explicit filters passed via CALCULATE; measures often depend on filter context for final aggregation.

2. Describe one scenario where you would choose a calculated column over a measure.
   Answer

   Use a calculated column when a value must exist at row level for relationships, sorting, or to be used as a slicer/category (for example, computing ProductTier = IF([Sales] > 1000, "High", "Low") that you want to filter by in visuals).

Creative

Design a mini dashboard concept using a calendar table, a sales ranking, and one trend visual. Briefly state which DAX elements you would implement.

Answer

Create Dim_Calendar with CALENDAR + ADDCOLUMNS (Year/Month). Implement SalesRanking measure using RANKX over ALL('Product'). Use a measure MonthlySales = CALCULATE(SUM('Sales'[Amount]), USERELATIONSHIP if needed) and a line chart for trend. Use a table visual showing Product, Total Sales, Sales Rank. Use SUMMARIZECOLUMNS for any export needs.

Activities, Vocabulary, Homework, Practice Task

Activities

1. Create Dim_Calendar and add Year, Month Number, Month Name, Quarter.
2. Write a SUMMARIZECOLUMNS query to show monthly orders for "Clothing".
3. Produce a RANKX measure to rank products by total sales.

Answer

Activity answers: (1) Use CALENDAR + ADDCOLUMNS with YEAR(), MONTH(), FORMAT(), QUARTER(). (2) Use EVALUATE + SUMMARIZECOLUMNS grouping by date month and filtering product category = "Clothing". (3) SalesRank = RANKX(ALL('Product'[Product Name]), [Total Sales],,DESC,DENSE).

Vocabulary

• Calculated Table
• Calculated Column
• Measure
• Filter Context
• Row Context
• Iterating Function

Answer

Definitions: Calculated Table — table built by DAX expressions; Calculated Column — stored per-row value; Measure — calculation evaluated in filter context; Filter Context — the set of filters applied to an expression; Row Context — the single-row evaluation context; Iterating Function — function like SUMX that evaluates an expression row-by-row.

Homework

1. Implement a calendar table and use it to calculate Year-to-Date sales.
2. Create a measure that computes average profit per order for a chosen category and display it by month.

Answer

Hints: YTD Sales = TOTALYTD([Total Sales], 'Dim_Calendar'[Date]); AvgProfitPerOrder = DIVIDE([Total Sales Profit],[Orders]). Use CALCULATE with FILTER if category specific.

Practice Task

Given a Sales table with Quantity, Unit Price, Discount, build a calculated column Total Cost and then a summary table showing average Total Cost by Product Category.

Answer

Calculated column: Total Cost = 'Sales'[Quantity] * 'Sales'[Unit Price] * (1 - 'Sales'[Discount]). Summary: SalesSummary = SUMMARIZE('Sales', 'Product'[Category], "AvgTotalCost", AVERAGE('Sales'[Total Cost])) or use SUMMARIZECOLUMNS with an appropriate measure.

End of lecture sheet — practice iteratively and use small tests to validate DAX logic before publishing reports.