■ Complete Power BI

Notes

Beginner to Advanced

Power Query & ETL Data Modeling DAX Basics to Advanced

Visualizations Dashboards Performance Optimization

Comprehensive Study Guide & Reference Handbook

Covers ETL · DAX · modeling · visualizations · dashboards · real business use cases
Table of Contents
1. Power BI Overview
1.1 What is Power BI?
1.2 Power BI Architecture
1.3 Key Components
1.4 Workflow

2. Data Loading & Power Query

2.1 Connecting to Data Sources
2.2 Power Query Editor
2.3 Transformations
2.4 M Language Basics

3. Data Modeling
3.1 Star Schema
3.2 Relationships
3.3 Cardinality & Direction
3.4 Calculated Columns vs Measures

4. DAX – Basics
4.1 What is DAX?
4.2 Syntax & Operators
4.3 Basic Measures
4.4 Common Functions

5. DAX – Intermediate
5.1 CALCULATE
5.2 Filter Context vs Row Context
5.3 Time Intelligence
5.4 Variables

6. DAX – Advanced
6.1 FILTER & ALL
6.2 Iterator Functions
6.3 RANKX & TOPN
6.4 Dynamic Measures

7. Visualizations
7.1 Visual Types
7.2 Formatting
7.3 Interactions
7.4 Custom Visuals

8. Dashboards & Reports

8.1 Reports vs Dashboards
8.2 Pages & Navigation
 8.3 Bookmarks
8.4 Drill-Through

9. Performance Optimization
9.1 Data Model Best Practices
9.2 DAX Optimization
9.3 Power Query Performance
9.4 Aggregations

10. Real Business Use Cases

10.1 Sales Dashboard
10.2 HR Analytics
10.3 Financial Reporting

11. Practice Questions

Beginner · Intermediate · Advanced

12. Power BI Cheat Sheet

Quick Reference
Chapter 1: Power BI Overview

1.1 What is Power BI?

■ Definition: Power BI is Microsoft's business analytics service that enables users to connect to data,
transform it, model it, and create interactive reports and dashboards. It is available as Power BI Desktop
(free), Power BI Service (cloud), and Power BI Mobile.

Edition Description Best For

Free Windows application for report

Power BI Desktop Report developers & analysts
authoring

Teams, collaboration,
Power BI Service Cloud platform ([Link]) for sharing
scheduling

Power BI Mobile iOS/Android app for consuming reports Managers, field users

Regulated industries, no
Power BI Report Server On-premises report hosting
cloud

Developers building ISV

Power BI Embedded Embed reports in custom apps via API
solutions

Power BI Premium Dedicated capacity; paginated reports; XMLA Enterprise, large datasets

1.2 Power BI Architecture

The Power BI architecture follows a layered approach:

■ Architecture Flow
Data Sources
■
▼
[Power Query / Get Data] ■■■ ETL: Extract, Transform, Load
■
▼
[Data Model] ■■■ Tables, Relationships, Calculated Columns, Measures (DAX)
■
▼
[Report Canvas] ■■■ Visuals, Pages, Filters, Slicers
■
▼
[Power BI Service] ■■■ Publish, Share, Schedule Refresh, Dashboards
■
▼
[Consumers] ■■■ Browser, Mobile, Embedded, Teams

1.3 Key Components

Component Purpose

Get Data Connect to 100+ data sources (Excel, SQL, APIs, SharePoint, etc.)

Power Query Editor ETL tool — clean, shape, transform data using M language

Data Model Define tables, relationships, hierarchies; write DAX

Report View Drag-and-drop canvas for building interactive reports

DAX Data Analysis Expressions — formula language for calculations

Service (Cloud) Publish, share, schedule refresh, create dashboards

Gateway Bridge between on-premises data and Power BI Service

Chapter 2: Data Loading & Power
Query

2.1 Connecting to Data Sources

Power BI supports 100+ connectors. Go to Home → Get Data.

Source Category Examples

File Excel (.xlsx), CSV, JSON, XML, PDF, SharePoint Folder

Database SQL Server, MySQL, PostgreSQL, Oracle, Azure SQL DB

Cloud Azure Blob, Dataverse, Snowflake, Google BigQuery, Redshift

Online Services SharePoint, Dynamics 365, Salesforce, Google Analytics

Other Web (URL scraping), OData Feed, ODBC, REST API (blank query)

2.2 Power Query Editor

Power Query Editor is the ETL environment. Every action creates a Step in the Applied Steps pane — all
steps are recorded as M code.

■ Power Query UI Sections

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
■ Ribbon: Home | Transform | Add Column | View ■
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
■ Queries Pane ■ Data Preview (column headers + rows) ■
■ (left sidebar) ■ ■
■ - All loaded tables ■ ■
■ ■ ■
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
■ Applied Steps (right sidebar) ■
■ Source → Navigation → Promoted Headers → Changed Type → ... ■
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

2.3 Key Transformations

Transformation Location Description

Promote Row as Headers Transform tab Make first row the column headers

Change Data Type Transform tab Set column to Text/Number/Date etc.

Remove Columns Right-click column Delete unneeded columns

Keep/Remove Rows Home tab Filter rows by condition, top/bottom n

Split Column Transform tab Split by delimiter or # of characters

 Transformation Location Description

Merge Columns Transform tab Combine columns into one string

Group By Transform tab Aggregate like SQL GROUP BY

Pivot Column Transform tab Rows → columns (like Excel pivot)

Unpivot Columns Transform tab Columns → rows (normalise wide data)

Merge Queries Home tab SQL-style JOIN between two queries

Append Queries Home tab SQL-style UNION ALL of two queries

Add Custom Column Add Column tab Write M expression for new column

Conditional Column Add Column tab IF/ELSE logic for new column

Fill Down/Up Transform tab Propagate values to fill NULLs

Replace Values Transform tab Find and replace specific values

2.4 M Language Basics

■ Definition: M (Power Query Formula Language) is a functional, case-sensitive language that Power
Query uses under the hood. Every query is an M expression.

■ M Language Examples
// Basic query structure
let
Source = [Link]([Link]("C:\[Link]"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
PromotedHeaders = [Link](Sheet1_Sheet),
ChangedType = [Link](PromotedHeaders,{
{"Date", type date},
{"Sales", type number}
}),
FilteredRows = [Link](ChangedType,
each [Sales] > 1000),
AddedColumn = [Link](FilteredRows, "Tax",
each [Sales] * 0.2, type number)
in
AddedColumn

// Useful M functions:
// [Link](table, each [col] > value)
// [Link](table, "Name", each expression)
// [Link]([column])
// [Link]([DateColumn])
// [Link]([Amount], 2)
// [Link]({1,2,3}) → 6
// [Link]({"a","b","c"}, "-") → "a-b-c"
Chapter 3: Data Modeling

3.1 Star Schema

■ Definition: The star schema is the recommended data model in Power BI. It has a central Fact Table
(transactions, events) surrounded by Dimension Tables (descriptive attributes).

■ Star Schema Structure

■■■■■■■■■■■■■■■■■■■■
■ DimDate ■
■ DateKey (PK) ■
■ Year, Month... ■
■■■■■■■■■■■■■■■■■■■■
■
■■■■■■■■■■■■ ■ ■■■■■■■■■■■■■■■■■■■■
■DimProduct■ ■ ■ DimCustomer ■
■ProductKey■ ■ ■ CustomerKey (PK) ■
■ (PK) ■ ■ ■ Name, Region... ■
■ Name, ■ ■ ■■■■■■■■■■■■■■■■■■■■
■ Category ■ ■ ■
■■■■■■■■■■■■ ■ ■
■ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
■ ■ FactSales ■
■■■■■■■ DateKey (FK) ■■ ProductKey (FK) ■
■ CustomerKey (FK) ■
■ SalesAmount, Quantity, Cost ■
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

Benefits:
• Simple, easy-to-understand structure
• Fewer table joins = better performance
• DAX formulas are simpler
• Clear separation: descriptive (dims) vs numeric (facts)

3.2 Relationships
Relationships define how tables are connected. In Power BI, go to Model View to create and manage
relationships.

Setting Options Recommendation

One-to-Many (1:*), One-to-One (1:1), Use 1:* (star schema). Avoid

Cardinality
Many-to-Many (*:*) *:* when possible

Use Single (dim → fact). Both

Cross-filter direction Single, Both
only when needed

Only one active per table pair;

Active / Inactive Active (solid line), Inactive (dashed) use USERELATIONSHIP for
inactive
 Setting Options Recommendation

Keys must have matching

Relationship key FK column in fact links to PK in dimension data types; no spaces in
names

3.3 Calculated Columns vs Measures

Aspect Calculated Column Measure

Evaluated at Data load / refresh Query time (when used in visual)

Stored in Data model (RAM) Not stored — computed on demand

Has row context (can reference row

Row context No row context by default
values)

Filter context No filter context by default Has filter context

Used for Segmentation, flags, lookup values KPIs, aggregations, dynamic calcs

Performance Uses RAM; expensive for large tables Efficient; computed only when needed

Syntax Column = Expression (row by row) Measure = DAX aggregation

Chapter 4: DAX – Basics

4.1 What is DAX?

■ Definition: DAX (Data Analysis Expressions) is the formula language used in Power BI, Power Pivot,
and Analysis Services. It is used to create calculated columns, measures, and tables. DAX is similar to
Excel formulas but operates on entire columns and tables.

4.2 DAX Syntax Fundamentals

■ DAX Syntax Rules
// Measure syntax
Measure Name = DAX_Expression

// Reference: TableName[ColumnName]
Total Sales = SUM(FactSales[SalesAmount])

// Spaces in names require single quotes

Revenue = SUM('Sales Table'[Revenue Amount])

// Comments
// Single-line comment
/* Multi-line
comment */

// DAX is NOT case-sensitive

sum(FactSales[SalesAmount]) -- same as SUM(...)

// Operators
// Arithmetic: + - * /
// Comparison: = <> > < >= <=
// Logical: && || ! (AND, OR, NOT also work)
// Text: & (concatenation)
// IN: [Column] IN {val1, val2}

4.3 Basic Measures

■ Common Basic Measures
// Aggregation measures
Total Sales = SUM(FactSales[SalesAmount])
Total Quantity = SUM(FactSales[Quantity])
Avg Sale Amount = AVERAGE(FactSales[SalesAmount])
Min Sale = MIN(FactSales[SalesAmount])
Max Sale = MAX(FactSales[SalesAmount])
Row Count = COUNTROWS(FactSales)
Distinct Customers = DISTINCTCOUNT(FactSales[CustomerKey])
// Ratio / percentage
Profit Margin % =
DIVIDE(
SUM(FactSales[Profit]),
SUM(FactSales[SalesAmount]),
0 // alternative result if denominator = 0
)

// Conditional count
High Value Orders =
COUNTROWS(
FILTER(FactSales, FactSales[SalesAmount] > 1000)
)

// Text concatenation in calculated column

FactSales[Full Name] = FactSales[FirstName] & " " & FactSales[LastName]

4.4 Essential DAX Functions

Category Function Description

Aggregation SUM(col) Sum of all values in column

Aggregation AVERAGE(col) Average of non-blank values

Aggregation COUNT(col) Count of numbers

Aggregation COUNTA(col) Count of non-blank values

Aggregation COUNTROWS(table) Number of rows in table

Aggregation DISTINCTCOUNT(col) Count of unique values

Math DIVIDE(a,b,alt) Safe division (no div-by-zero error)

Math ROUND(n,d) Round to d decimal places

Math ABS(n) Absolute value

Text CONCATENATE(a,b) Join two strings (use & operator instead)

Text LEFT/RIGHT/MID Substring extraction

Text FORMAT(val,fmt) Format as text: FORMAT(Date,"MMMM")

Text VALUE(text) Convert text to number

Date TODAY() Today's date

Date NOW() Current date and time

Date YEAR/MONTH/DAY(date) Extract date parts

Date DATEDIFF(d1,d2,unit) Difference between two dates

Date DATE(y,m,d) Construct a date

Logical IF(cond,t,f) Conditional expression

Logical SWITCH(expr,v1,r1,...) Multi-way conditional (like CASE)

Category Function Description

Logical AND/OR/NOT Logical operators

Logical IFERROR(expr,alt) Return alt if expr errors

Logical ISBLANK(expr) Check if value is blank

Chapter 5: DAX – Intermediate

5.1 CALCULATE — The Most Important DAX Function

■ Definition: CALCULATE evaluates an expression in a modified filter context. It is the cornerstone of
advanced DAX. Syntax: CALCULATE(expression, filter1, filter2, ...)

■ CALCULATE Examples
// Sales for a specific category
Electronics Sales =
CALCULATE(
SUM(FactSales[SalesAmount]),
DimProduct[Category] = "Electronics"
)

// Sales for current year

Current Year Sales =
CALCULATE(
SUM(FactSales[SalesAmount]),
YEAR(DimDate[Date]) = YEAR(TODAY())
)

// Remove existing filters (ALL)

All Products Sales =
CALCULATE(
SUM(FactSales[SalesAmount]),
ALL(DimProduct) -- ignores any product filter
)

// Market share %
Market Share % =
DIVIDE(
SUM(FactSales[SalesAmount]),
CALCULATE(SUM(FactSales[SalesAmount]), ALL(DimProduct))
)

// CALCULATE with multiple filters (AND logic)

High Value Electronics =
CALCULATE(
COUNTROWS(FactSales),
DimProduct[Category] = "Electronics",
FactSales[SalesAmount] > 500
)

// CALCULATETABLE — returns a table instead of a scalar

Top Products Table =
CALCULATETABLE(
DimProduct,
DimProduct[Category] = "Electronics"
)
5.2 Filter Context vs Row Context
Row context = iterating through rows (in calculated columns and iterator functions like SUMX). Filter
context = the active filters applied by slicers, report filters, and CALCULATE.

■ Context Examples
// Calculated COLUMN — has row context
// Each row knows its own SalesAmount
FactSales[Tax Amount] = FactSales[SalesAmount] * 0.1

// MEASURE — has filter context, NO row context by default

Total Tax = SUM(FactSales[SalesAmount]) * 0.1
// ↑ This is WRONG for % — measures aggregate first then multiply

// Correct approach using iterator:

Total Tax =
SUMX(
FactSales,
FactSales[SalesAmount] * 0.1
)
// SUMX creates row context row-by-row, then sums

// EARLIER — access outer row context in nested iterations

// (rarely needed with modern DAX; use VARIABLES instead)

5.3 Time Intelligence Functions

■ Definition: Time Intelligence functions work with date tables. Requirements: (1) A Date/Calendar
table, (2) marked as Date Table in Power BI, (3) a continuous date range.

■ Time Intelligence
// Month-to-Date (MTD)
Sales MTD =
CALCULATE(
SUM(FactSales[SalesAmount]),
DATESMTD(DimDate[Date])
)

// Quarter-to-Date (QTD)
Sales QTD =
CALCULATE(
SUM(FactSales[SalesAmount]),
DATESQTD(DimDate[Date])
)

// Year-to-Date (YTD)
Sales YTD =
CALCULATE(
SUM(FactSales[SalesAmount]),
DATESYTD(DimDate[Date])
)

// Same period last year (SPLY)

 Sales SPLY =
CALCULATE(
SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date])
)

// Year-over-Year growth %
YoY Growth % =
DIVIDE(
SUM(FactSales[SalesAmount]) -
CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date])),
CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date]))
)

// Previous month
Prev Month Sales =
CALCULATE(
SUM(FactSales[SalesAmount]),
PREVIOUSMONTH(DimDate[Date])
)

// Rolling 3-month average

Rolling 3M Avg =
AVERAGEX(
DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -3, MONTH),
[Total Sales]
)

5.4 Variables (VAR)

■ Definition: Variables in DAX store intermediate results for reuse within a measure. They improve
readability, debugging, and performance.

■ Using VAR...RETURN
// Without variables (hard to read, evaluated twice)
YoY Growth % =
DIVIDE(
SUM(FactSales[SalesAmount]) -
CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date])),
CALCULATE(SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date]))
)

// With variables (clean, each expression evaluated ONCE)

YoY Growth % =
VAR CurrentSales = SUM(FactSales[SalesAmount])
VAR PriorYearSales =
CALCULATE(
SUM(FactSales[SalesAmount]),
SAMEPERIODLASTYEAR(DimDate[Date])
 )
VAR YoYChange = CurrentSales - PriorYearSales
RETURN
DIVIDE(YoYChange, PriorYearSales, BLANK())
Chapter 6: DAX – Advanced

6.1 FILTER, ALL, ALLEXCEPT, ALLSELECTED

■ Filter Modifier Functions
// ALL — remove all filters from a table or column
All Sales = CALCULATE(SUM(FactSales[SalesAmount]), ALL(FactSales))

// ALLEXCEPT — remove all filters EXCEPT specified columns

Regional Share =
DIVIDE(
SUM(FactSales[SalesAmount]),
CALCULATE(SUM(FactSales[SalesAmount]),
ALLEXCEPT(FactSales, FactSales[Region]))
)

// ALLSELECTED — respect user slicer selections but ignore visual filters

Pct of Sliced Total =
DIVIDE(
SUM(FactSales[SalesAmount]),
CALCULATE(SUM(FactSales[SalesAmount]), ALLSELECTED())
)

// FILTER — explicit table filter returning a filtered table

Premium Orders =
CALCULATE(
SUM(FactSales[SalesAmount]),
FILTER(FactSales, FactSales[Profit] > 200)
)

// Combining FILTER and CALCULATE

Top Region Sales =
CALCULATE(
SUM(FactSales[SalesAmount]),
FILTER(
ALL(DimRegion),
DimRegion[RegionName] = "North America"
)
)

6.2 Iterator Functions (X-functions)

■ Definition: Iterator functions iterate row-by-row over a table, evaluate an expression for each row in
row context, then aggregate the results.

■ Iterator (X) Functions

// SUMX — row-by-row calculation then sum
Revenue After Discount =
SUMX(
 FactSales,
FactSales[Quantity] * FactSales[UnitPrice] * (1 - FactSales[Discount])
)

// AVERAGEX — row-level calculation then average

Avg Profit Per Order =
AVERAGEX(
FactSales,
FactSales[Revenue] - FactSales[Cost]
)

// COUNTX — count rows where expression is non-blank

Orders With Discount =
COUNTX(
FILTER(FactSales, FactSales[Discount] > 0),
FactSales[OrderID]
)

// MAXX / MINX
Best Single Sale = MAXX(FactSales, FactSales[SalesAmount])

// PRODUCTX — multiply all values

// CONCATENATEX — concatenate with separator
Product Names =
CONCATENATEX(
DimProduct,
DimProduct[ProductName],
", ",
DimProduct[ProductName], ASC
)

6.3 RANKX & TOPN

■ RANKX and TOPN
// RANKX — rank products by sales
Product Rank =
RANKX(
ALL(DimProduct), // ranking over all products
[Total Sales], // measure to rank by
, // value (blank = use current row)
DESC, // direction
Skip // ties handling: Skip or Dense
)

// Show only top 5 (use this in a visual-level filter)

Top 5 Flag =
IF([Product Rank] <= 5, "Top 5", "Other")

// TOPN — return top N rows as a table

Top 10 Products =
TOPN(
10,
ALL(DimProduct),
 [Total Sales],
DESC
)

// Dynamic top N using parameter

Top N Sales =
CALCULATE(
[Total Sales],
TOPN([Top N Value], ALL(DimProduct), [Total Sales])
)

6.4 Dynamic Measures with What-If Parameters

■ Dynamic Measures
// Create a What-If Parameter in Power BI:
// Modeling → New Parameter → Numeric Range
// This auto-creates a table and a slicer

// Example: Dynamic discount rate (0-50%, step 1%)

// Power BI creates:
// Discount Rate = GENERATESERIES(0, 0.5, 0.01)
// Discount Rate Value = SELECTEDVALUE('Discount Rate'[Discount Rate], 0)

// Use in a measure:
Discounted Sales =
[Total Sales] * (1 - [Discount Rate Value])

// Dynamic currency conversion

Converted Sales =
[Total Sales] * [Exchange Rate Value]

// Field Parameters (Power BI Nov 2022+)

// Allow users to swap measures/dimensions dynamically in visuals
Chapter 7: Visualizations

7.1 Visual Types & When to Use Them

Visual Best For Key Settings

X-axis: category, Y-axis: value, Legend:

Bar/Column Chart Compare categories
series

Line Chart Trends over time X-axis: date, Y-axis: measure, Line: series

Area Chart Trend + volume Filled line chart

Pie/Donut Chart Part-to-whole (max 5-7 slices) Legend: category, Values: measure

Correlation between two

Scatter Plot X-axis, Y-axis, Size (optional)
measures

Map / Filled Map Geographic distribution Location: field, Size/Color: measure

Card Single KPI value Fields: measure

Multi-row Card Multiple KPIs Fields: multiple measures

KPI Visual Metric vs target with trend Value, Target, Trend axis

Table Detailed tabular data Rows, Values, Conditional formatting

Matrix Pivot table style Rows, Columns, Values (aggregated)

Treemap Hierarchical part-to-whole Group, Size, Color

Funnel Chart Stage-by-stage conversion Values: stages in order

Waterfall Chart Running total / bridge Category, Y-axis

Gauge Progress vs target Value, Min, Max, Target

Ribbon Chart Ranking changes over time Axis: time, Legend: category

7.2 Formatting Best Practices

■ Best Practice: Use consistent colours — one accent colour for highlights, neutral for everything else.
■ Best Practice: Always label axes and add a descriptive title to every visual.
■ Best Practice: Use conditional formatting to draw attention to outliers or targets.
■ Common Mistake: Do not use 3D charts — they distort proportions and are hard to read.
■ Common Mistake: Avoid using too many colours — more than 5-7 in one chart becomes confusing.

■ Conditional Formatting Rules

Background Color:
Format by: Rules
Based on field: [Sales Growth %]
 Rule 1: If value < 0 → Red (#FF0000)
Rule 2: If 0 <= value < 10 → Yellow (#FFC000)
Rule 3: If value >= 10 → Green (#00B050)

Data Bars:
Minimum: 0 (Fixed)
Maximum: Highest Value
Positive bar color: Blue
Show bar only: ON/OFF

Icon Sets:
Arrow Up (green) when value > 100%
Arrow Flat (yellow) when 90%-100%
Arrow Down (red) when < 90%

7.3 Slicers & Filters

Filter/Slicer Type Scope Use Case

Visual-level filter Single visual only Limit one chart to top 10

All visuals on one

Page-level filter Focus a page on one region
page

Report-level filter All pages in report Global date range filter

Slicer (visual) Drives other visuals User-controlled filtering

Drillthrough filter Target page only Context from source page

Cross-report filter Another report Linked reports in same workspace

Chapter 8: Dashboards & Reports

8.1 Reports vs Dashboards

Feature Report (Desktop/Service) Dashboard (Service only)

Pages Multiple pages Single scrollable page

Interactivity Full (filters, drill-down) Limited (click through to report)

Data sources One dataset per page Tiles from multiple reports/datasets

Creation Power BI Desktop or Service Power BI Service (pin tiles)

Alerts Not available Available on numeric tiles

Sharing Via workspace/app Direct dashboard sharing

Best for Detailed analysis Executive overview, KPI monitoring

8.2 Bookmarks & Navigation

Bookmarks capture the current state of a report page (visuals, filters, slicer selections). Used for
storytelling, navigation buttons, and toggle views.

■ Bookmark Patterns
Use Cases for Bookmarks:

1. TOGGLE VISUAL
- Bookmark A: Chart visible, Table hidden
- Bookmark B: Chart hidden, Table visible
- Button with Actions → Bookmark (toggles between A and B)

2. PAGE NAVIGATION
- Add buttons to each page
- Set Action → Page Navigation for each button
- Mimics a web app navigation menu

3. STORY / PRESENTATION MODE

- Create a series of bookmarks = steps in a story
- Present in View → Bookmarks pane → Play

4. RESET FILTERS
- Set a "Reset" bookmark with no filters applied
- Link a Reset button to this bookmark

8.3 Drill-Through & Drill-Down

■ Drill Configuration
DRILL-DOWN (within same visual):
 - Requires a hierarchy (Year > Quarter > Month > Day)
- Or manually add multiple fields to Axis
- Click the drill-down arrow icons in visual header

DRILL-THROUGH (to another page):

Step 1: Create a detail page (e.g., "Product Details")
Step 2: Add a field to the "Drill through" filter well
(e.g., DimProduct[ProductName])
Step 3: On source page, right-click a data point
→ Drill through → Product Details

TOOLTIP PAGE:
Step 1: Create a new page
Step 2: Format → Page Information → Tooltip = On
Step 3: Resize to tooltip canvas (e.g., 320x240)
Step 4: On source visual, Format → Tooltip
→ Type = Report Page → Page = your tooltip page
Chapter 9: Performance Optimization

9.1 Data Model Best Practices

Practice Reason

Use star schema Fewer joins, simpler DAX, better compression

Remove unused columns Each column uses RAM

Use integer keys in relationships Faster than string keys

Avoid many-to-many Use bridge tables or reformulate

Mark Date table as Date Table Enables time intelligence functions

Set column data types correctly Wrong types waste memory

Disable Auto Date/Time Creates hidden tables per date column — increases model size

Use Import mode when possible DirectQuery is slower for complex measures

9.2 DAX Optimization

■ Best Practice: Use DIVIDE() instead of / to handle divide-by-zero without errors.
■ Best Practice: Use VAR to avoid computing the same expression multiple times in a measure.
■ Common Mistake: Avoid using FILTER(ALL(table), ...) — use VALUES() or other optimized patterns.
■ Common Mistake: Avoid row-by-row FILTER on large tables — use CALCULATE with direct column filters.

■ DAX Performance Patterns

// SLOW: FILTER iterates all rows
Slow Measure =
CALCULATE([Total Sales],
FILTER(ALL(FactSales), FactSales[Category] = "A"))

// FAST: direct filter on dimension

Fast Measure =
CALCULATE([Total Sales],
DimProduct[Category] = "A")

// Use Performance Analyzer to measure:

// View → Performance Analyzer → Start Recording
// Then interact with visuals to see milliseconds per visual
// Look for high DAX query times → optimize those measures

9.3 Power Query Performance

Best Practice Detail

Filter rows early Reduce data volume before transformations

Remove unused columns at

Smaller dataset to load
source

Avoid native queries in

Use Power Query filters instead
DirectQuery

Disable Load for staging queries Right-click query → Enable Load = OFF

Use query folding Push transformations to data source (SQL push-down)

Avoid custom functions on large

Row-by-row M is slow on millions of rows
tables
Chapter 10: Real Business Use Cases

10.1 Sales Dashboard

A standard sales dashboard typically includes: Total Revenue, YoY Growth, Top 10 Products, Sales by
Region (Map), Monthly Trend (Line chart), and Sales Rep Performance (Bar chart).

■ Key DAX Measures for Sales

// Core measures
Total Revenue = SUM(FactSales[SalesAmount])
Total Cost = SUM(FactSales[COGS])
Gross Profit = [Total Revenue] - [Total Cost]
Gross Margin% = DIVIDE([Gross Profit], [Total Revenue])

// Period comparisons
Revenue LY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
Revenue YoY % =
VAR curr = [Total Revenue]
VAR prev = [Revenue LY]
RETURN DIVIDE(curr - prev, prev)

// Running total
Revenue YTD = CALCULATE([Total Revenue], DATESYTD(DimDate[Date]))

// Ranking
Product Rank = RANKX(ALL(DimProduct), [Total Revenue],, DESC, SKIP)
Top 10 Flag = IF([Product Rank] <= 10, "Top 10", "Other")

// Target vs Actual
vs Target % =
DIVIDE([Total Revenue] - SUM(FactTargets[Target]), SUM(FactTargets[Target]))

10.2 HR Analytics Dashboard

■ HR DAX Measures
// Headcount
Total Employees = COUNTROWS(DimEmployee)
Active Employees = CALCULATE(COUNTROWS(DimEmployee),
DimEmployee[Status] = "Active")
Turnover Rate % =
DIVIDE(
COUNTROWS(FILTER(DimEmployee, DimEmployee[TerminationDate] <> BLANK())),
[Total Employees]
)
Avg Tenure (Years) =
AVERAGEX(
DimEmployee,
DATEDIFF(DimEmployee[HireDate], TODAY(), YEAR)
)
 Headcount by Dept = COUNTROWS(DimEmployee) // slice by DimDept
Salary Budget Util =
DIVIDE(SUM(FactPayroll[ActualSalary]), SUM(FactBudget[BudgetAmount]))

10.3 Financial Reporting

■ Financial DAX Patterns
// P&L structure using SWITCH on account types
PL Amount =
SWITCH(TRUE(),
DimAccount[Type] = "Revenue", SUM(FactGL[Amount]),
DimAccount[Type] = "Expense", -SUM(FactGL[Amount]),
DimAccount[Type] = "Asset", SUM(FactGL[Amount]),
SUM(FactGL[Amount])
)

// Budget vs Actual
Variance = [Actual Amount] - [Budget Amount]
Variance % = DIVIDE([Variance], ABS([Budget Amount]))
Variance Label =
IF([Variance] >= 0,
FORMAT([Variance], "+#,##0;-#,##0"),
FORMAT([Variance], "#,##0;(#,##0)"))

// Cash Flow — cumulative

Cumulative CF =
CALCULATE(
SUM(FactCashFlow[Amount]),
FILTER(
ALL(DimDate),
DimDate[Date] <= MAX(DimDate[Date])
)
)
Chapter 11: Practice Questions

Beginner Level
Q1. What is the difference between a Measure and a Calculated Column in Power BI?
Answer: Calculated column: stored in model, computed at row level during load. Measure: computed at
query time in filter context, not stored.

Q2. Write a DAX measure to calculate the Total Revenue from a FactSales table.
Answer: Total Revenue = SUM(FactSales[SalesAmount])

Q3. How do you remove blank rows from a dataset in Power Query?
Answer: Home tab → Remove Rows → Remove Blank Rows

Q4. What chart type would you use to show sales by month over 3 years?
Answer: Line Chart with Date on X-axis, Sales measure on Y-axis, Year as Legend or small multiples.

Q5. What is a star schema? Why is it recommended in Power BI?

Answer: Central fact table surrounded by dimension tables. Recommended for simpler DAX, better
performance, and clear structure.

Intermediate Level
Q6. Write a DAX measure for Year-over-Year Sales Growth %.
Answer: VAR curr=SUM(Sales[Amt]) VAR
prev=CALCULATE(SUM(Sales[Amt]),SAMEPERIODLASTYEAR(Date[Date])) RETURN
DIVIDE(curr-prev,prev)

Q7. How do you create a drill-through page in Power BI?

Answer: Create detail page → add a field to 'Drill through' filter well → right-click source visual data
point → Drill through.

Q8. What is CALCULATE and how does it modify filter context?

Answer: CALCULATE evaluates an expression with modified filter context. Filter arguments ADD,
REPLACE, or REMOVE filters from the evaluation context.

Q9. Write a measure showing % of Total Sales for each product category.
Answer: % of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(DimProduct)))

Q10. What is query folding in Power Query and why does it matter?
Answer: Query folding pushes transformation steps back to the source as native queries (e.g., SQL).
Faster and more efficient than M processing in Power BI.
Advanced Level
Q11. Explain the difference between FILTER and CALCULATE for filtering. Which is more
performant?
Answer: CALCULATE with column filters is faster — uses Vertipaq engine natively.
FILTER(ALL(table),...) is slower as it iterates rows. Use direct column predicates in CALCULATE.

Q12. Write a DAX measure that ranks products within their category and returns 'Top 3' or 'Other'.
Answer: VAR
rnk=RANKX(ALLSELECTED(DimProduct[Product]),CALCULATE([Sales]),,DESC,DENSE) RETURN
IF(rnk<=3,'Top 3','Other')

Q13. How would you implement a dynamic date slicer that adjusts all time intelligence measures?
Answer: Mark Date table as Date Table; use DATESYTD/MTD functions which automatically use the
Date table's active filter context from slicers.

Q14. Describe Power BI Row-Level Security (RLS) and how to implement static and dynamic RLS.
Answer: Static: Manage Roles → add DAX filter per table per role. Dynamic: [Email] =
USERPRINCIPALNAME() filter on a security mapping table.

Q15. A report with 50 visuals is loading slowly. What steps would you take to optimize it?
Answer: 1) Use Performance Analyzer 2) Reduce visuals per page 3) Optimize slow DAX measures 4)
Remove unused columns 5) Check model relationships 6) Use Import mode 7) Add aggregation tables.
Chapter 12: Power BI Cheat Sheet

DAX Function Reference

Category Function Syntax

Aggregation SUM SUM(Table[Column])

Aggregation SUMX SUMX(Table, Expression)

Aggregation AVERAGE AVERAGE(Table[Column])

Aggregation COUNT / COUNTA COUNT(Table[Column])

Aggregation COUNTROWS COUNTROWS(Table)

Aggregation DISTINCTCOUNT DISTINCTCOUNT(Table[Column])

Aggregation MAX / MIN MAX(Table[Column])

Filter CALCULATE CALCULATE(Expr, Filter1, ...)

Filter ALL ALL(Table) or ALL(Table[Col])

Filter ALLEXCEPT ALLEXCEPT(Table, Table[Col])

Filter ALLSELECTED ALLSELECTED(Table)

Filter FILTER FILTER(Table, Condition)

Time Intel DATESYTD DATESYTD(DateColumn)

Time Intel DATESMTD DATESMTD(DateColumn)

SAMEPERIODLASTYEA
Time Intel SAMEPERIODLASTYEAR(DateColumn)
R

Time Intel PREVIOUSMONTH PREVIOUSMONTH(DateColumn)

Time Intel DATEADD DATEADD(DateCol, -1, YEAR)

Math DIVIDE DIVIDE(Num, Denom, AltResult)

Math ROUND ROUND(Number, Decimals)

Logic IF IF(Condition, True, False)

Logic SWITCH SWITCH(Expr, V1, R1, V2, R2, Else)

Logic IFERROR IFERROR(Expression, AltResult)

Ranking RANKX RANKX(Table, Expr, , DESC, Dense)

Ranking TOPN TOPN(N, Table, OrderBy, DESC)

Text FORMAT FORMAT(Value, FormatString)

Table VALUES VALUES(Table[Column])

 Category Function Syntax

Table SUMMARIZE SUMMARIZE(Table, Col1, "Name", Expr)

Power Query M — Common Functions

M Function Description

[Link](t, each [col]>val) Filter rows

[Link](t, "Name", each expr) Add calculated column

[Link](t, {"col1","col2"}) Remove columns

[Link](t, {{"Old","New"}}) Rename columns

[Link](t,{{"col",type}}) Change data types

[Link](t, {"key"}, {{"Agg",each

Group By
[Link]([col]),type}})

[Link](t, vals, colAttr, valAttr) Pivot columns

[Link](t, {"id"}, "Attr","Val") Unpivot

[Link]/Lower/Trim([col]) String case/trim

[Link]/Month/Day([date]) Extract date parts

[Link]([col], 2) Round numbers

[Link]({1,2,3}) Sum a list

Keyboard Shortcuts — Power BI Desktop

Shortcut Action

Ctrl + Z Undo

Ctrl + S Save

Ctrl + C / V Copy / Paste visual

Alt + Enter New line in DAX editor

Ctrl + Enter Commit formula in DAX editor

Ctrl + / Toggle comment in DAX/M

F2 Rename visual/field

Ctrl + A Select all visuals on canvas

Del Delete selected visual

Ctrl + G Group visuals

Tab Cycle through visuals (accessibility)