Course (Part 755)

Course: Power BI A-Z: Hands-On Power BI Training For Data Science

Lesson

Topic: Power BI Fundamentals — DAX Basics, Calculated Tables, Summaries, Sorting, and Query Techniques

Learning Outcomes

• Understand DAX roles: calculated columns, measures, and calculated tables.
• Create a calendar table for time intelligence (CALENDAR example).
• Summarize datasets with SUMMARIZECOLUMNS and protect calculations with DIVIDE.
• Control DAX query ordering (ORDER BY) and start point (START AT).
• Apply filters, slicers, and build basic visualizations (bar, area, treemap, map).
• Understand joins, duplicates, aggregation and granularity trade-offs.

Conceptual Explanation

Power BI uses DAX to perform calculations that are either stored per row (calculated columns), computed on demand in visual context (measures), or used to produce new tables (calculated tables). A reliable time dimension is fundamental: build it from your fact table date range. Use SUMMARIZECOLUMNS for grouped summaries and DIVIDE to avoid division by zero. When querying with EVALUATE, include ORDER BY to guarantee sorting because visual sort settings do not apply to DAX queries. For focused data extraction you can use START AT to begin a result set at a given key (helpful for pagination or drilling into a range of keys).

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

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
-- or for a table:
EVALUATE 'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

Bilingual (EN → বাংলা)

Important Notes

• DAX context matters: row context vs filter context — test measures in visuals with slicers to confirm behavior.
• Calculated columns increase model size; prefer measures when aggregation is all that is needed.
• When joining tables, handle duplicates by pre-aggregating with SUMMARIZE to avoid unexpected row multiplications.
• Always guard divisions with DIVIDE and check for NULLs when joining remote datasets.

Exercises

True / False

1. SUMMARIZECOLUMNS inherits Power BI visual sort order automatically.
   Answer

   False — DAX queries require explicit ORDER BY.

2. CALCULATED COLUMNS are evaluated once and stored in the model.
   Answer

   True — they are computed during processing and stored.

3. DIVIDE(x, y) will raise an error if y = 0.
   Answer

   False — DIVIDE handles divide-by-zero and returns alternate result when provided.

Fill in the Blanks

1. To create a calendar table you use the __________ function.
   Answer

   CALENDAR

2. Use __________ to group and return aggregated rows in a DAX query.
   Answer

   SUMMARIZECOLUMNS

3. When dividing measures safely, use the __________ function.
   Answer

   DIVIDE

Multiple Choice (MCQ)

1. What is the best choice for a dynamic aggregation that changes with filters?
   • A. Calculated Column
   • B. Measure
   • C. Static Table
   • D. CSV file
   Answer

   B. Measure — Measures compute on demand and respond to filter context.

2. Which clause is required to guarantee order in an EVALUATE query?
   • A. FILTER BY
   • B. SORT
   • C. ORDER BY
   • D. START AT
   Answer

   C. ORDER BY

Matching

Match the DAX function to its purpose:

Descriptive

1. Explain when you would prefer a calculated column over a measure.
   Answer

   Use a calculated column when you need row-level values stored in the model (for slicers, relationships, or categorization), such as an Age Bin or a static Category label derived from row data. Measures are preferred for aggregations.

2. Describe how you would handle duplicate product IDs when joining Sales to Products.
   Answer

   Pre-aggregate sales by ProductID using SUMMARIZE or SUMMARIZECOLUMNS to one row per ProductID, then join/relate to the Products table to avoid Cartesian-like duplication; or use relationships with properly defined cardinality and aggregation functions (SUMX over filtered summaries when necessary).

Creative

1. Design a short dashboard plan (3 visuals) to decide “Who Gets The Annual Reward?” and list the main DAX measures required.
   Answer

   Dashboard plan: (1) Bar chart showing Total Sales by Employee (measure: Total Sales = SUM(Sales[Amount])), (2) Line chart of Monthly Sales Trend per Employee (measure: Orders = COUNTROWS(Sales) and Month slicer using Dim_Calendar), (3) KPI tiles for Average Profit per Order (Avg Profit = DIVIDE([Total Profit], [Orders])) and % Target Achieved (Measure with CALCULATE and target filter). Use filters/slicers to choose review period and product categories; apply ranking measure (RANKX) to highlight top candidates.

2. Propose a method to color-code chart bars based on threshold (e.g., green > $10k else red).
   Answer

   Create a measure or calculated column returning color name: Sales Colour = IF([Total Sales] > 10000, "Green", "Red") and then apply conditional formatting on the visual by field value. Prefer measure if color should respect current filter context.

Activities, Vocabulary, Homework, Practice Task

Activities

1. Activity 1: Connect to a CSV with sales data and create Dim_Calendar from the date column.
2. Activity 2: Create measures Orders, Total Sales Profit, and Avg Profit per Order; build a monthly SUMMARIZECOLUMNS table and validate with a bar chart.

Vocabulary

• CALENDAR — function to create date tables.
• SUMMARIZECOLUMNS — group-by summary in queries.
• DIVIDE — safe division for measures.
• START AT — begins query results at a specified key.
• RANKX — ranking function used for leaderboards.

Homework

1. Create a Customer_Segment table using SUMMARIZE and classify customers into Low/Medium/High based on total spend; create visuals showing counts and average order values per segment.
2. Write a short report (one page) explaining when to use calculated tables vs query summaries (give two practical examples).

Practice Task

Task: Using your dataset, produce an EVALUATE query that lists Month Name, Product Category, Orders, Avg Profit per Order for category "Clothing", ordered by Month of Year.

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

End of lecture sheet — practice these patterns, and iterate: small models, test measures, then scale up. Ask questions in the next class for any unclear behavior in DAX context or performance.