EPEnglishPreps Log InFree Sign Up
Home › Academic Composition › Other Compositions

Course (Part 750)

Other Compositions · English · 3 Dec 2025

🎯 Other Compositions🌐 English📅 3 Dec 2025📖 Course 39 of 683
📚 All Other Compositions compositions
🧠 Test Your Skill10 questions from this composition
Other CompositionsCourse (Part 750)

Course: Microsoft Power BI: Beginner to Advanced Intelligence

Welcome to the course! This module introduces Power BI’s DAX (Data Analysis Expressions) as the engine for creating calculated columns, measures, and tables that drive professional reports. We begin with calculated tables—especially the calendar table—because a robust date dimension unlocks time intelligence and consistent period calculations. You will learn the CALENDAR pattern: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])). Summarization is central to DAX; SUMMARIZECOLUMNS lets you group by dimensions and compute measures in one pass. Use DIVIDE for safe ratios (e.g., Avg Profit per Order = DIVIDE([Total Sales Profit],[Orders])). Remember queries (EVALUATE) do not inherit Power BI visual sort order, so include ORDER BY when you need explicit ordering. For targeted exploration, START AT lets you page or begin output from a specific key (for example START AT "SO43661"). We’ll also cover relationships, active vs. inactive relations, USERELATIONSHIP, CROSSFILTER, and how relationships act as filters in calculations. Best practice: do heavy, destructive cleaning in Power Query and use DAX for analytical, context-aware measures. Hands-on exercises will move you from simple SUMs to CALCULATE with complex filters and time-intelligence (YTD/MTD/YoY). By the end of this chapter you will be able to design a star schema, write efficient DAX summaries, and produce clear, interactive dashboards. Let’s get started—practice and iteration are the fastest routes to mastery.

Power BI DAX — Lesson Sheet

Compact, academic lecture sheet with Q&A and exercises. Includes DAX examples and bilingual help (EN → বাংলা).

Lesson

Subtitle: Introduction to DAX, Calculated Tables, Summaries, and Query Patterns

Learning Outcomes

  • Understand the role of DAX in Power BI and when to use Power Query vs DAX.
  • Create calculated tables (calendar) and basic measures (SUM/COUNT/DIVIDE).
  • Summarize datasets with SUMMARIZECOLUMNS and write DAX queries (EVALUATE, ORDER BY, START AT).
  • Apply CALCULATE with filters, time intelligence (YTD/MTD), and relationship modifiers (USERELATIONSHIP, CROSSFILTER).
  • Design simple star schema relationships and use them as filters in measures.

Conceptual Explanation

DAX is a formula language optimized for relational, columnar models. Use calculated tables to create dimensions such as a calendar (Dim_Calendar = CALENDAR(MIN(...), MAX(...))). Aggregation queries use SUMMARIZECOLUMNS for grouped results and measures compute dynamic values within visual filter contexts. When using DAX queries (EVALUATE) remember to explicitly specify ORDER BY because visual sort settings are not applied. START AT allows paging or focused result sets. CALCULATE modifies filter context and supports adding/removing filters (ALL, ALLEXCEPT, REMOVEFILTERS). Relationships push filters across tables and can be toggled in measures using USERELATIONSHIP or overridden with CROSSFILTER. Prefer Power Query for destructive ETL (remove rows, change types) and use DAX for analytical, context-aware calculations (running totals, YTD, comparisons).

Bilingual (EN → বাংলা)

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

বাংলা: ক্যালেন্ডার টেবল তৈরি: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))

Use SUMMARIZECOLUMNS to group and DIVIDE for safe division. / SUMMARIZECOLUMNS দিয়ে গ্রুপ করুন এবং DIVIDE ব্যবহার করে নিরাপদভাবে ভাগ করুন।

Important Notes

  • Use DIVIDE(numerator, denominator, alternate) to avoid division-by-zero.
  • DAX queries ignore Power BI visual sort; use ORDER BY in queries.
  • Prefer Power Query for structural cleanup; DAX for context-aware metrics.
  • Test measures after changing relationships—inactive relationships are not used unless activated (USERELATIONSHIP).

Key Examples & Patterns (Reference)

Calculated Table — Calendar

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

Creates a contiguous date table from min→max date in the fact table. Mark as Date table in model for time intelligence.

Summarize (group & measures)

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
Answer

Groups by month and category, filters for Clothing, calculates orders and average profit per order. ORDER BY enforces chronological output.

Query & Pagination

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

Evaluates sales orders sorted ascending and starts output from the specified order key. Useful for paging and focused inspection.

CALCULATE with Filters

Total Electronics Sales =
CALCULATE(
  SUM('Sales'[SalesAmount]),
  'Product'[Category] = "Electronics"
)
Answer

CALCULATE overrides the filter context for the expression, returning total sales just for Electronics.

Exercises → True / False / Fill in the Blanks / MCQ / Matching / Descriptive / Creative

Activities

  1. Create a calendar table for Fact_Sales and mark it as Date table.
  2. Build a SUMMARIZECOLUMNS query that reports monthly sales and orders for "Clothing".
  3. Write a measure for Net Sales = Sales − Withdrawals and show on a Card visual.
  4. Use CALCULATE with USERELATIONSHIP to compute Sales by ShipDate.
Answer

Examples and patterns are in the reference section above; use CALENDAR, SUMMARIZECOLUMNS, DIVIDE, CALCULATE and USERELATIONSHIP as shown.

Vocabulary

  • CALCULATE — change filter context for an expression.
  • SUMMARIZECOLUMNS — group and return a table of aggregates.
  • DIVIDE — safe division with alternate result.
  • USERELATIONSHIP — activate an inactive relationship in a measure.
  • CROSSFILTER — change filter propagation direction.
Answer

Memorize these; they recur across examples and exercises.

Homework

  1. Import a CSV of your own transactions and create Dim_Calendar.
  2. Make measures: Total Sales, Total Withdrawals, Net Sales (with COALESCE/IFERROR handling).
  3. Design three card visuals (Total Sales, Net Sales, MoM Growth).
Answer

Use CALCULATE for period measures, TOTALYTD/TOTALMTD for time intelligence, and DIVIDE/COALESCE to avoid errors.

Practice Task

Write a DAX measure to compute 3-month moving average of SalesAmount and visualize it on a line chart.

Answer 
Sales_MA_3 =
AVERAGEX(
  DATESINPERIOD(
    Dim_Calendar[Date],
    LASTDATE(Dim_Calendar[Date]),
    -3,
    MONTH
  ),
  CALCULATE(SUM('Sales'[SalesAmount]))
)

This computes the average sales over the last 3 months.

Q&A — Quick Check

True / False

  • TF1: DAX queries inherit Power BI visual sort order. —
    Answer

    False. Use ORDER BY in the DAX query.

  • TF2: Use Power Query for structural, destructive cleanup. —
    Answer

    True. ETL belongs in Power Query; DAX is for analytical logic.

Fill in the Blanks

  1. To avoid division-by-zero use: _______(numerator, denominator, alternate). —
    Answer

    DIVIDE

  2. Generate a continuous date range with: _______(MIN(...), MAX(...)). —
    Answer

    CALENDAR

MCQ

  1. Which function activates an inactive relationship in a calculation?
    • A. CROSSFILTER
    • B. USERELATIONSHIP
    • C. ALL
    • D. RELATED
    Answer

    B — USERELATIONSHIP activates a specified inactive relationship within CALCULATE.

Matching

Match function to purpose:

FunctionPurpose
ALLRemove filters from a column/table
SUMMARIZECOLUMNSGroup and aggregate with optional filters
USERELATIONSHIPTemporarily activate inactive relationship
Answer

Row mapping is shown in the table. Use these building blocks when composing complex measures.

Descriptive

Describe when to use CALCULATE vs a simple SUM measure.

Answer

Use SUM('Table'[Column]) for a straight aggregation. Use CALCULATE to change filter context (apply/remove filters, evaluate under different relationships, compute period-specific values).

Creative

Design a single-page Wealth dashboard wireframe: list 6 visuals and the main measure behind each.

Answer
  1. Card — Total Net Worth (Total Assets − Total Liabilities)
  2. Line chart — Net Worth Over Time (running total)
  3. Bar chart — Monthly Income vs Expenses (Total Income, Total Expense)
  4. Pie/Donut — Expense by Category (SUM by category)
  5. Map — Assets by Location (geo sums)
  6. Table — Recent Transactions (paginated DAX query START AT)

Further Reading & Tips

  • Practice: create example models and convert complex Excel logic into DAX.
  • Debugging: use DAX Studio to profile queries and evaluate performance.
  • Keep ETL in Power Query; keep analytical logic in DAX.

Similar compositions

Course (Part 793)Other CompositionsCourse (Part 792)Other CompositionsCourse (Part 790)Other CompositionsCourse (Part 789)Other CompositionsCourse (Part 784)Other CompositionsCourse (Part 783)Other Compositions
🎓 Want more writing practice?Make academic composition sheets for any class, subject and chapter on AlphaGenEdu - free.
Create free account →