My Notes

Go To Home Page

Power BI Notes

Table of Content

PHASE 1: GETTING STARTED WITH POWER BI

PHASE 2: CONNECTING & PREPARING DATA

PHASE 3: DATA MODELING

PHASE 4: DAX (DATA ANALYSIS EXPRESSIONS)

PHASE 5: DESIGNING REPORTS & DASHBOARDS

PHASE 6: PERFORMANCE OPTIMIZATION

🔵 PHASE 1: GETTING STARTED WITH POWER BI

1️⃣ What is Power BI?

Power BI is a:

Business Intelligence tool
Used for dashboards
Used for reports
Used for decision-making

It converts:

Raw data → Insights → Visual dashboards

2️⃣ Power BI Components

1. Power BI Desktop (Development tool)

2. Power BI Service (Cloud)

3. Power BI Mobile

For your course → mainly Power BI Desktop.

3️⃣ Installation (Step-by-Step)

Step 1:

Go to official Microsoft site
Search: Power BI Desktop

Step 2:

Download installer

Step 3:

Install like normal software

Step 4:

Open Power BI Desktop

4️⃣ Power BI Interface (VERY IMPORTANT)

Power BI has 3 main views:

1️⃣ Report View
2️⃣ Data View
3️⃣ Model View

🔹 Report View

Used for:

Creating visuals
Designing dashboards

🔹 Data View

Used for:

Seeing raw data
Checking columns

🔹 Model View

Used for:

Creating relationships
Managing schema

5️⃣ Workflow in Power BI

Step 1: Get Data
Step 2: Transform Data
Step 3: Model Data
Step 4: Create Measures (DAX)
Step 5: Build Visuals
Step 6: Optimize

This is your mental flow.

6️⃣ Adjusting Settings

Go to:

File → Options & Settings → Options

You can configure:

Regional settings
Language
Data load
Security

Important for: Date formats & locale.

🔵 PHASE 2: CONNECTING & PREPARING DATA

(This is where real analysts are made.)

1️⃣ Connecting to Data

Power BI supports many data sources:

Excel
CSV
SQL Server
MySQL
Web
SharePoint
APIs
Cloud databases

🔹 Step-by-Step: Import Excel File

Step 1: Open Power BI Desktop
Step 2: Click Home → Get Data
Step 3: Choose Excel
Step 4: Select your file
Step 5: Click Load (or Transform Data if cleaning needed)

Done

🔹 Import from CSV

Same process:

Home → Get Data → Text/CSV

🔹 Import from SQL Database

Step 1: Home → Get Data → SQL Server
Step 2: Enter:

Server name
Database name

Step 3: Choose Import or DirectQuery

🔹 Import vs DirectQuery (Very Important)

Import:

Data stored inside Power BI
Faster performance
Recommended for small/medium datasets

DirectQuery:

Data stays in database
Real-time updates
Slower performance

2️⃣ Power Query Editor

After clicking "Transform Data", Power Query opens.

This is your data cleaning lab.

Power Query contains:

Preview table
Applied Steps panel
Transform ribbon

Everything you do is recorded in Applied Steps.

3️⃣ Basic Data Cleaning (Step-by-Step)

🔹 Remove Columns

Step: Right click column → Remove OR Home → Remove Columns

🔹 Change Data Type

Step: Click column header icon

Choose correct type:

Text
Whole Number
Decimal
Date

Very important.

Wrong data type = wrong visuals.

🔹 Rename Column

Double click column name Type new name

Clean column names improve DAX later.

🔹 Remove Duplicates

Select column → Remove Rows → Remove Duplicates

🔹 Filter Data

Click filter icon in column header Choose values

4️⃣ Advanced Transformations

🔹 Split Column

Example: Full Name → First Name + Last Name

Step: Select column → Split Column → By Delimiter

🔹 Merge Columns

Select two columns → Merge Columns

🔹 Create New Column (Custom Column)

Step: Add Column → Custom Column

Write formula:

Example:

[Sales] * 0.10

🔹 Replace Values

Select column → Replace Values

Used for:

Fix spelling
Standardize categories

🔹 Group By

Used for aggregation.

Step: Home → Group By

Choose:

Column to group
Operation (Sum, Count, Avg)

5️⃣ Handling Missing Values

🔹 Replace Nulls

Transform → Replace Values → Replace null with 0

🔹 Remove Blank Rows

Home → Remove Rows → Remove Blank Rows

6️⃣ Data Profiling Tools (Very Important)

Power BI provides:

Column distribution
Column quality
Column profile

Enable from:

View → Check Data Profiling options

These help detect:

Null values
Errors
Distribution

7️⃣ Applied Steps Panel (Important Concept)

Every action you perform:

Appears in Applied Steps.

Example:

Source
Changed Type
Removed Columns
Filtered Rows

You can:

Delete step
Modify step
Reorder step

This is why Power Query is powerful.

It records transformation logic.

8️⃣ Close & Apply

After cleaning:

Click: Home → Close & Apply

Data now loads into Power BI model.

🔵 PHASE 3: DATA MODELING

1️⃣ What is Data Modeling?

Data modeling means:

Connecting multiple tables logically
Defining relationships
Structuring data properly

Without modeling:

Measures give wrong results
Filters behave strangely
Dashboard becomes messy

2️⃣ Fact Table vs Dimension Table (VERY IMPORTANT)

This is core concept.

🔹 Fact Table

Contains:

Numerical values
Transactions
Metrics

Example:

Sales Table:

| Date | ProductID | Quantity | Revenue |

Fact tables contain:

Measurable data

🔹 Dimension Table

Contains:

Descriptive information

Example:

Product Table:

| ProductID | ProductName | Category |

Dimension tables describe facts.

Easy Memory Trick:

Fact = Numbers
Dimension = Description

3️⃣ Star Schema (Best Practice)

In professional BI modeling, we use:

Star Schema

Structure:

Fact table in center
Dimension tables around it

Advantages:

Faster performance
Cleaner relationships
Easier DAX

4️⃣ Snowflake Schema

Similar to star schema

But dimension tables are normalized.

More complex.

Used rarely in Power BI unless required.

5️⃣ Creating Relationships (Step-by-Step)

Now practical.

🔹 Step 1:

Go to Model View

🔹 Step 2:

Drag common column

From one table

To another table

Example:

ProductID (Sales table) → ProductID (Product table)

🔹 Step 3:

Check relationship settings:

Cardinality
Cross filter direction

6️⃣ Cardinality (VERY IMPORTANT)

There are 3 types:

🔹 One-to-Many (1:*)

Most common.

Example:

One Product
Many Sales

Correct modeling in most cases.

🔹 Many-to-One (*:1)

Reverse direction.

🔹 Many-to-Many (*:*)

Danger zone

Use carefully.

Can cause incorrect totals.

7️⃣ Cross Filter Direction

Two options:

Single
Both

Best practice:

Use Single unless necessary.

Both can create ambiguity.

8️⃣ Creating Hierarchies (Step-by-Step)

Used for drill-down.

Example:

Date hierarchy:

Year → Quarter → Month → Day

How to Create:

Step 1: Right-click column (e.g., Year)
Step 2: Select "Create Hierarchy"
Step 3: Drag Month & Day into hierarchy

Now visual supports drill-down.

9️⃣ Managing Date Tables (VERY IMPORTANT)

Always create proper date table.

Why?

Time intelligence functions require:

Continuous date table
Marked as Date Table

Step-by-Step:

1. Create Date table (DAX or Power Query)
2. Go to Table Tools
3. Click "Mark as Date Table"
4. Select Date column

Now time-based calculations work properly.

🔟 Filter Flow (Common Confusion)

Filters flow from:

Dimension → Fact

Example:

If you filter Product Category

Sales values update.

But filtering fact table

Does NOT filter dimension automatically.

Understand this carefully.

🔵 PHASE 4: DAX (DATA ANALYSIS EXPRESSIONS)

1️⃣ What is DAX?

DAX = Data Analysis Expressions

It is a formula language used to:

Create calculated columns
Create measures
Perform aggregations
Build KPIs
Apply filters dynamically

Think of it as:

Excel formulas + SQL logic + filter context intelligence.

2️⃣ Calculated Column vs Measure (VERY IMPORTANT)

This is the most common confusion.

🔹 Calculated Column

Created row by row
Stored in memory
Static calculation

Example:

If Sales table has Quantity and Price:

You create:

Revenue = Quantity * Price

This is calculated for every row.

Step-by-Step: Create Calculated Column

1. Go to Data View
2. Select table
3. Click "New Column"
4. Write formula:

Revenue = Sales[Quantity] * Sales[Price]

Press Enter.

Column created.

🔹 Measure

Dynamic calculation
Not stored row-wise
Calculated at report level
Depends on filters

Example:

Total Revenue:

Total Revenue = SUM(Sales[Revenue])

Measure recalculates based on:

Slicers
Filters
Visual context

Step-by-Step: Create Measure

1. Go to Report View
2. Select table
3. Click "New Measure"
4. Write formula

Press Enter.

Memory Trick:

Calculated Column → Row logic
Measure → Aggregation logic

3️⃣ Basic DAX Functions

🔹 SUM

Total Sales = SUM(Sales[Revenue])

🔹 AVERAGE

Avg Sales = AVERAGE(Sales[Revenue])

🔹 COUNT

Total Orders = COUNT(Sales[OrderID])

🔹 DISTINCTCOUNT

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

4️⃣ CALCULATE (MOST IMPORTANT FUNCTION)

This is the heart of DAX.

CALCULATE modifies filter context.

Example:

Total Sales = SUM(Sales[Revenue])

Sales 2023 = CALCULATE(
    [Total Sales],
    Sales[Year] = 2023
)

This changes filter to Year 2023.

5️⃣ FILTER Function

Used inside CALCULATE for advanced filtering.

Example:

High Sales = CALCULATE(
    [Total Sales],
    FILTER(Sales, Sales[Revenue] > 1000)
)

6️⃣ Row Context vs Filter Context (CRITICAL)

🔹 Row Context

Exists in:

Calculated columns
Iterators

It evaluates row by row.

🔹 Filter Context

Exists in:

Measures
Visual filters
Slicers

Most DAX confusion happens here.

7️⃣ Iterator Functions (Advanced but Important)

These functions iterate row by row.

Example:

🔹 SUMX

Total Revenue = SUMX(
    Sales,
    Sales[Quantity] * Sales[Price]
)

Difference:

SUM → sums column
SUMX → evaluates expression per row

8️⃣ Time Intelligence (Very Important)

Requires proper date table.

🔹 TOTALYTD

YTD Sales = TOTALYTD(
    [Total Sales],
    'Date'[Date]
)

🔹 SAMEPERIODLASTYEAR

Last Year Sales = CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

Used for:

Growth comparison
YoY analysis

9️⃣ IF & Logical Functions

Example:

Profit Status =
IF(
    [Total Sales] > 10000,
    "Good",
    "Needs Improvement"
)

🔟 SWITCH Function

Cleaner than multiple IFs.

Category Label =
SWITCH(
    TRUE(),
    [Total Sales] > 20000, "Excellent",
    [Total Sales] > 10000, "Good",
    "Low"
)

🔵 PHASE 5: DESIGNING REPORTS & DASHBOARDS

This phase is about:

Storytelling with data
Visual clarity
Business decision support

Power BI is not about showing data.

It is about guiding decisions.

1️⃣ Understanding Report Canvas

When you open Report View:

You see:

Canvas (main area)
Visualizations pane
Fields pane
Filters pane

2️⃣ Adding Visuals (Step-by-Step)

Let's create a simple chart.

🔹 Create a Bar Chart

Step 1: Click Bar Chart icon in Visualizations pane
Step 2: Drag Category field to Axis
Step 3: Drag Sales field to Values

Chart created

🔹 Create a Line Chart

Step 1: Click Line Chart
Step 2: Drag Date to Axis
Step 3: Drag Sales to Values

Used for time trend analysis.

🔹 Create a Pie Chart

Step 1: Click Pie Chart
Step 2: Drag Category to Legend
Step 3: Drag Sales to Values

Use carefully. Too many slices = bad design.

3️⃣ Cards & KPIs (Very Important)

Card visual shows:

Single important metric.

Example:

Total Revenue
Total Customers
Profit

Step-by-Step:

1. Click Card visual
2. Drag measure into Values

Simple and powerful.

4️⃣ Slicers (Interactive Filtering)

Slicers allow user to filter dashboard.

🔹 Create a Slicer

Step 1: Click Slicer icon
Step 2: Drag field (e.g., Year)

Now user can select year dynamically.

🔹 Types of Slicers

Dropdown
List
Date range
Hierarchy slicer

5️⃣ Drill Down Feature

Used for hierarchy navigation.

Example:

Year → Quarter → Month

Step-by-Step:

1. Create hierarchy
2. Use it in chart axis
3. Enable drill mode (top right of chart)

User can click to drill into data.

6️⃣ Conditional Formatting

Used to highlight important values.

🔹 Apply Conditional Formatting

Step 1: Select visual
Step 2: Click dropdown on Values
Step 3: Choose Conditional Formatting
Step 4: Choose rules or color scale

Example:

If profit < 0 → Red
If profit > 0 → Green

7️⃣ Using Maps

Power BI supports:

Map
Filled Map

Step-by-Step:

1. Click Map visual
2. Drag Location field
3. Drag Sales into Size

Used for geographical analysis.

8️⃣ Designing Professional Layout

Now design principles (very important):

🔹 Keep It Clean

Don't overcrowd
Leave white space
Avoid too many colors

🔹 Use Consistent Color Theme

Go to:

View → Themes

Choose professional theme.

🔹 Align Properly

Use grid alignment tools.

🔹 Use Titles

Each visual should answer a question.

Example:

"Sales by Region – 2023"

Not:

"Chart 1"

9️⃣ Dashboard vs Report

Report:

Multiple pages
Interactive

Dashboard:

Single summary page
Executive level

🔟 Publishing Report

After completing report:

Step 1: Click Publish
Step 2: Login to Power BI account
Step 3: Choose workspace

Report uploaded to cloud.

🔵 PHASE 6: PERFORMANCE OPTIMIZATION

1️⃣ Why Optimization Matters

Imagine:

5 million rows
10 visuals
Complex DAX
Multiple slicers

If not optimized:

Report loads slowly
Visuals lag
Business loses trust

Optimization = speed + efficiency.

2️⃣ Performance Analyzer (Step-by-Step)

Power BI provides built-in performance tool.

🔹 How to Use Performance Analyzer

Step 1: Go to View tab
Step 2: Click Performance Analyzer
Step 3: Click Start Recording
Step 4: Refresh visuals

It shows:

DAX query time
Visual display time
Other processing time

Now you know what is slow.

3️⃣ Optimize Data Model (VERY IMPORTANT)

Model design affects speed more than visuals.

🔹 Use Star Schema

Avoid:

Snowflake complexity
Too many relationships

Star schema = faster.

🔹 Remove Unnecessary Columns

Every column consumes memory.

In Power Query:

Remove unused columns before loading.

🔹 Avoid Many-to-Many Relationships

These slow down filter propagation.

Prefer: 1-to-many relationships.

4️⃣ Optimize DAX

Bad DAX = slow report.

🔹 Avoid Complex Nested CALCULATE

Keep measures simple and reusable.

Create base measures:

Total Sales = SUM(Sales[Revenue])

Then build advanced measures on top of it.

🔹 Use Variables (VAR)

Improves readability and performance.

Example:

Profit Margin =
VAR Revenue = [Total Sales]
VAR Cost = SUM(Sales[Cost])
RETURN
Revenue - Cost

Cleaner and faster.

🔹 Avoid Using Calculated Columns When Not Needed

Measures are better than calculated columns in most cases.

Calculated columns increase model size.

5️⃣ Reduce Visual Load

Too many visuals on one page = slow.

Best practice:

6–8 visuals per page
Use drill-through pages for detail

6️⃣ Use Aggregations

For very large datasets:

Use summarized tables instead of raw data.

Example:

Aggregate daily data instead of minute-level.

7️⃣ Disable Auto Date/Time (Advanced Tip)

Power BI auto-creates hidden date tables.

Turn off for better control:

File → Options → Data Load → Disable Auto Date/Time.

8️⃣ Limit Slicers

Too many slicers slow down report.

Use:

Hierarchical slicers
Smart filtering

9️⃣ Import Mode vs DirectQuery Performance

Import Mode:

Faster
Recommended when possible

DirectQuery:

Slower
Used for very large live datasets

🔟 Best Practices Summary

✔ Clean data before modeling
✔ Use star schema
✔ Minimize columns
✔ Prefer measures over calculated columns
✔ Use Performance Analyzer
✔ Avoid overloading page
✔ Optimize DAX logic

Mini Project of using Next.js and Tailwind CSS