Three Core Components

• Power BI Desktop — Free Windows application where you build reports. Connect data, transform it in Power Query, write DAX measures, and create visuals.
• Power BI Service (app.powerbi.com) — Cloud-based portal to publish, share, and collaborate on reports. Supports dashboards, workspaces, and scheduled refresh.
• Power BI Mobile — Android/iOS app to view and annotate reports on the go. Supports push notifications for data alerts.

Supporting Tools in the Ecosystem

• Power Query — ETL (Extract, Transform, Load) engine built into Desktop and Excel. Uses M language.
• DAX (Data Analysis Expressions) — Formula language for creating measures and calculated columns.
• Power BI Gateway — Software installed on-premises to bridge your local SQL Server or Excel files with the Power BI cloud service for scheduled refresh.
• Dataflows — Reusable Power Query transformations stored in the cloud, shared across multiple reports.
• Datasets / Semantic Models — The data model (tables + relationships + measures) published to the Service, reusable by multiple reports.
• Power BI Embedded — Embed Power BI reports into your own website or application using APIs.

Typical Workflow

System Requirements

• Operating System: Windows 8.1 / Windows 10 / Windows 11 (64-bit recommended)
• RAM: Minimum 2 GB; Recommended 4 GB or more for large datasets
• Disk Space: 2 GB free for installation
• Display: 1440 x 900 resolution or higher recommended
• .NET Framework: 4.6.2 or higher (auto-installed)

Installation Steps

• Option 1 (Recommended): Install from Microsoft Store — search "Power BI Desktop" and click Install. Auto-updates monthly.
• Option 2: Download .exe from powerbi.microsoft.com/downloads. Run installer, accept license, choose install path.
• Sign in: Launch Power BI Desktop. Sign in with your Microsoft/Office 365 account (free to create at microsoft.com).
• Updates: Power BI Desktop updates every month (e.g., September 2026 release). Check Help > About for your current version.

First Launch Checklist

• Home ribbon visible with Get Data, Transform Data, and Visualizations pane on right
• Three view icons on the left: Report (bar chart icon), Data (table icon), Model (diagram icon)
• Sign-in account displayed top-right corner
• Sample report option in splash screen — click to open a pre-built sales demo

Report View Layout

• Ribbon (Top): Home, Insert, Modeling, View, Optimize, Help tabs — tools for inserting visuals, formatting, and publishing.
• Canvas (Center): The blank report page where you drag visuals. Each page is a tab at the bottom.
• Visualizations Pane (Right): Pick a chart type. Below it: Fields, Format, Analytics sub-panes.
• Fields Pane (Far Right): Lists all tables and columns from your data model. Drag fields onto the canvas or into visual buckets.
• Filters Pane: Apply page-level or visual-level filters without adding a slicer visual.

🎯 Power BI Desktop — Ribbon Tabs Reference

The ribbon at the top of Power BI Desktop is your command center. Below is a complete breakdown of every tab and what each option does — with actual screenshots from the software.

📌 HOME Tab

📷 Upload ribbon_home.png to your images/ folder on Hostinger

Menu / Button	What It Does
Get Data	Connect to 100+ data sources — Excel, SQL, web, SharePoint, cloud services and more. The starting point for every report.
Excel Workbook	Quick shortcut to import data directly from a local Excel (.xlsx / .xls) file.
OneLake Catalog	Browse and connect to datasets stored in Microsoft Fabric's OneLake data lake platform.
SQL Server	Direct connector to Microsoft SQL Server databases — enter server name and credentials to pull tables or write custom SQL.
Enter Data	Manually type a small table of data inside Power BI — useful for lookup tables, parameters, or sample data without a file.
Dataverse	Connect to Microsoft Dataverse (used by Power Apps) to pull business data stored in the Power Platform.
Recent Sources	Quickly reconnect to data sources you've used before — saves time re-entering connection details.
Transform Data	Opens Power Query Editor — the ETL (Extract, Transform, Load) workspace where you clean, shape, and reshape your data before loading.
Refresh	Re-runs all data queries and refreshes every table in your report with the latest data from the source.
New Visual	Adds a blank visual placeholder on the canvas — then select a chart type from the Visualizations pane.
Text Box	Insert a free-form text box on the canvas — for titles, annotations, descriptions, or instructions.
More Visuals	Browse and import custom visuals from the AppSource marketplace — charts not included by default (e.g. Gantt, Sankey).
New Visual Calculation	Write a DAX expression that calculates a value directly at the visual level — no new measure or column needed in the model.
New Measure	Create a DAX measure — a calculated formula (SUM, AVERAGE, IF, etc.) stored in the model and usable across any visual.
Quick Measure	Generate common DAX patterns (year-over-year change, running total, average per category) using a guided wizard — no DAX writing needed.
Sensitivity	Apply Microsoft Information Protection (MIP) sensitivity labels (Confidential, Public, Internal) to classify and protect your report.
Publish	Upload your .pbix file to Power BI Service (web) — makes the report accessible online and enables sharing, scheduling, and dashboards.
Prep Data for AI	Tag your semantic model so Copilot and Q&A features understand your data better — set synonyms, descriptions, and relationships.
Copilot	AI assistant inside Power BI — asks questions in plain English, generates visuals, writes DAX measures, and summarizes report pages.

📌 INSERT Tab

📷 Upload ribbon_insert.png to your images/ folder on Hostinger

Menu / Button	What It Does
New Page	Adds a blank report page (tab) to your report — useful for separating Executive Summary, Detail Views, and KPI pages.
New Visual	Same as in Home — inserts a visual placeholder and opens the Visualizations pane to pick a chart type.
More Visuals	Access AppSource marketplace for custom chart types — waterfall, bullet charts, word clouds, maps and hundreds more.
Key Influencers	AI visual — automatically analyzes your data to show which factors most influence a metric (e.g., what drives high sales).
Decomposition Tree	AI visual — interactive drill-down that breaks a metric into its components, letting users explore data paths manually or via AI splits.
Narrative / Smart Narrative	AI visual — auto-generates a written text summary of your data in plain language, updating dynamically when filters change.
Paginated Report	Insert a paginated report visual — ideal for pixel-perfect, print-ready reports (invoices, statements) rendered page by page.
Power Apps	Embed a Power Apps app inside your Power BI report — allows users to write back to data sources without leaving the dashboard.
Power Automate	Embed a Power Automate flow trigger button — run workflows (send emails, create records) directly from a report with one click.
Text Box	Add a free-form text annotation or label anywhere on the canvas — for context, headings, or explanatory notes.
Buttons	Add interactive buttons (Back, Blank, Bookmark, Drill-through, etc.) for report navigation and user actions.
Shapes	Insert geometric shapes (rectangles, lines, arrows, circles) — used for visual grouping, dividers, and layout design.
Image	Embed a static image file (logo, icon, background) from your computer onto the report canvas.
Add a Sparkline	Add a tiny inline trend line inside a table or matrix cell — shows movement over time without a full separate chart.

📌 MODELING Tab

📷 Upload ribbon_modeling.png to your images/ folder on Hostinger

Menu / Button	What It Does
Manage Relationships	Open the relationship manager — view, create, edit, or delete links between tables. The most critical step in building your data model.
New Visual Calculation	Create a calculation scoped to a specific visual — doesn't affect the data model, just that one chart or table.
New Measure	Write a DAX formula (e.g., Total Sales = SUM(Sales[Amount])) stored in the model — reusable across all visuals and pages.
Quick Measure	Guided wizard to create common DAX patterns without writing code — running totals, percentages of total, time intelligence, etc.
New Column	Add a calculated column to a table using DAX — computed row by row, stored in the model (increases file size).
New Table	Create a calculated table using DAX — useful for calendar tables, parameter tables, or bridging tables.
Mark as Date Table	Tell Power BI which table is your date/calendar table — enables automatic time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, etc.).
Change Detection	Set up automatic page refresh for real-time reports — Power BI checks the data source at intervals and updates only when data has changed.
New Parameter	Create a What-If parameter — a numeric slicer that dynamically changes a value in your DAX measures (e.g., discount %, growth rate).
Manage Roles	Define Row-Level Security (RLS) roles using DAX filter rules — restricts which rows of data each user or group can see.
View As	Test your RLS roles — preview the report as if you were a specific role to verify that data filtering works correctly before publishing.
Q&A Setup	Configure the natural language Q&A feature — add synonyms, mark fields as featured, and set how Power BI interprets your questions.
Language / Linguistic Schema	Download and edit the YAML linguistic schema file — add synonyms, phrasings, and domain-specific terms to improve Q&A accuracy.

📌 VIEW Tab

📷 Upload ribbon_view.png to your images/ folder on Hostinger

Menu / Button	What It Does
Themes	Apply a pre-built colour and font theme to your entire report in one click — or import a custom JSON theme file for your brand colours.
Page View	Switch how the canvas fits the screen: Fit to Page, Fit to Width, or Actual Size — affects only your preview, not the published report.
Mobile Layout	Toggle to a phone-screen canvas and rearrange visuals specifically for mobile viewers — Power BI Service shows this layout on phones.
Scale to Fit	Automatically scale the report canvas to fill your available screen area — helpful on smaller monitors.
Gridlines	Show or hide alignment grid lines on the canvas — helps position and align visuals precisely.
Snap to Grid	When enabled, visuals automatically snap to the nearest grid point when you move or resize them — ensures clean alignment.
Lock Objects	Lock selected visuals in place so they can't be accidentally moved or resized — useful for background shapes and headers.
Filters	Show or hide the Filters pane on the right side of the canvas.
Bookmarks	Open the Bookmarks pane — capture the current state of the report (filters, visuals, page) as a named bookmark for navigation or storytelling.
Selection	Open the Selection pane — see all objects on the current page, change their visibility (eye icon), and manage z-order (layering).
Performance Analyzer	Record and measure how long each visual takes to load — identifies slow DAX queries or render bottlenecks in your report.
Sync Slicers	Open the Sync Slicers pane — make a slicer on one page control filters on other pages too, keeping context consistent across the report.

📌 OPTIMIZE Tab

📷 Upload ribbon_optimize.png to your images/ folder on Hostinger

Menu / Button	What It Does
Pause Visuals	Temporarily stops all visuals from refreshing when you make changes — speeds up editing by preventing constant re-rendering while you work.
Refresh Visuals	Manually trigger a refresh of all paused visuals — use after Pause Visuals to apply your changes in one go.
Optimization Presets	Apply preset optimization profiles (e.g., "Balanced", "Performance") that adjust report settings like query reduction and render behavior.
Performance Analyzer	Same as View tab — record visual load times and DAX query durations to find and fix slow parts of your report.
Apply All Slicers	When using query reduction mode, slicers don't filter until you click "Apply" — this button applies all pending slicer selections at once.

📌 HELP Tab

📷 Upload ribbon_help.png to your images/ folder on Hostinger

Menu / Button	What It Does
About	Shows the Power BI Desktop version number and build — useful when reporting bugs or checking if you need an update.
Guided Learning	Opens Microsoft's guided learning paths for Power BI — structured courses for beginners to advanced users.
Training Videos	Links to official Microsoft video tutorials — short, task-based videos on specific Power BI features.
Documentation	Opens the official Power BI documentation (docs.microsoft.com) — full reference for every feature, DAX function, and connector.
Support Blog	Microsoft's Power BI support blog — troubleshooting guides, known issues, and workarounds published by the product team.
Power BI Blog	The official Power BI team blog — monthly updates, new feature announcements, and deep-dive articles on capabilities.
Community	Opens the Power BI Community forum — ask questions, find answers, and browse thousands of discussions from users worldwide.
Power BI for Developers	Resources for embedding Power BI into apps — REST API documentation, JavaScript SDK, and embedding tutorials.
Submit an Idea	Opens the Power BI Ideas portal — upvote existing feature requests or submit new ideas directly to the Microsoft product team.
External Tools	Launch third-party tools connected to Power BI Desktop (e.g., DAX Studio, Tabular Editor) — for advanced model editing and DAX debugging.
Examples	Download sample .pbix reports from Microsoft — pre-built dashboards like Retail Analysis, IT Spend, and Human Resources to study.
Consulting Services	Find certified Microsoft partners and consulting firms who specialize in Power BI implementations and training.

Three Main Views

• Report View: Build and format your visuals. The final dashboard the user sees.
• Data View (Table View): Browse your loaded tables row by row. Spot data quality issues. Write calculated columns here.
• Model View: See all tables as boxes with lines showing relationships between them. Essential for star schema design.

Key Shortcuts to Know

Categories of Data Sources

• File-based: Excel (.xlsx), CSV, JSON, XML, PDF tables, Power BI template (.pbit)
• Databases: SQL Server, MySQL, PostgreSQL, Oracle, SAP HANA, Snowflake, Azure SQL
• Cloud Services: SharePoint Online, OneDrive, Azure Blob Storage, Dynamics 365, Salesforce
• Online Services: Google Analytics, Facebook, Twilio, GitHub, MailChimp
• Other: Web (scrape HTML tables), OData feed, ODBC, R script, Python script

How to Access Get Data

• Click Home > Get Data to open the full connector gallery with 100+ options.
• Common sources appear as quick buttons: Excel, Text/CSV, SQL Server, Web.
• Search by typing in the search box at the top of the Get Data dialog (e.g., type "postgres" to find PostgreSQL connector).
• Each connector may require a driver or credentials — Power BI prompts you automatically.

Connectivity Modes

Connecting to Excel

• Go to Home > Get Data > Excel Workbook. Browse to your .xlsx file.
• The Navigator dialog shows all sheets and named Tables inside the file. Select one or multiple.
• Best Practice: Format your Excel data as a Table (Ctrl+T) before connecting — Power BI recognizes Table names and handles new rows automatically on refresh.
• Avoid connecting to raw sheet ranges — if someone adds a column in Excel, it may break your query.

Connecting to CSV

• Go to Home > Get Data > Text/CSV. Power BI auto-detects delimiter (comma, semicolon, tab).
• Check the preview — if the first row is not a header, uncheck "Use first row as headers" in Power Query.
• CSV files do not have data types — Power BI guesses. Always verify column types in Power Query (numbers vs text).

Example: Sales Data Excel File

After connecting, Power BI imports this as a table. You can then create a measure: Total Sales = SUM(Sales[Total])

Steps to Connect

• Go to Home > Get Data > SQL Server.
• Enter Server name (e.g., DESKTOP-ABC\SQLEXPRESS or 192.168.1.10) and optional Database name.
• Choose Import or DirectQuery mode.
• Authentication: Windows Authentication (domain users) or SQL Server Authentication (username + password).
• The Navigator lists all databases, tables, and views. Select the tables you need.

Using a Native SQL Query

Instead of loading an entire table, you can write a SQL query to pull only the data you need:

SELECT
    OrderID,
    CustomerName,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
  AND TotalAmount > 10000

This reduces the data loaded into Power BI, improving performance — especially important for tables with millions of rows.

Common Issues & Fixes

• Cannot connect: Check that SQL Server Browser service is running and port 1433 is open in firewall.
• Login failed: Use SQL Auth with the correct username/password. Windows Auth needs the machine to be on the same domain.
• Timeout: Add CommandTimeout option in Advanced settings when connecting to slow servers.

Web Connector (HTML Table Scraping)

• Go to Get Data > Web. Enter any public URL that contains HTML tables.
• Power BI scrapes all tables found on the page. Example: Wikipedia's list of Indian states by GDP.
• Power Query then lets you clean the scraped data (remove header rows, fix types).
• Web data refreshes on schedule — Power BI re-scrapes the URL at each refresh.

SharePoint Online Lists

• Go to Get Data > SharePoint Online List. Enter your SharePoint site URL (e.g., https://yourcompany.sharepoint.com/sites/Sales).
• Sign in with your Microsoft 365 account. All lists from that site appear in the Navigator.
• SharePoint lists work great for team-maintained lookup tables (product catalog, employee list, project status).
• Tip: Avoid connecting to SharePoint Excel files via the File connector — use SharePoint Folder for proper refresh.

OneDrive for Business

• Connect to files stored in OneDrive using Get Data > Web with the file's shareable link (change ?e=xxx to ?format=csv for CSV).
• Better approach: Use Get Data > SharePoint Folder and navigate to the OneDrive path.

Import Mode

• Data is copied from the source into the Power BI file (.pbix) in memory (VertiPaq engine).
• All DAX calculations run on the in-memory copy — extremely fast.
• Data is as fresh as your last refresh (scheduled or manual). Not real-time.
• File size limit: 1 GB for Pro, 100 GB for Premium per dataset.

DirectQuery Mode

• Data stays in the source database. Power BI sends SQL queries to the DB each time a visual is clicked.
• Always shows the latest data — truly real-time for live dashboards.
• Performance depends on your database server speed. Can be slow for complex DAX.
• Some DAX functions are not supported in DirectQuery (e.g., certain time intelligence functions).

When to Use Which

What is Power Query?

Power Query is the ETL (Extract, Transform, Load) engine inside Power BI. Every transformation you apply is recorded as a step using a language called M. You can see and edit these steps in the Applied Steps panel on the right side of the Query Editor.

Opening Power Query Editor

• Click Home > Transform Data to open the Power Query Editor window.
• Left panel: list of all queries (tables). Center: data preview. Right: Applied Steps.
• Each step is an M expression — click the fx bar to see or edit the formula.
• The Query Editor is non-destructive — changes apply only when you click Close & Apply.

Common First Steps on Any Dataset

• Promote Headers: Use first row as column names (Home > Use First Row as Headers).
• Change Type: Set correct data types for each column (Text, Number, Date, etc.).
• Remove Top Rows: Skip metadata rows that appear before the actual data.
• Filter Rows: Remove test data, blanks, or out-of-scope records.
• Rename Columns: Replace "Column1" with meaningful names like "Order_ID".

Common Column Transformations

• Split Column: Split "Chennai - Tamil Nadu" by delimiter " - " into two columns: City and State.
• Trim / Clean: Remove leading/trailing spaces (Trim) and non-printable characters (Clean).
• Replace Values: Replace "N/A" or "--" with null or a default value.
• Extract: Pull text before/after a delimiter, or extract by position (e.g., first 4 characters of an order code).
• Uppercase / Lowercase: Standardize text case for consistent joins.
• Date Transformations: Extract Year, Month, Quarter, Day of Week from a date column.

Example: Clean a Product Name Column

Number Transformations

• Round: Round to 2 decimal places (useful for currency amounts in Rs.).
• Absolute Value: Convert negative returns to positive for analysis.
• Percentage: Divide a column by another and set type to Percentage.
• Standard (Statistics): Create min, max, sum columns in one click via the Statistics option.

Merge Queries (JOIN)

Merge combines two tables horizontally based on a matching key — like a VLOOKUP or SQL JOIN.

• Left Outer: All rows from the left table + matching rows from right. (Most common)
• Inner: Only rows that match in both tables.
• Full Outer: All rows from both tables.
• Anti Join: Rows in left that have NO match in right — useful to find unmatched records.

Orders table: [OrderID, CustomerID, Amount]
Customers table: [CustomerID, CustomerName, City]

Merge on CustomerID (Left Outer)
Result: [OrderID, CustomerID, Amount, CustomerName, City]

Append Queries (UNION)

Append stacks tables vertically — like SQL UNION ALL. Use when you have the same structure across multiple files.

• Example: Monthly sales files Jan.csv, Feb.csv, Mar.csv — append all three into one Sales table.
• Append New: Creates a new query from two tables (non-destructive).
• Append to Existing: Adds rows to the first query.
• Column names must match (case-sensitive). Mismatched columns appear as null in the combined table.

Combining Folder of Files

• Get Data > Folder. Select folder containing 12 monthly CSV files.
• Power Query shows all files. Click "Combine & Transform" — auto-appends all files into one table.
• New monthly files dropped into the same folder are auto-picked up on next refresh.

Custom Column (M Formula)

When built-in transformations aren't enough, write your own formula using M Language in Add Column > Custom Column.

// Calculate GST (18%) on Amount
= [Amount] * 0.18

// Combine First and Last Name
= [First_Name] & " " & [Last_Name]

// Classify sales amount
= if [Amount] > 100000 then "High"
  else if [Amount] > 50000 then "Medium"
  else "Low"

// Extract Year from Date
= Date.Year([Order_Date])

// Convert text "1,23,456" to number
= Number.From(Text.Remove([Amount_Text], {",", " "}))

Conditional Column (No-Code)

• Add Column > Conditional Column — build if/else logic with a form (no M typing).
• Great for classifying: Region = "South" if City in {Chennai, Bangalore, Hyderabad}.

Key M Language Concepts

• Case-sensitive: Text.Upper works; text.upper fails.
• Null handling: Use if [Col] = null then "Unknown" else [Col] to replace nulls.
• List functions: List.Sum({1,2,3}) returns 6. Use inside custom columns.
• Date functions: Date.AddDays([Date], 30) adds 30 days to a date column.

Data Profiling Tools in Power Query

• Column Quality: Shows % of Valid, Error, and Empty values per column as a colored bar.
• Column Distribution: Shows the distribution of values — how many unique values, which value appears most often.
• Column Profile: Detailed stats at the bottom — Min, Max, Average, Count, Null count, Error count.
• Enable these via View tab > Column Quality / Column Distribution / Column Profile.

Handling Data Quality Issues

• Nulls: Right-click column > Replace Values > replace null with 0 (numbers) or "Unknown" (text).
• Errors: Right-click column > Remove Errors (removes the row) or Replace Errors (fills with a value).
• Duplicates: Home > Remove Duplicates (removes entire duplicate rows). Or keep only unique by a key column.
• Outliers: Filter column to exclude values above/below a reasonable threshold.

Example: Profiling a Sales Amount Column

What is a Star Schema?

A star schema organizes data into one central Fact table surrounded by multiple Dimension tables. The fact table holds measurable events (sales, orders, transactions); dimensions describe who, what, where, and when.

Fact Table vs Dimension Table

Why Star Schema in Power BI?

• Power BI's VertiPaq engine is optimized for star schemas — better compression and faster queries.
• DAX time intelligence functions require a separate, contiguous Date dimension table.
• Slicers and filters work more predictably — filter once on a dimension and it flows to all related facts.
• Avoids many-to-many ambiguity issues that slow down reports and cause incorrect totals.

Creating Relationships

• Go to Model View. Power BI auto-detects relationships based on column names. Verify them manually.
• To create: drag a column from one table and drop onto the matching column in another table.
• To edit: double-click the relationship line to set cardinality and cross-filter direction.

Cardinality Types

• One-to-Many (1:*): Most common. One row in Dimension matches many rows in Fact. (e.g., one Product appears in many Orders)
• One-to-One (1:1): Rare. Both tables have unique values in the join column.
• Many-to-Many (*:*): Both tables have repeating values. Requires bridge table or special handling.

Filter Direction

• Single (Default): Filter flows from dimension to fact only. (Recommended for star schemas)
• Both (Bidirectional): Filter flows both ways. Can cause circular dependency issues — use carefully.

Active vs Inactive Relationships

You can have only one active relationship between two tables. If you need to analyze a fact table by two date fields (e.g., Order Date and Ship Date), create two relationships to the Date table. Only one can be active; use USERELATIONSHIP in DAX to activate the inactive one.

Ship Date Sales =
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], Date[Date])
)

Why You Need a Date Table

Time intelligence DAX functions (SAMEPERIODLASTYEAR, DATESYTD, PREVIOUSMONTH) require a dedicated, contiguous Date table marked as the official Date Table in your model. Without it, these functions either fail or give wrong results.

Creating a Date Table in DAX

Date =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",         YEAR([Date]),
    "Month No",     MONTH([Date]),
    "Month Name",   FORMAT([Date], "MMMM"),
    "Quarter",      "Q" & QUARTER([Date]),
    "Weekday",      FORMAT([Date], "dddd"),
    "Week No",      WEEKNUM([Date], 21),
    "FY Quarter",   "FY Q" & INT(MOD(MONTH([Date]) - 4, 12) / 3 + 1),
    "Is Weekend",   IF(WEEKDAY([Date],2) >= 6, TRUE(), FALSE())
)

CALENDARAUTO() automatically detects the min and max dates across all date columns in your model.

Mark as Date Table

• After creating, right-click the Date table in Fields pane > Mark as Date Table > select the Date column.
• Link the Date table to every fact table via the date key column.
• This enables all time intelligence DAX functions to work correctly.

Calculated Columns

• Computed row by row, stored in the table (takes memory). Available in the Fields pane like any regular column.
• Can be used in slicers, rows of a matrix, or as an axis in a chart.
• Example: GST Amount = Sales[Amount] * 0.18 — added to every row in Sales table.
• Evaluated during data refresh, not during visual interaction.

Measures

• Computed dynamically at query time based on the current filter context. NOT stored row by row.
• Must be used as a Value in a visual (not as axis or slicer).
• Example: Total Sales = SUM(Sales[Amount]) — changes dynamically when you filter by Region or Month.
• Always prefer measures over calculated columns for aggregations.

Comparison

When Does Many-to-Many Occur?

Many-to-many happens when the join column has duplicate values in BOTH tables. Example: a salesperson covers multiple regions, and a region has multiple salespersons. Neither table has a unique key for the join.

Solution 1: Bridge Table

Create a bridge (junction) table that resolves the many-to-many into two one-to-many relationships.

Solution 2: Power BI Native M:M

• Power BI supports native many-to-many relationships since 2018. Set cardinality to Many to Many (*:*).
• Works well for budget vs actuals scenarios where both tables share a common dimension key.
• Limitation: bidirectional filtering is enabled by default — can cause performance issues in large models.

Reduce File Size

• Remove unused columns: Every column in the model takes memory. Delete anything not used in DAX or visuals.
• Use integer keys: Relationships on integers (1, 2, 3) are faster than on long text strings ("Chennai-Region-South-Zone").
• Avoid high cardinality text columns: A URL or long description column with millions of unique values bloats the model.
• Summarize imported data: Import pre-aggregated data (monthly totals) instead of millions of daily transaction rows when detail isn't needed.

DAX Performance Tips

• Use DIVIDE(numerator, denominator, 0) instead of numerator / denominator — handles division by zero gracefully.
• Use VAR in complex measures to calculate intermediate results once instead of repeatedly.
• Avoid nested CALCULATE statements more than 2 levels deep.
• Use Performance Analyzer (View tab) to measure how long each visual takes to render — identifies slow DAX measures.

Model View Best Practices

• Organize tables: Fact tables at center, Dimensions around them (like a star).
• Hide foreign key columns from the report view (right-click > Hide in Report View) — users should use dimension tables for filtering, not raw IDs.
• Create a dedicated Measures table (empty query + calculated measures only) to keep measures organized.

What is DAX?

DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot, and Analysis Services. It lets you create custom calculations — measures and calculated columns — that respond dynamically to filters and slicers in your reports.

DAX vs Excel Formulas

DAX Syntax Basics

Measure Name = FUNCTION(Table[Column], [Optional Args])

// Example measures
Total Sales = SUM(Sales[Amount])
Order Count = COUNTROWS(Sales)
Avg Order   = AVERAGE(Sales[Amount])
Max Sale    = MAX(Sales[Amount])

• Table[Column] — always reference columns with table name prefix.
• Measures are created in the Fields pane > right-click table > New Measure.
• DAX is not case-sensitive: SUM = sum.
• Use // comments to document complex measures.

Aggregation Functions

// Total Revenue (sum of all amounts)
Total Revenue = SUM(Sales[Amount])

// Number of orders
Order Count = COUNTROWS(Sales)

// Count non-blank values in a column
Product Count = COUNT(Products[ProductID])

// Count distinct customers
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

// Average order value
Avg Order Value = AVERAGE(Sales[Amount])

// Minimum and Maximum
Min Sale = MIN(Sales[Amount])
Max Sale = MAX(Sales[Amount])

SUMX — Row-by-Row Calculation

When you need to multiply two columns first and then sum the result, use SUMX:

// Revenue = Qty * Unit Price, summed across all rows
Revenue = SUMX(Sales, Sales[Qty] * Sales[UnitPrice])

// Gross Profit with discount
Gross Profit = SUMX(Sales, (Sales[UnitPrice] - Sales[CostPrice]) * Sales[Qty])

Example Output

What is CALCULATE?

CALCULATE is the most powerful DAX function. It evaluates an expression (like SUM) and applies additional or modified filter context on top of whatever filters are already active from slicers.

// Sales only for South region — ignores whatever slicer is selected
South Sales =
CALCULATE(SUM(Sales[Amount]), Sales[Region] = "South")

// Sales for current year
Current Year Sales =
CALCULATE(SUM(Sales[Amount]), YEAR(Sales[OrderDate]) = YEAR(TODAY()))

// Remove all date filters (show grand total regardless of time slicer)
All Time Sales =
CALCULATE(SUM(Sales[Amount]), ALL(Date))

// Sales % of total
Sales % =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Filter Context vs Row Context

• Filter Context: The set of filters from slicers, row/column headers in a matrix, and CALCULATE modifiers. Measures live in filter context.
• Row Context: The current row being evaluated. Exists inside calculated columns and iterator functions (SUMX, FILTER, etc.).
• CALCULATE converts row context to filter context — this is the core of understanding advanced DAX.

FILTER Function

// Sales where Amount > 50,000
High Value Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 50000)
)

// Sales for specific product category
Electronics Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Products[Category] = "Electronics"
)

Prerequisites

• A Date table marked as Date Table in Model View (covered in Chapter 4.3).
• Date table connected to your fact table via the date column.

Common Time Intelligence Measures

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

// Same period last year
Sales LY =
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

// Year-over-year growth %
YoY Growth % =
VAR CurrentSales = SUM(Sales[Amount])
VAR LastYearSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))
RETURN
DIVIDE(CurrentSales - LastYearSales, LastYearSales, 0)

// Month-to-Date
Sales MTD = CALCULATE(SUM(Sales[Amount]), DATESMTD(Date[Date]))

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

Example: YoY Comparison Table

RANKX — Dynamic Ranking

// Rank products by sales (1 = highest)
Product Rank =
RANKX(
    ALL(Products[ProductName]),
    [Total Revenue],
    ,
    DESC,
    Dense
)

// Only show top 5 products
Top 5 Flag = IF([Product Rank] <= 5, "Top 5", "Others")

TOPN — Retrieve Top N Rows

// Sum of top 3 customers' sales
Top 3 Customer Sales =
CALCULATE(
    SUM(Sales[Amount]),
    TOPN(3, ALL(Customers[CustomerName]), [Total Revenue], DESC)
)

VAR / RETURN — Variables

Variables store intermediate results, making complex measures readable and faster (computed once).

Profit Margin % =
VAR TotalRevenue = SUM(Sales[Amount])
VAR TotalCost    = SUM(Sales[CostAmount])
VAR GrossProfit  = TotalRevenue - TotalCost
RETURN
DIVIDE(GrossProfit, TotalRevenue, 0)

// SWITCH(TRUE()) pattern — replaces nested IF
Sales Category =
SWITCH(
    TRUE(),
    [Total Revenue] >= 1000000, "Platinum",
    [Total Revenue] >= 500000,  "Gold",
    [Total Revenue] >= 100000,  "Silver",
    "Bronze"
)

The Visual Selection Framework

Common Mistakes to Avoid

• Pie charts with 8+ slices: Hard to read. Use bar chart instead.
• 3D charts: Distort proportions. Always use 2D.
• Dual-axis line charts with different scales: Misleading. Use separate visuals with shared filters.
• Cluttered visuals: Remove gridlines, borders, data labels on every point — show only what adds value.
• Inappropriate use of Gauge: Gauges look great but waste space. Use KPI cards instead for most scenarios.

Bar & Column Charts

• Clustered Bar: Compare multiple series side by side horizontally. Best for long category names.
• Clustered Column: Same but vertical. Best for time-series (months/quarters on X-axis).
• Stacked Column: Shows total + composition. Useful for revenue by product category over months.
• 100% Stacked: Compares proportions when absolute values differ greatly between categories.

Line Charts

• Best for continuous time-series data: daily sales, weekly production, monthly headcount.
• Add a forecast line (Analytics pane > Forecast) to project future trends.
• Use area chart when you want to emphasize the volume under the trend.

Combo Chart (Line + Column)

A combo chart overlays a line on a column chart — great for showing Revenue (columns) vs Profit Margin % (line) on the same time axis.

• Drag the first measure to Column Values; drag the second measure to Line Values.
• Format each series independently — different colors, data labels on/off.
• Add a secondary Y-axis when the two measures have very different scales (e.g., revenue in crores vs margin in %).

Table Visual

• Shows raw data rows. Add columns from Fields pane. Sort by clicking column headers.
• Add conditional formatting: background color scale on a number column highlights high/low values.
• Use Data bars (Format > Conditional Formatting > Data Bars) to add in-cell mini bar charts.
• Tables auto-paginate — users can scroll through thousands of rows.

Matrix Visual (Pivot Table)

• Rows + Columns + Values = dynamic cross-tabulation. Like a pivot table in Excel.
• Supports drill-down: if rows = Year > Quarter > Month, click the drill-down arrow to expand.
• Enable Subtotals and Grand Totals in Format pane.
• Apply conditional formatting on the Values to create a heat map matrix.

Card & Multi-Row Card

• Card: Shows a single large number with a label. Perfect for KPIs on a dashboard (Total Revenue, Headcount, OTD %).
• Multi-Row Card: Shows multiple key metrics in a stacked list — alternative to a table for summary stats.
• Format card: turn off category label, change font size to 36-48pt for impact, add border color matching your theme.

Map Visual (Bubble Map)

• Add a location field (City, State, Country) to the Location bucket. Add a measure to Size — bubbles scale by value.
• Power BI uses Bing Maps to geocode location names. Precise lat/lon columns are more reliable for Indian tier-2 cities.
• Add a measure to Color Saturation to create a heatmap effect on the bubbles.

Filled Map (Choropleth)

• Fills geographic regions (states, countries) with color based on a measure.
• Add State to Location bucket. Add measure to Color Saturation — darker = higher value.
• For Indian state maps, ensure state names match Bing Maps spelling exactly (e.g., "Tamil Nadu" not "Tamilnadu").

ArcGIS Maps / Azure Maps

• Advanced geo visuals with layers, heat maps, and clustering. Available under Visualizations > More Visuals.
• Requires an ArcGIS/Azure account for full features. Basic version is free.

Example: Sales by Indian State

Custom Visuals from AppSource

• Click the three dots (...) in the Visualizations pane > Get more visuals > AppSource.
• Popular custom visuals: Bullet Chart, Chiclet Slicer, Calendar Visual, Sankey Chart, Word Cloud, Gantt Chart.
• Certified visuals (marked with a blue badge) are reviewed by Microsoft for security and quality.
• Import a .pbiviz file for offline environments: three dots > Import visual from file.

Power BI Themes

• A theme is a JSON file that sets colors, fonts, and default formatting for every visual in your report.
• Go to View > Themes — choose a built-in theme or import a custom JSON file.
• Create your company's branded theme (matching logo colors) and share the JSON file with your team.

Minimal Theme JSON Example

{
  "name": "Lernus Academy Theme",
  "dataColors": ["#f7c948","#1e6fbf","#2e8b57","#c0392b","#8e44ad"],
  "background": "#ffffff",
  "foreground": "#0f1e35",
  "tableAccent": "#f7c948",
  "visualStyles": {
    "*": {
      "*": {
        "fontFamily": [{ "value": "Inter" }]
      }
    }
  }
}

Slicers

• A slicer is an interactive filter widget on the report canvas. Users click it to filter all visuals on the page.
• Add a slicer: click Slicer visual from Visualizations pane, then drag a field into it (Year, Region, Category).
• Slicer types: List, Dropdown, Between (for numeric ranges), Relative Date (Last 7 days, This month).
• Change type in Format pane > Slicer Settings > Style.
• Use Sync Slicers (View > Sync Slicers) to make one slicer filter multiple pages simultaneously.

Report-Level, Page-Level, Visual-Level Filters

• Visual-level: Applies only to one visual. Drag a field to the Visual Filters bucket in the Filters pane.
• Page-level: Applies to all visuals on the current page.
• Report-level: Applies across all pages — use for permanent business filters (e.g., exclude test data, show only current year).
• Filters pane can be hidden from report viewers: Format > Filter pane > Hidden.

Slicer Best Practices

• Always add a "Select All" option for multi-select slicers.
• Group related slicers in a dedicated filter panel area (left or top of the page).
• Use dropdown slicers to save canvas space when you have many filter options.
• Add a Clear All Slicers button using a Blank Button + Reset All Slicers bookmark.

Drill-Through

Drill-through lets users right-click a data point and navigate to a detail page filtered to that specific item.

• Create a detail page (e.g., "Customer Detail"). In the Filters pane, drag the drill-through field (CustomerName) to the Drill-Through section.
• Power BI auto-adds a Back button on the detail page.
• Right-click any customer name in your main report > Drill through > Customer Detail.
• The detail page shows all visuals filtered to that one customer.

Bookmarks

• A bookmark saves the current state: visible pages, slicer values, visual visibility, scroll position.
• View > Bookmarks Pane > Add. Name your bookmark (e.g., "Default View", "South Region View").
• Link a button to a bookmark: Insert > Button > set Action = Bookmark > select bookmark.
• Use case: Toggle between two charts on the same space — one bookmark shows Chart A hidden, one shows Chart B hidden. Button toggles between them.

Report Tooltips

• Create a dedicated Tooltip page (Page Information > Allow use as tooltip = ON, set Page Size = Tooltip).
• Build rich visuals on the tooltip page. In your main visual's Format > Tooltip > Type = Report Page > select your tooltip page.
• Hovering over any data point shows the custom tooltip page — much richer than the default tooltip.

Publishing Steps

• Save your .pbix file in Desktop. Click Home > Publish.
• Sign in with your Microsoft 365 account. Select a Workspace (My Workspace for personal use; shared workspace for team).
• Publishing uploads both the Report and the Dataset (data model) to Power BI Service.
• Open app.powerbi.com in your browser to see the published report.

Sharing Reports

• Share button: Enter colleague email. They receive a link. Requires both parties to have Power BI Pro or the workspace to be in Premium capacity.
• Publish to web (public): Creates a public iframe embed link — anyone with the link can view. Do NOT use for sensitive business data.
• Embed in Teams: Add Power BI tab to a Teams channel — team members see live report inside Teams.
• App: Package multiple reports into a Power BI App and distribute to a large group at once.

Workspaces

• My Workspace: Personal space. Reports visible only to you.
• Shared Workspace: Team space. Add members with roles: Admin, Member, Contributor, Viewer.
• Only Admins and Members can publish reports to a shared workspace.

What is RLS?

Row-Level Security restricts which data rows a user can see in a published report. A Chennai regional manager sees only Chennai data; a Bangalore manager sees only Bangalore — same report, different data views based on login identity.

Setting Up RLS in Power BI Desktop

• Go to Modeling tab > Manage Roles.
• Create a new role (e.g., "South Region"). Add a DAX filter on the relevant table:

// Static RLS — only South region data
[Region] = "South"

// Dynamic RLS — filter by logged-in user's email
[SalesPersonEmail] = USERPRINCIPALNAME()

// Dynamic RLS with mapping table
[Region] IN
    VALUES(
        FILTER(RegionAccess, RegionAccess[UserEmail] = USERPRINCIPALNAME())[Region]
    )

Assigning Users to Roles in Power BI Service

• Publish the report to the Service. Go to the Dataset > Security (three dots menu).
• Under each role, add the email addresses of the users who should be in that role.
• Test RLS as a specific user: Dataset > Security > Test as role.

Scheduled Data Refresh

• After publishing, open the Dataset in Power BI Service > Settings > Scheduled Refresh.
• Toggle "Scheduled Refresh" ON. Set frequency: Daily at 7 AM, or multiple times per day (up to 8x for Pro, 48x for Premium).
• Power BI Service re-runs your data queries at the scheduled time and updates the report automatically.
• Set up email notifications for refresh failure.

On-Premises Data Gateway

• If your data source is on a local server (SQL Server on your office machine, local Excel file), you need a Data Gateway.
• Download Power BI Gateway from powerbi.microsoft.com/gateway. Install on a machine that has access to the data source and stays ON 24/7.
• Configure the gateway in Power BI Service: Settings (gear icon) > Manage Gateways > add data source connection credentials.
• The gateway acts as a secure tunnel between Power BI cloud and your on-premises data.

Gateway Types

Project Overview

Build a complete sales analytics dashboard for a fictional company "Vega Electronics" — a Chennai-based electronics distributor with 4 regions, 3 product categories, and 2 years of monthly sales data.

Data Model

• Sales (Fact): OrderID, Date, ProductID, CustomerID, Region, Qty, Amount, Cost
• Products (Dim): ProductID, ProductName, Category, Brand
• Customers (Dim): CustomerID, CustomerName, City, Segment
• Date (Dim): Date, Year, Month, Quarter, Weekday

Key DAX Measures to Create

Total Revenue = SUM(Sales[Amount])
Total Cost    = SUM(Sales[Cost])
Gross Profit  = [Total Revenue] - [Total Cost]
GP Margin %   = DIVIDE([Gross Profit], [Total Revenue], 0)

Revenue LY    = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Date[Date]))
Revenue YTD   = CALCULATE([Total Revenue], DATESYTD(Date[Date]))

YoY % = DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY], 0)

Product Rank  = RANKX(ALL(Products[ProductName]), [Total Revenue], , DESC, Dense)

Dashboard Pages

• Page 1 — Executive Summary: 4 KPI cards (Revenue, GP Margin, Orders, Customers) + Revenue trend line + Region map.
• Page 2 — Product Performance: Top 10 products bar chart + Category donut + GP Margin by brand matrix.
• Page 3 — Customer Analysis: Revenue by customer segment + RFM scatter + Top 10 customers table.
• Page 4 — Detail (Drill-Through): Order-level table filtered by customer or product.

Project Overview

Build an HR metrics dashboard for a manufacturing company with 500 employees across 5 departments in Chennai. Track headcount, attrition, leave utilization, and payroll summary.

Key HR Measures

// Total active employees
Headcount = CALCULATE(COUNTROWS(Employees), Employees[Status] = "Active")

// Attrition rate
Attrition Rate % =
DIVIDE(
    CALCULATE(COUNTROWS(Employees), Employees[Status] = "Resigned"),
    [Headcount] + CALCULATE(COUNTROWS(Employees), Employees[Status] = "Resigned"),
    0
)

// Average tenure in years
Avg Tenure Years =
AVERAGEX(
    FILTER(Employees, Employees[Status] = "Active"),
    DATEDIFF(Employees[JoiningDate], TODAY(), YEAR)
)

// Total payroll
Total Payroll = SUMX(Employees, Employees[BasicSalary] + Employees[Allowances])

// Leave utilization %
Leave Utilized % = DIVIDE(SUM(Leave[LeaveDays]), SUM(Leave[LeaveEntitlement]), 0)

Dashboard Pages

• Page 1 — Overview: Headcount card, Attrition % card, Avg Tenure, New Joiners this month, Department breakdown donut.
• Page 2 — Attrition Analysis: Monthly attrition trend, Department-wise attrition bar chart, Attrition by tenure bucket.
• Page 3 — Payroll Summary: Total payroll by department, Salary grade distribution, Payroll cost trend YTD.

Project Overview

Build a financial P&L (Profit & Loss) dashboard and Budget vs Actuals comparison for a mid-size company. Data comes from a SQL Server GL (General Ledger) table with account codes, amounts, and periods.

Key Financial Measures

// Actuals from GL
Actual Revenue =
CALCULATE(SUM(GL[Amount]), GL[AccountType] = "Revenue")

Actual Expenses =
CALCULATE(SUM(GL[Amount]), GL[AccountType] = "Expense")

Net Profit = [Actual Revenue] - [Actual Expenses]
Net Margin % = DIVIDE([Net Profit], [Actual Revenue], 0)

// Budget vs Actuals
Budget Revenue = SUM(Budget[Amount])
Revenue Variance = [Actual Revenue] - [Budget Revenue]
Revenue Variance % = DIVIDE([Revenue Variance], [Budget Revenue], 0)

// Budget utilization
Budget Utilized % = DIVIDE([Actual Expenses], SUM(Budget[BudgetAmount]), 0)

P&L Table (Matrix Visual)

Project Overview

Build a supply chain operations dashboard tracking On-Time Delivery (OTD), inventory levels, supplier performance, and procurement spend for a manufacturing company with 3 plants and 50+ suppliers.

Key Supply Chain Measures

// On-Time Delivery %
OTD % =
DIVIDE(
    CALCULATE(COUNTROWS(Deliveries), Deliveries[ActualDate] <= Deliveries[PromisedDate]),
    COUNTROWS(Deliveries),
    0
)

// Average lead time in days
Avg Lead Time =
AVERAGEX(
    PurchaseOrders,
    DATEDIFF(PurchaseOrders[PODate], PurchaseOrders[GRNDate], DAY)
)

// Inventory turnover ratio
Inventory Turnover =
DIVIDE([Total COGS], AVERAGE(Inventory[InventoryValue]))

// Days of inventory on hand
Days Inventory = DIVIDE(365, [Inventory Turnover])

// Supplier spend by category
Supplier Spend = SUM(PurchaseOrders[POAmount])

Dashboard Pages

• Page 1 — Overview: OTD % gauge, Avg Lead Time card, Total Procurement Spend YTD, Spend by category donut.
• Page 2 — Supplier Performance: OTD % by supplier bar chart, Lead time vs. spend scatter chart, Top 10 suppliers by spend table.
• Page 3 — Inventory: Days on hand by material group, Inventory value trend, Slow-moving stock flags (items with no movement in 90 days).

Instructions

Answer all 20 questions. You need 80% (16 correct) to pass and receive your certificate. Take your time — there is no time limit.