Power BI Fundamentals

Power BI là tool BI hàng đầu của Microsoft, tích hợp hoàn hảo với Office 365. Nếu tổ chức dùng Microsoft, Power BI là lựa chọn tối ưu.

🎯 Mục tiêu

Hiểu Power BI ecosystem
Connect và transform data
Build relationships và data model
Viết DAX formulas cơ bản
Create interactive reports

1. Power BI Ecosystem

1.1. Power BI Products

Product	Platform	Purpose	Cost
Power BI Desktop	Windows	Create reports	Free
Power BI Service	Web	Share & collaborate	Free/$10/mo
Power BI Mobile	iOS/Android	View on mobile	Free
Power BI Report Server	On-premise	Internal hosting	License

1.2. Power BI Desktop Interface

Text

1┌─────────────────────────────────────────────────────────────────────┐
2│ File   Home   Insert   Modeling   View   Optimize   Help           │
3├─────────┬─────────────────────────────────────────────────┬────────┤
4│ FILTERS │                                                 │ VISUAL │
5│         │                                                 │IZATIONS│
6│ ┌─────┐ │                                                 │ ┌────┐ │
7│ │     │ │         REPORT CANVAS                           │ │▤▥▦▧│ │
8│ │     │ │                                                 │ │▨▩▪▫│ │
9│ │     │ │    (Drag visuals here)                         │ └────┘ │
10│ │     │ │                                                 │ FIELDS │
11│ └─────┘ │                                                 │ ┌────┐ │
12│         │                                                 │ │ □  │ │
13│         │                                                 │ │ ├─ │ │
14│         │                                                 │ │ └─ │ │
15│         │                                                 │ └────┘ │
16├─────────┴─────────────────────────────────────────────────┴────────┤
17│  [📊 Report]  [📋 Data]  [🔗 Model]                                 │
18└─────────────────────────────────────────────────────────────────────┘

1.3. Three Views

View	Icon	Purpose
Report	📊	Build visualizations
Data	📋	View/edit tables
Model	🔗	Manage relationships

2. Getting Data

2.1. Data Sources

Text

1Get Data button → Categories:
2├── File (Excel, CSV, JSON, PDF)
3├── Database (SQL Server, MySQL, PostgreSQL)
4├── Power Platform (Dataverse, Dataflows)
5├── Azure (Synapse, Blob Storage)
6├── Online Services (SharePoint, Dynamics 365)
7└── Other (Web, ODBC, Python/R scripts)

2.2. Load vs Transform

Option	When to Use
Load	Data is clean, ready to use
Transform	Need cleaning, reshaping

2.3. Power Query Editor

Text

1┌─────────────────────────────────────────────────────────────┐
2│ Home  Transform  Add Column  View  Tools  Help              │
3├─────────────┬───────────────────────────────────────────────┤
4│ QUERIES     │  PREVIEW                                      │
5│ ─────────── │  ───────────────────────────────────────────  │
6│ □ Orders    │  OrderID  Date      Customer   Amount         │
7│ □ Products  │  1001     2024-01   Acme       $5,000         │
8│ □ Customers │  1002     2024-01   Beta Co    $3,200         │
9│             │  1003     2024-02   Gamma Inc  $7,800         │
10├─────────────┼───────────────────────────────────────────────┤
11│ APPLIED     │                                               │
12│ STEPS       │  Formula Bar:                                 │
13│ ─────────── │  = Table.TransformColumnTypes(...)            │
14│ • Source    │                                               │
15│ • Navigation│                                               │
16│ • Changed   │                                               │
17│   Type      │                                               │
18└─────────────┴───────────────────────────────────────────────┘

2.4. Common Transformations

Action	Use Case
Remove columns	Delete unnecessary fields
Filter rows	Remove nulls, errors
Change type	Text → Number, Date
Replace values	Clean up data
Split column	"John Doe" → "John", "Doe"
Merge queries	Join tables
Append queries	Stack tables
Pivot/Unpivot	Reshape data structure

3. Data Modeling

3.1. Star Schema

Text

1┌───────────────┐
2                 │   DIM_Date    │
3                 │   ──────────  │
4                 │   DateKey     │
5                 │   Date        │
6                 │   Month       │
7                 │   Year        │
8                 └───────┬───────┘
9                         │
10┌───────────────┐        │        ┌───────────────┐
11│ DIM_Product   │        │        │ DIM_Customer  │
12│ ──────────── │        │        │ ──────────── │
13│ ProductKey   │◄───────┼───────►│ CustomerKey  │
14│ ProductName  │        │        │ CustomerName │
15│ Category     │        │        │ Segment      │
16└───────────────┘        │        └───────────────┘
17                         │
18                         ▼
19                 ┌───────────────┐
20                 │  FACT_Sales   │
21                 │  ──────────── │
22                 │  SalesKey     │
23                 │  DateKey      │
24                 │  ProductKey   │
25                 │  CustomerKey  │
26                 │  Amount       │
27                 │  Quantity     │
28                 └───────────────┘

3.2. Creating Relationships

Text

1Model View → Drag field from Table A to Table B
2 
3Relationship Properties:
4├── Cardinality: One-to-Many, Many-to-Many
5├── Cross Filter: Single, Both
6├── Active: Yes/No
7└── Referential Integrity: Assume (for performance)

3.3. Best Practices

✅ DO:

Use star schema (facts + dimensions)
Hide technical keys from report view
Create date dimension table
Name tables and columns clearly

❌ DON'T:

Many-to-many without bridge table
Circular dependencies
Too many bi-directional filters
Wide tables với 100+ columns

4. DAX Basics

4.1. What is DAX?

DAX = Data Analysis Expressions

Formula language của Power BI
Similar to Excel formulas but more powerful
Used for measures và calculated columns

4.2. Calculated Column vs Measure

Aspect	Calculated Column	Measure
Calculated	Row by row	On aggregation
Storage	Stored in model	Calculated at runtime
Use	Categorization, filtering	Aggregations, KPIs
Performance	Uses memory	More efficient

4.3. Essential DAX Functions

dax

1-- SUM, AVERAGE, COUNT
2Total Sales = SUM(Sales[Amount])
3Avg Order = AVERAGE(Sales[Amount])
4Order Count = COUNT(Sales[OrderID])
5 
6-- DISTINCTCOUNT
7Customer Count = DISTINCTCOUNT(Sales[CustomerID])
8 
9-- CALCULATE - thay đổi filter context
10Sales East = CALCULATE(
11    SUM(Sales[Amount]),
12    Region[RegionName] = "East"
13)
14 
15-- Time Intelligence
16Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
17Sales PY = CALCULATE(
18    SUM(Sales[Amount]),
19    SAMEPERIODLASTYEAR('Date'[Date])
20)
21 
22-- Year-over-Year Growth
23YoY Growth = 
24VAR CurrentYear = SUM(Sales[Amount])
25VAR LastYear = CALCULATE(
26    SUM(Sales[Amount]),
27    SAMEPERIODLASTYEAR('Date'[Date])
28)
29RETURN 
30DIVIDE(CurrentYear - LastYear, LastYear, 0)

4.4. Filter Context

dax

1-- FILTER context từ slicers, visuals
2-- ALL removes filter context
3 
4Total All Products = CALCULATE(
5    SUM(Sales[Amount]),
6    ALL(Product)  -- Ignore product filter
7)
8 
9-- % of Total
10% of Total = 
11DIVIDE(
12    SUM(Sales[Amount]),
13    CALCULATE(SUM(Sales[Amount]), ALL(Sales)),
14    0
15)

4.5. Variables (VAR)

dax

1-- Clean and readable
2Profit Margin = 
3VAR TotalRevenue = SUM(Sales[Revenue])
4VAR TotalCost = SUM(Sales[Cost])
5VAR Profit = TotalRevenue - TotalCost
6RETURN
7DIVIDE(Profit, TotalRevenue, 0)

5. Building Reports

5.1. Visual Types

Text

1Visualizations Pane:
2┌──────────────────────────────┐
3│  ▤  Stacked bar              │
4│  ▥  Clustered bar            │
5│  ▦  Stacked column           │
6│  ▧  Line chart               │
7│  ▨  Area chart               │
8│  ▩  Combo chart              │
9│  ○  Pie/Donut                │
10│  ⬡  Treemap                  │
11│  🗺  Map                      │
12│  📊 Matrix                   │
13│  📋 Table                    │
14│  📇 Card                     │
15│  🎯 KPI                      │
16│  ⬚  Slicer                   │
17└──────────────────────────────┘

5.2. Building a Visual

Text

11. Click visual type in pane
22. Drag fields to wells:
3   ├── Axis/Categories
4   ├── Values
5   ├── Legend
6   └── Tooltips
7 
83. Format in Format pane:
9   ├── Visual (chart-specific)
10   └── General (borders, title)

5.3. Card Visuals for KPIs

Text

1┌─────────────┐  ┌─────────────┐  ┌─────────────┐
2│ Total Sales │  │   Profit    │  │   Orders    │
3│             │  │             │  │             │
4│  $2.5M      │  │   $420K     │  │   12,547    │
5│   ▲ 15%     │  │   ▲ 8%      │  │   ▲ 22%     │
6└─────────────┘  └─────────────┘  └─────────────┘

DAX for KPI with comparison:

dax

1Sales vs Target = 
2VAR Actual = SUM(Sales[Amount])
3VAR Target = SUM(Target[Amount])
4VAR Diff = Actual - Target
5VAR Emoji = IF(Diff >= 0, "▲", "▼")
6RETURN
7Emoji & " " & FORMAT(ABS(Diff/Target), "0%")

5.4. Slicers & Filters

Slicer types:

List (checkboxes)
Dropdown
Date range (slider)
Relative date ("Last 30 days")

Text

1Format → Slicer settings:
2├── Single select / Multi-select
3├── Show "Select All"
4├── Search box
5└── Orientation (vertical/horizontal)

6. Interactivity

6.1. Cross-filtering

Default behavior:

Text

1Click bar in Chart A → 
2  Chart B filters to show related data
3  Chart C highlights related data

6.2. Edit Interactions

Text

1Format → Edit Interactions
2 
3For each visual, choose:
4├── Filter (subset data)
5├── Highlight (dim non-related)
6└── None (no interaction)

6.3. Drill-through

Text

1Setup:
21. Create detail page
32. Add drill-through field (e.g., Product)
43. Right-click on visual → Drill through
5 
6User experience:
7Main Report: Click "Electronics" →
8  Drill through →
9  Detail page filtered to Electronics

6.4. Bookmarks

Save specific states:

Filters applied
Visuals visible/hidden
Page navigation

Use for:

Toggle views (detailed/summary)
Guided navigation
Presentations

7. Power BI vs Tableau

7.1. Comparison

Aspect	Power BI	Tableau
Price	$10/user/mo	$70/user/mo
Ease	Easier start	Steeper curve
Visualization	Good	Excellent
Data Modeling	Excellent	Good
DAX/Calcs	Very powerful	Powerful
Microsoft	Native integration	Connectors
Community	Growing	Mature

7.2. When to Use Which?

Choose Power BI:

Microsoft 365 organization
Budget-conscious
Need strong data modeling
Self-service BI focus

Choose Tableau:

Visualization-heavy needs
Complex analytics
Cross-platform teams
Premium features needed

8. Hands-on: Sales Report

Project: Executive Sales Dashboard

Data: Sample Sales Data (Excel)

Report Pages:

Page 1: Executive Summary

Text

1┌────────────────────────────────────────────────┐
2│ 💰 Revenue   📈 Growth   📦 Orders   👥 Customers │
3│   $2.5M      +15%        12K         3.2K       │
4├────────────────────────────────────────────────┤
5│ Revenue by Month (Line)  │  Revenue by Region   │
6│                          │  (Map)               │
7├──────────────────────────┼─────────────────────┤
8│ Top Products (Bar)       │  Segment Split      │
9│                          │  (Donut)            │
10└──────────────────────────┴─────────────────────┘
11│ [Date Slicer] [Region] [Segment]               │

Page 2: Product Detail (Drill-through)

Text

1Product: {Selected Product}
2├── Monthly trend
3├── Customer breakdown  
4├── Profit analysis
5└── Comparison to category average

Key DAX Measures

dax

1// Total Revenue
2Revenue = SUM(Sales[Amount])
3 
4// YTD Revenue
5Revenue YTD = TOTALYTD([Revenue], 'Date'[Date])
6 
7// vs Previous Year
8Revenue PY = 
9CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
10 
11// Growth %
12Growth % = 
13DIVIDE([Revenue] - [Revenue PY], [Revenue PY], 0)
14 
15// Profit Margin
16Profit Margin = 
17DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)

📝 Quiz

🎯 Key Takeaways

Power Query = Data preparation
Data Model = Relationships, star schema
DAX = Calculations, measures
Reports = Visuals, interactivity
Power BI Service = Share và collaborate

🚀 Bài tiếp theo

Dashboard Design Best Practices - Học cách thiết kế dashboard chuyên nghiệp, từ layout đến storytelling!