Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

DAX Introduction and Syntax in Power BI

Data Analysis Expressions (DAX) is a formula language used in Power BI to create calculations from data in a model. DAX helps calculate totals, averages, percentages, year-to-date values, rankings, and many other business metrics.

DAX formulas work with tables, columns, measures, relationships, and filter context. Learning its basic syntax is the first step toward building interactive and reliable Power BI reports.

Learning Objectives

  • Understand what DAX is and where it is used.
  • Learn the basic structure of a DAX formula.
  • Understand calculated columns, measures, and calculated tables.
  • Use table names, column references, operators, and functions.
  • Create basic sales calculations using DAX.
  • Recognize common formula errors and debugging techniques.

1. What Is DAX?

DAX stands for Data Analysis Expressions. It is a formula language designed for working with data models. DAX formulas can calculate results from table columns and respond to filters applied through report visuals, slicers, and relationships.

Data Model Sales Products Customers DAX Formula SUM() CALCULATE() Measures Report Result Total Sales Average Sales Sales by Category DAX transforms model data into useful analytical results.
Diagram 1: DAX formulas use model data to produce calculations for reports.

2. Where Is DAX Used?

DAX Feature Purpose Example
Measure Calculates a result based on the current report filter context. Total Sales
Calculated Column Calculates a value for each row in a table. Profit for each sales row
Calculated Table Creates a table from a DAX expression. A calendar table
Time Intelligence Analyzes data across dates and periods. Year-to-date sales
Filter Calculations Calculates results under specified filter conditions. Sales for a selected category

3. Basic DAX Syntax

A DAX measure typically begins with a name, followed by an equals sign and an expression. Functions use parentheses, and table column references use square brackets for the column name.

Total Sales = SUM(Sales[SalesAmount])

Understand each part of the formula:

  • Total Sales: The name of the measure.
  • = Assigns the expression to the measure.
  • SUM: A DAX function that adds numeric values.
  • Sales: The table containing the column.
  • [SalesAmount]: The column whose values are added.
Measure Name Total Sales = Function SUM Column Reference Sales[SalesAmount] Total Sales = SUM(Sales[SalesAmount]) A named expression combines a function with its required argument.
Diagram 2: The main parts of a simple DAX measure.

4. Create Your First DAX Measure

Assume that your Sales table contains a numeric SalesAmount column. Follow these steps to create a measure.

  1. Open your report in Power BI Desktop.
  2. Locate the Sales table in the Data or Model pane.
  3. Select the Sales table.
  4. Choose New measure from the Modeling or Table tools ribbon.
  5. Enter the DAX expression shown below.
  6. Press Enter to save the measure.
  7. Add a Card visual and place the Total Sales measure in it.
Total Sales = SUM(Sales[SalesAmount])

If the SalesAmount values are 5000, 3000, and 2000, the measure returns 10000 when all three rows are included in the current filter context.

Tip: A measure is evaluated when it is used in a visual or another expression. Its result can change when report filters, slicers, or row and column groupings change.

5. Calculated Columns vs Measures

Calculated columns and measures both use DAX, but they serve different purposes. Understanding the difference helps you choose the right calculation type.

Calculated Column Calculated for each row Stored in the model Useful for row-level attributes Example: Profit = Revenue - Cost Typically computed during model processing. Measure Evaluated when requested Responds to filter context Useful for aggregated results Example: Total Sales = SUM(...) Calculated for the current visual context.
Diagram 3: Calculated columns produce row-level values; measures calculate results for the current context.
Feature Calculated Column Measure
Calculation Level Row by row Based on the current evaluation context
Storage Values are generally stored in the model. The result is calculated when needed.
Typical Uses Row categories, labels, row-level calculations. Totals, averages, ratios, and business KPIs.
Example Profit per row Total Profit

Calculated Column Example

If the Sales table contains Revenue and Cost columns, create a calculated column named Profit.

Profit = Sales[Revenue] - Sales[Cost]

Measure Example

Total Profit = SUM(Sales[Profit])

The calculated column calculates profit for each row. The measure aggregates those row-level values according to the current filters.

6. Common DAX Operators

Operator Meaning Example
+ Addition Sales[Price] + Sales[Tax]
- Subtraction Sales[Revenue] - Sales[Cost]
* Multiplication Sales[Quantity] * Sales[UnitPrice]
/ Division Sales[Revenue] / Sales[Quantity]
= Equality or assignment, depending on usage MeasureName = Expression
> Greater than Sales[Quantity] > 10
<> Not equal to Sales[Status] <> "Cancelled"
&& Logical AND Condition1 && Condition2
|| Logical OR Condition1 || Condition2

7. Useful Beginner DAX Functions

Function Purpose Example
SUM Adds numeric values in a column. SUM(Sales[SalesAmount])
AVERAGE Calculates the average of numeric values. AVERAGE(Sales[SalesAmount])
COUNTROWS Counts rows in a table expression. COUNTROWS(Sales)
DISTINCTCOUNT Counts distinct values in a column. DISTINCTCOUNT(Sales[CustomerID])
IF Returns a result based on a condition. IF([Total Sales] > 10000, "High", "Low")
DIVIDE Performs division with optional handling for a zero or blank denominator. DIVIDE([Total Profit], [Total Sales], 0)

8. Create More Basic Measures

Total Quantity

Total Quantity = SUM(Sales[Quantity])

Average Sales Amount

Average Sales = AVERAGE(Sales[SalesAmount])

Number of Sales Rows

Sales Row Count = COUNTROWS(Sales)

Unique Customers

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Profit Margin

Profit Margin = DIVIDE([Total Profit], [Total Sales], 0)

Format Profit Margin as a percentage using the measure formatting options. This makes the result easier to interpret in a report.

9. Understanding Filter Context

Filter context is the set of filters affecting a DAX calculation. Filters can come from slicers, visual rows and columns, page filters, report filters, and relationships between tables.

Report Filters Year, Category Filter Context Applies to calculation DAX Measure Total Sales All Categories Example total: 100,000 Electronics Filter Example total: 70,000 Accessories Filter Example total: 30,000
Diagram 4: A measure can return different results under different filter contexts.

For example, a Total Sales measure may show 100,000 when no category filter is applied, 70,000 for Electronics, and 30,000 for Accessories. These are illustrative values; actual results depend on your dataset.

Remember: A measure does not simply calculate one permanent total. It evaluates according to the filters and groupings in the current report context.

10. Common DAX Errors

Error Possible Cause How to Fix It
Column not found The table or column name is incorrect. Check the Fields pane and verify the reference.
Syntax error A missing parenthesis, comma, or quotation mark. Review the formula structure and matching parentheses.
Incorrect aggregation A column is being aggregated in an unsuitable way. Choose an appropriate function and verify the data type.
Unexpected measure result Filters or relationships change the calculation context. Test the measure in a simple table visual and inspect the filters.
Division by zero The denominator is zero or blank. Use DIVIDE with an appropriate alternate result.

11. Practical Exercise

Use a Sales table with the following sample records to practice creating basic DAX measures.

SaleID Product Quantity SalesAmount Cost
1 Laptop 2 50000 40000
2 Mouse 3 1500 900
3 Keyboard 2 2500 1600

Tasks

  1. Import the sample data into Power BI Desktop.
  2. Create a measure named Total Sales.
  3. Create a measure named Total Quantity.
  4. Create a measure named Average Sales.
  5. Create a calculated column named Profit.
  6. Create a Total Profit measure.
  7. Create a Profit Margin measure using DIVIDE.
  8. Display the measures in Card visuals.
  9. Build a table visual showing Product, Total Sales, and Total Profit.
  10. Format the monetary measures as currency and Profit Margin as a percentage.

Expected Results

Calculation Expected Value
Total Sales 54,000
Total Quantity 7
Average Sales per Row 18,000
Total Profit 11,500
Profit Margin Approximately 21.30%

12. Knowledge Check

Question 1

What does DAX stand for?

Answer: Data Analysis Expressions.

Question 2

Which DAX function adds numeric values in a column?

Answer: SUM.

Question 3

What is the main difference between a measure and a calculated column?

Answer: A calculated column produces row-level values, while a measure evaluates a calculation for the current context.

Question 4

Why is DIVIDE useful?

Answer: It supports division with an optional alternate result when the denominator is zero or blank.

Question 5

What can cause a measure to return different values in different visuals?

Answer: The visuals may apply different filter contexts or group the data differently.

Summary

In this lesson, you learned what DAX is, how to write a basic formula, and how to reference tables and columns. You also explored measures, calculated columns, common operators, and useful aggregation functions.

You practiced creating sales, quantity, profit, and margin calculations and learned how filter context affects measure results. Always verify column references, data types, formula syntax, and calculation results before using DAX measures in business reports.