Power BI

1. What Power BI Is

Key points

• Made by Microsoft. It works very well with Excel, SQL Server, Azure, Teams and SharePoint.
• Reports are interactive. Click a bar in one chart and the other charts filter too.
• Three languages you meet: M (Power Query, for cleaning), DAX (for formulas), and a little SQL (to read from databases).
• A Power BI Desktop file is saved as .pbix. A template (no data, only design) is .pbit. A project folder for Git is .pbip.
• It is popular because it is cheap, easy to start, and has a free desktop app.

Our example Sales table

Most examples in these notes use this tiny table. Each row is one sale.

Sales amount for one row = Qty × Price. For the first row that is 10 × 20 = 200.

2. Components & Microsoft Fabric

The parts

Key points

• Desktop is Windows only. Mac users run it in a virtual machine, or edit some things in the Service.
• You can also build simple reports straight in the browser (Service), but Desktop has all the features.
• Microsoft Fabric (since 2023) is Microsoft's all-in-one data platform. Power BI is now one part ("workload") of Fabric.
• Fabric adds data engineering, data warehouses and notebooks. All data sits in one shared lake called OneLake.
• Copilot (AI help) can write DAX, summarise pages and suggest visuals, on paid Fabric capacity.

3. The Power BI Workflow

How to do it (step by step)

1. Get data: Home → Get data → pick a source (Excel, SQL Server, CSV, web…).
2. Transform: click Transform data. Power Query Editor opens. Clean the data, then Close & Apply.
3. Model: go to Model view. Link tables with relationships. Write measures in DAX.
4. Visualise: go to Report view. Drag fields onto the page to build charts.
5. Publish: Home → Publish → choose a workspace in the Service.
6. Share: in the Service, share the report, publish an app, and set up scheduled refresh.

4. Data Sources & Connection Modes

Common sources

• Files: Excel, CSV, JSON, XML, PDF, a whole folder of files.
• Databases: SQL Server, MySQL, PostgreSQL, Oracle, Snowflake, Databricks.
• Cloud / online: SharePoint, OneDrive, Azure SQL, Dataverse, Salesforce, Google Analytics, a web page.
• Power BI / Fabric: existing semantic models, dataflows, lakehouses and warehouses.

The connection modes

• Direct Lake (Fabric only): reads Delta tables in OneLake directly. Nearly Import speed, without a full copy.
• Storage mode for each table is set in Model view → table properties: Import, DirectQuery or Dual (can act as either).

5. Power Query Editor & Applied Steps

Key points

• Open it with Home → Transform data.
• Left side: Queries (each one becomes a table). Middle: a preview of the data. Right side: Applied Steps.
• Click any step to see the data at that moment. Click the ✕ to delete a step.
• The formula bar shows the M code for each step. Turn it on in View → Formula bar.
• Column quality / distribution / profile (View tab) show errors, empty values and unique counts. Great for spotting dirty data.
• Query folding = Power Query turns your steps into one SQL query and lets the database do the work. Faster. Right-click a step → "View Native Query" to check.
• Click Close & Apply to load the clean data into the model.

How to do it

1. Get data → Excel → pick the Sales sheet → Transform Data (not Load).
2. Check the first steps Power BI added: Source, Navigation, Promoted Headers, Changed Type.
3. Add your own steps (next section).
4. Rename steps to clear names, like "Removed blank rows". Right-click → Rename.
5. Close & Apply.

6. Common Transformations

The everyday list

Example: conditional column

Rule: if Qty ≥ 5 then "Bulk" else "Single". Result on our Sales table:

Example: fill down

Excel exports often show a region only on the first row of a group. Fill down fixes it.

7. Merge, Append, Pivot, Unpivot & Group By

Merge vs Append

• Merge join kinds: Left outer (default: all rows from the first table), Right outer, Full outer, Inner (only matches), Left anti (rows with no match: great for finding missing data), Right anti.
• Tip: in a good model you often don't need to merge. Keep Products as its own table and use a relationship instead.

Pivot and Unpivot

Unpivot turns many columns into rows. Pivot does the opposite. Power BI likes long, thin tables, so unpivot is used far more often.

How: select the Product column → Transform → Unpivot Columns → Unpivot Other Columns. This still works when new months are added later.

Group By

Group By makes a summary table, like a SQL GROUP BY. Example: total Qty per Region.

How: Home → Group By → group by Region → new column "Total Qty", operation Sum, column Qty.

8. M Language Basics

Key points

• See all the code in Home → Advanced Editor.
• A query is a let ... in block. Each line inside let is one step. in says which step is the final result.
• Each step uses the step before it, by name.
• M is case-sensitive. Table.SelectRows works; table.selectrows fails.
• each [Qty] > 0 means "for each row, check Qty is greater than 0".
• Step names with spaces are written like #"Changed Type".

let
    // 1. Read the CSV file
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter = ",", Encoding = 65001]),
    // 2. Use the first row as column names
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    // 3. Set data types
    Typed = Table.TransformColumnTypes(Promoted,
        {{"Date", type date}, {"Qty", Int64.Type}, {"Price", type number}}),
    // 4. Keep only rows with Qty above 0
    Filtered = Table.SelectRows(Typed, each [Qty] > 0),
    // 5. Add Amount = Qty * Price
    Added = Table.AddColumn(Filtered, "Amount", each [Qty] * [Price], type number)
in
    Added

9. Fact vs Dimension Tables

Key points

• Fact: many rows, grows every day, mostly numbers and ID columns (keys). Example: Sales.
• Dimension: fewer rows, mostly text, one row per thing. Example: Product, Store, Date, Customer.
• You sum and count fact columns. You filter and group by dimension columns.
• They are linked by a key: Sales[ProductID] matches Product[ProductID].
• Granularity (grain) = what one row of the fact means. Example: "one row = one product in one order".

Our flat Sales table, split into a model

The rest of these notes use this model: Sales, Product, Store and 'Date'.

10. Star vs Snowflake Schema

How to turn a snowflake into a star

1. In Power Query, merge Category into Product (Merge Queries on CategoryID).
2. Expand the Category Name column.
3. Right-click the Category query → untick Enable load, so it doesn't load as a separate table.

11. Relationships, Cardinality & Filter Direction

Cardinality

• One-to-many (1:*): one row in Product matches many rows in Sales. The "one" side must have unique keys. This is 90% of relationships.
• One-to-one (1:1): each row matches exactly one row. Usually means the two tables should be merged into one.
• Many-to-many (*:*): neither side is unique. Example: a Budget table by Category joined to Product on Category. Works, but can give confusing totals. A bridge table (a table of unique keys in the middle) is often cleaner.

Cross-filter direction

• Single (default, recommended): filters go from the "one" side (dimension) to the "many" side (fact). Picking "Pen" filters Sales.
• Both (bi-directional): filters go both ways. Useful for some many-to-many cases, but can slow the model and cause ambiguity (more than one path between tables).
• Prefer turning on "both" only inside one measure with CROSSFILTER(..., Both), instead of on the whole relationship.

Active vs inactive relationships

• Only one active relationship can exist between two tables. It is a solid line in Model view.
• Extra ones are inactive (dotted line). Example: Sales has OrderDate and ShipDate, both linked to 'Date'[Date].
• Use an inactive one inside a measure with USERELATIONSHIP:

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )   -- use the dotted line for this measure only
)

How to create a relationship

1. Go to Model view.
2. Drag Product[ProductID] onto Sales[ProductID].
3. Double-click the line. Check cardinality (Many to one) and cross-filter direction (Single).
4. Power BI may auto-detect relationships on load. Always check them.

12. The Date (Calendar) Table

Key points

• Time intelligence functions (YTD, last year) need a proper date table to work correctly.
• Rules: one row per date, unique dates, no blanks, no gaps. Best to cover whole years.
• CALENDAR(start, end) makes dates between two dates you choose.
• CALENDARAUTO() looks at all dates in your model and makes full years that cover them.
• Turn off Auto date/time (File → Options → Data Load). It creates hidden date tables for every date column and makes the file bigger.

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month No", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Weekday", FORMAT ( [Date], "ddd" )
)

How to do it

1. Modeling → New table → paste the formula above.
2. Select the table → Table tools → Mark as date table → choose the Date column.
3. Select the Month column → Sort by column → Month No. Otherwise months sort A–Z (Apr, Aug, Dec…).
4. Create a relationship: 'Date'[Date] (one) → Sales[OrderDate] (many).
5. Always use 'Date' columns in visuals and slicers, not Sales[OrderDate].

13. DAX Basics: Calculated Column vs Measure vs Calculated Table

Key points

• Syntax: Name = expression. Columns are written Table[Column]. Measures are written [Measure].
• Table names with spaces or reserved words need quotes: 'Date'[Year].
• Prefer measures. They don't use memory and they respond to every filter.
• Keep measures in one empty table called _Measures so they are easy to find.
• Comments: -- or // for one line, /* */ for many lines.
• An implicit measure is when you drag a number column and Power BI sums it for you. An explicit measure is one you write. Write explicit ones; they are reusable.

14. Row Context vs Filter Context

Row context

• Exists in calculated columns and inside iterator functions like SUMX.
• Lets you write Sales[Qty] * Sales[Price]: "this row's Qty times this row's Price".
• Row context does not filter anything by itself.

Filter context

• Comes from everything around a visual cell: slicers, rows/columns of a matrix, chart axes, the Filters pane, cross-filtering from other visuals, and RLS.
• Every measure is calculated inside a filter context. That's why the same measure shows a different number in every cell.
• CALCULATE is the function that changes filter context.

Example

A matrix with Region on rows, and the measure Total Qty = SUM(Sales[Qty]):

Context transition (one line): when CALCULATE runs inside a row context, it turns the current row into a filter. A measure used in a calculated column does this automatically.

15. Aggregations & Iterators (SUM vs SUMX)

The basic aggregators

Iterators: SUMX, AVERAGEX, COUNTX, MAXX…

• Syntax: SUMX(table, expression). It creates a row context on the table.
• You can't write SUM(Sales[Qty] * Sales[Price]). SUM takes only one column. Use SUMX.
• Iterators don't need a stored "Amount" column, so the model stays smaller.

Total Qty = SUM ( Sales[Qty] )

-- Row by row: Qty * Price, then add all results
Total Sales = SUMX ( Sales, Sales[Qty] * Sales[Price] )
-- 200 + 240 + 120 + 900 + 480 = 1,940

Orders = COUNTROWS ( Sales )

Avg Sale per Order = AVERAGEX ( Sales, Sales[Qty] * Sales[Price] )   -- 1,940 / 5 = 388

Unique Products = DISTINCTCOUNT ( Sales[ProductID] )

16. CALCULATE & FILTER

Key points

• Syntax: CALCULATE(expression, filter1, filter2, ...). Many filters are joined with AND.
• A simple filter like Store[Region] = "North" replaces any existing filter on that column.
• To keep the existing filter and add yours on top, wrap it in KEEPFILTERS(...).
• FILTER(table, condition) goes row by row (an iterator). Use it when the condition is complex, like comparing a measure or two columns.
• Don't use FILTER on a whole big fact table when a simple column filter will do. It is slower.

-- Sales only for the North region
North Sales =
CALCULATE ( [Total Sales], Store[Region] = "North" )

-- Two conditions (AND)
North Pen Sales =
CALCULATE ( [Total Sales], Store[Region] = "North", Product[Product] = "Pen" )

-- OR inside one column
North or South Sales =
CALCULATE ( [Total Sales], Store[Region] IN { "North", "South" } )

-- FILTER for a row-level condition: only big orders (amount over 1,000)
Big Order Sales =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Qty] * Sales[Price] > 1000 )
)

-- Count products that sold more than 500 in total (FILTER over a measure)
Top Products Count =
COUNTROWS ( FILTER ( VALUES ( Product[Product] ), [Total Sales] > 500 ) )

17. ALL, ALLEXCEPT & REMOVEFILTERS

Key points

• ALL(table) or ALL(column): ignore all filters on that table or column.
• ALLEXCEPT(table, column): ignore all filters on the table except the columns you list.
• REMOVEFILTERS(...): same as ALL when used inside CALCULATE. Newer and clearer name.
• ALLSELECTED(...): ignore filters from inside the visual, but keep slicer/page filters. Good for "% of what is shown".
• ALL can also return a table (e.g. for RANKX). REMOVEFILTERS can only be used as a CALCULATE filter.

-- Total for all regions, no matter what is selected
All Region Sales =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Store[Region] ) )

-- % of total across regions
Region % of Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Store[Region] ) ) )

-- % of the product's own category (keep Category filter, drop the rest of Product)
% of Category =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) ) )

Result in a matrix

18. RELATED & RELATEDTABLE

Key points

• Both need an existing relationship between the tables.
• Used in calculated columns or inside iterators like SUMX (they need a row context).
• RELATED works like Excel's VLOOKUP, but it uses the relationship, so it is safer.

-- Calculated column in Sales (many side): fetch the product's category
Category = RELATED ( Product[Category] )

-- Calculated column in Product (one side): how many sales rows each product has
Order Count = COUNTROWS ( RELATEDTABLE ( Sales ) )

-- Measure: sales using a price stored in the Product table
Sales at List Price =
SUMX ( Sales, Sales[Qty] * RELATED ( Product[ListPrice] ) )

19. IF, SWITCH, DIVIDE & VAR / RETURN

Key points

• IF(condition, value_if_true, value_if_false). Same idea as Excel.
• SWITCH replaces many nested IFs. SWITCH(TRUE(), ...) checks conditions from top to bottom and takes the first true one.
• DIVIDE(a, b, alternate) returns BLANK (or your alternate value) when b is 0. The / operator gives an error (infinity) instead.
• VAR stores a value. RETURN gives the final answer. Variables are calculated once, so code is faster and easier to read.
• Logic operators: && = AND, || = OR. & joins text.

-- IF
Sales Status = IF ( [Total Sales] >= 1000, "On target", "Below target" )

-- SWITCH(TRUE()) for bands
Sales Band =
SWITCH (
    TRUE (),
    [Total Sales] >= 100000, "High",
    [Total Sales] >= 50000, "Medium",
    "Low"                                   -- default if nothing matched
)

-- Safe division
Avg Price = DIVIDE ( [Total Sales], [Total Qty], 0 )

-- VAR / RETURN: profit margin
Profit Margin % =
VAR Revenue = [Total Sales]
VAR Cost = SUMX ( Sales, Sales[Qty] * RELATED ( Product[UnitCost] ) )
RETURN
    DIVIDE ( Revenue - Cost, Revenue )

20. Time Intelligence

Key functions

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

-- Fiscal year ending 31 March (common in India)
Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3-31" )

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

Sales LM = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

-- Year-over-year growth %
YoY % =
VAR CurrentSales = [Total Sales]
VAR LastYearSales = [Sales LY]
RETURN
    DIVIDE ( CurrentSales - LastYearSales, LastYearSales )

-- Rolling 3 months
Sales Rolling 3M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

YoY example

• Format YoY % as a percentage (Measure tools → Format → Percentage).
• Newer Power BI versions also have calendar-based time intelligence (preview since late 2025) for custom calendars like 4-4-5 retail weeks. The classic functions above are still what interviews ask.

21. RANKX

Key points

• Syntax: RANKX(table, expression, [value], [order], [ties]).
• The table is usually ALL(Product[Product]). Without ALL, each row only sees itself and every product gets rank 1. This is the classic mistake.
• Order: DESC (default, highest = 1) or ASC.
• Ties: Skip (default: 1, 2, 2, 4) or Dense (1, 2, 2, 3).
• For a "Top 5" visual you can also use the visual-level Top N filter, no DAX needed.
• Newer window functions like RANK, WINDOW and OFFSET also exist. RANKX is still the one interviews expect.

Product Rank =
RANKX ( ALL ( Product[Product] ), [Total Sales], , DESC, Dense )

-- Show sales only for the top 3 products, blank for others
Top 3 Sales =
IF ( [Product Rank] <= 3, [Total Sales] )

22. Choosing Visuals

Key points

• Card: shows one number. The newer card visual can show several numbers in one box.
• Slicer: an on-page filter (list, dropdown, date range, buttons). Users click it to filter the page.
• Table vs matrix: a table is a flat list (like Excel rows). A matrix is a pivot table: rows and columns, subtotals, and it can expand/collapse levels.
• Maps: Microsoft is replacing the older Map and Filled map visuals with Azure Maps. Map data needs clear location columns; set the Data category (City, Country) in Column tools.
• Custom visuals: extra visuals from AppSource (Get more visuals), or a .pbiviz file. Prefer certified ones (checked by Microsoft).
• Python and R visuals exist too, for special charts.
• Keep pages simple: 5–8 visuals, important KPIs at the top-left, consistent colours. Use a theme (View → Themes).

23. Interactions, Hierarchies, Drill-down & Drill-through

Interactions

• Default: click a bar → other visuals cross-highlight or cross-filter.
• Change it: select a visual → Format → Edit interactions. On each other visual choose Filter, Highlight or None.
• Ctrl + click selects many items.
• Sync slicers (View → Sync slicers) makes one slicer work across many pages.
• Filter levels in the Filters pane: visual, page, all pages (report). Plus drill-through filters.

Hierarchies and drill-down

• Create a hierarchy: in the Data pane, right-click Year → Create hierarchy → drag Quarter and Month into it.
• Or just put several fields in a visual's axis: Category, then Product.
• Drill icons on the visual: ↓ (turn on drill mode, then click a bar), ⇊ (next level for all), ⤓ (expand all down one level), ↑ (drill up).

Drill-through: how to do it

1. Create a new page called "Product Details".
2. Drag Product[Product] into the page's Drill through box (in the Visualizations pane).
3. Power BI adds a Back button automatically.
4. On any other page, right-click a bar for "Pen" → Drill through → Product Details.
5. The details page opens, filtered to Pen.

24. Bookmarks, Tooltip Pages & Conditional Formatting

Bookmarks

• View → Bookmarks pane → set up the page → Add.
• Use with buttons: Insert → Button → Action → Type: Bookmark.
• Common uses: "Reset filters" button, toggle between chart and table, show/hide a help panel (with the Selection pane).
• Each bookmark can choose to save Data (filters), Display (visible visuals) and Current page.
• Personal bookmarks in the Service let viewers save their own views.

Tooltip pages: how to do it

1. Add a new page. In Format page → Page information, turn on Allow use as tooltip.
2. Set Canvas settings → Type: Tooltip (small size).
3. Add small visuals to it, e.g. a monthly sales line.
4. On the main visual: Format → General → Tooltips → Type: Report page → choose your page.
5. Hover a bar: the mini page appears, filtered to that bar.

Conditional formatting

• On a table/matrix: select the field in the Values well → down arrow → Conditional formatting.
• Types: background colour, font colour, data bars, icons (arrows, traffic lights), web URL.
• Format by: Gradient (colour scale), Rules (e.g. < 0 red, ≥ 0 green), or Field value (a measure that returns a colour name or hex code).

-- Measure returning a colour, used with "Format by: Field value"
YoY Colour = IF ( [YoY %] < 0, "#D64545", "#2F6F4E" )

25. Parameters: What-if & Field Parameters

What-if parameter

1. Modeling → New parameter → Numeric range.
2. Name "Discount", min 0, max 0.5, increment 0.05. Tick "Add slicer to this page".
3. Power BI creates a table, a slicer and a measure for you.
4. Use the value in your own measure.

-- Created by Power BI
Discount = GENERATESERIES ( 0, 0.5, 0.05 )
Discount Value = SELECTEDVALUE ( Discount[Discount], 0 )

-- Your measure
Sales After Discount = [Total Sales] * ( 1 - [Discount Value] )

Field parameter

1. Modeling → New parameter → Fields.
2. Pick fields, e.g. Product[Category], Store[Region], 'Date'[Month].
3. Power BI adds a slicer. Put the parameter on a chart's axis.
4. Users pick "Region" in the slicer and the chart switches to show sales by Region.

• Field parameters can also switch measures: Sales / Qty / Profit with one slicer.
• Power Query also has query parameters (Home → Manage Parameters), e.g. a server name used in the Source step. Different from these report parameters.

26. Row-Level Security (RLS)

Key points

• You create roles with DAX filter rules on tables. The filter flows through relationships to the facts.
• Static RLS: the value is fixed in the rule. One role per region: "North", "South"…
• Dynamic RLS: one rule for everyone, using USERPRINCIPALNAME(), which returns the logged-in user's email.
• RLS applies to people with the Viewer role. Workspace Admins, Members and Contributors bypass RLS.
• Object-Level Security (OLS) hides whole tables or columns (set up with external tools like Tabular Editor).

Static RLS

-- Role "North" — rule on the Store table
[Region] = "North"

Dynamic RLS

Add a small mapping table that says which user sees which region:

-- Role "Regional Manager" — rule on the UserRegion table
-- Relationship: UserRegion[Region] → Store[Region] (filter must reach Store and Sales)
[Email] = USERPRINCIPALNAME ()

Simpler version when the email is already on the Store table: rule on Store is [ManagerEmail] = USERPRINCIPALNAME().

How to do it

1. Desktop: Modeling → Manage roles → New role → pick a table → write the DAX rule.
2. Test: Modeling → View as → choose a role (and "Other user" for an email).
3. Publish.
4. Service: semantic model → ⋯ → Security → add people or groups to each role.

27. Power BI Service & Licences

The main items

• Workspace: a shared folder for a team's content. My workspace is your private one.
• Workspace roles: Admin (everything), Member (edit + share), Contributor (edit), Viewer (only view).
• Semantic model (old name: dataset): the data + relationships + measures you published. Many reports can use one semantic model.
• Report: the pages of visuals. Dashboard: one page of pinned tiles (next section).
• Dataflow: Power Query in the cloud. Clean once, reuse in many models.
• App: a packaged, read-only bundle of reports and dashboards from a workspace. The best way to share with large groups of viewers.
• Deployment pipelines: move content from Development → Test → Production.
• Endorsement: mark a semantic model as Promoted or Certified so people trust it.

Ways to share

1. Workspace access: add colleagues to the workspace with a role. Good for the team that builds.
2. Share a report link with specific people. Good for a few people.
3. Publish an app: best for many viewers (whole department).
4. Embed in Teams, SharePoint or a website.
5. Publish to web: anyone on the internet can see it. Never use it for company data.
6. Also: Analyze in Excel, export to PDF/PowerPoint, email subscriptions, data alerts on dashboard tiles.

Licences at a glance

Prices change, so check Microsoft's pricing page. In 2025, Pro was about $14 and PPU about $24 per user per month.

28. Reports vs Dashboards

29. Refresh & Gateways

Key points

• Import models need refresh. DirectQuery and live connections don't store data, so they show fresh data on each click (but on-premises DirectQuery still needs a gateway).
• On-premises data gateway (standard mode): shared by many users, managed centrally. Recommended for companies.
• Personal mode: only for one user, Import only. Fine for learning.
• VNet data gateway: a Microsoft-managed gateway for data inside Azure private networks.
• Cloud sources (SharePoint Online, OneDrive, Azure SQL) don't need a gateway.
• Incremental refresh: refresh only new data (e.g. last 7 days) instead of the full history. Uses RangeStart and RangeEnd parameters.
• Refresh history and failure emails help you catch problems (wrong password, gateway offline).

How to set up scheduled refresh

1. Install the gateway on an always-on machine in the office and sign in.
2. In the Service: Settings → Manage connections and gateways → add a data source connection with credentials.
3. Open the semantic model → Settings → Gateway and cloud connections → map the source to the gateway.
4. Turn on Scheduled refresh, choose time zone and times (e.g. 7 AM daily).

30. Performance Tips

Find the problem first

• Performance Analyzer (Optimize tab → Performance analyzer; older versions: View tab). Click Start recording, then Refresh visuals.
• It shows time for each visual: DAX query, Visual display, and Other (waiting). Copy the query to DAX Studio for deeper checks.
• Tools like DAX Studio (VertiPaq Analyzer) show which columns use the most memory.

Model tips

• Remove unused columns and rows. The best data is data you don't load.
• Use a star schema. Avoid many-to-many and bi-directional filters.
• Columns with few unique values compress well. Split date-time into separate date and time columns; round long decimals.
• Turn off Auto date/time.
• Use correct data types. Whole numbers compress better than text.
• Keep query folding working in Power Query.

DAX and report tips

• Prefer measures over calculated columns.
• Use VAR so a value is calculated once.
• Filter columns, not whole tables: CALCULATE([Sales], Store[Region] = "North") instead of FILTER(Sales, ...).
• Use DIVIDE, and avoid deep IF chains with SWITCH.
• Fewer visuals per page. Each visual sends its own query.
• Use aggregations or Import for summary tables when the fact is huge in DirectQuery.

31. Power BI vs Tableau vs Excel

32. End-to-End: A Sales Dashboard

The task

"The sales head wants to see total sales, growth vs last year, best products, and sales by region, with a filter for year and region. Data is in Excel files and refreshed daily."

Step by step

1. Get data: Get data → Folder → the folder of monthly Excel files → Combine & Transform. Also load the Products and Stores sheets.
2. Transform: remove empty rows, set data types (Date, Whole number, Decimal), trim text, fix "Nrth" → "North", remove unused columns.
3. Model: create the 'Date' table with CALENDARAUTO, mark it as a date table. Relationships: Date, Product and Store (one) → Sales (many), single direction. Star schema.
4. Measures (in a _Measures table):

Total Sales = SUMX ( Sales, Sales[Qty] * Sales[Price] )
Total Qty = SUM ( Sales[Qty] )
Orders = COUNTROWS ( Sales )
Avg Order Value = DIVIDE ( [Total Sales], [Orders] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Product Rank = RANKX ( ALL ( Product[Product] ), [Total Sales], , DESC, Dense )

5. Visualise (one main page):
   • Top row: 4 cards — Total Sales, Orders, Avg Order Value, YoY %.
   • Line chart: Total Sales and Sales LY by Month.
   • Bar chart: Top 5 products (Top N filter on Total Sales).
   • Map or bar: Sales by Region.
   • Matrix: Category → Product with Total Sales and YoY %, conditional formatting red/green.
   • Slicers: Year and Region, synced across pages.
6. Extras: a drill-through page "Product Details", a tooltip page with the monthly trend, a "Reset filters" bookmark button.
7. Security: dynamic RLS so each regional manager sees only their region.
8. Publish to a "Sales" workspace. Put the Excel folder on SharePoint (no gateway needed) or set up a gateway for a local folder.
9. Refresh: scheduled daily at 7 AM.
10. Share: publish an app for the sales team. Pin the cards to a dashboard and add a data alert if YoY % drops below 0.

Insights to talk about

• "North gave 57% of sales, mainly from one expensive product (Bag)."
• "February sales fell 5% vs last year; Pen sales dropped in South."
• "I would suggest a promotion on Notebooks in East, the fastest-growing region."

Quick Revision

The most-asked Power BI questions, with short and simple answers. Say these in your own words.

Basics

• What is Power BI? A Microsoft BI tool to connect, clean, model, visualise and share data as interactive reports.
• Main components? Desktop (build), Service (share), Mobile (view), Report Server (on-premises).
• What is the workflow? Get data → Transform → Model → Visualise → Publish → Share.
• Import vs DirectQuery? Import copies data (fast, needs refresh). DirectQuery asks the source each time (fresh, slower).
• What is a live connection? Connecting to an existing semantic model or Analysis Services. You can't edit the model.
• What is a composite model? A model mixing Import and DirectQuery tables.
• What is Microsoft Fabric? Microsoft's all-in-one data platform; Power BI is one part of it.

Power Query

• Power Query vs DAX? Power Query (M) cleans and shapes data before loading. DAX calculates after loading.
• What are applied steps? The saved list of transformations, repeated on every refresh.
• Merge vs Append? Merge = join columns (like SQL JOIN). Append = stack rows (like UNION).
• What is unpivot? Turning many columns (Jan, Feb, Mar) into rows (Month, Value).
• What is query folding? Power Query sending your steps back to the source as one query, so the source does the work.

Modelling

• Fact vs dimension? Fact = events with numbers (Sales). Dimension = descriptions (Product, Date).
• Star vs snowflake? Star = dimensions link straight to the fact. Snowflake = dimensions split further. Star is best in Power BI.
• Types of cardinality? One-to-one, one-to-many (most common), many-to-many.
• Single vs both direction? Single = filter flows dimension → fact (default). Both = flows both ways; use carefully.
• Active vs inactive relationship? Only one active between two tables. Use an inactive one with USERELATIONSHIP.
• Why a date table? Time intelligence needs one row per day, no gaps. Mark it as a date table.

DAX

• Calculated column vs measure? Column = stored, per row, fixed. Measure = calculated at query time, reacts to filters.
• Row vs filter context? Row = "this row". Filter = "these filters are on".
• SUM vs SUMX? SUM adds one column. SUMX calculates per row, then adds.
• COUNT vs COUNTROWS vs DISTINCTCOUNT? Non-blank values in a column / rows in a table / unique values.
• What does CALCULATE do? Evaluates an expression with changed filters.
• ALL vs ALLEXCEPT? ALL removes all filters. ALLEXCEPT removes all except the columns you name.
• RELATED vs RELATEDTABLE? Many → one value / one → many rows.
• Why DIVIDE? It handles divide-by-zero safely.
• Why VAR? Readable code, and the value is calculated only once.
• How do you do YoY %? (Sales − SAMEPERIODLASTYEAR Sales) ÷ last year's Sales, with DIVIDE.
• Why is every RANKX rank 1? You forgot ALL around the table.

Reports, Service & Security

• Drill-down vs drill-through? Drill-down = next level in the same visual. Drill-through = jump to a detail page.
• What is a bookmark? A saved view of a page (filters, visible visuals), often used with buttons.
• Table vs matrix? Table = flat list. Matrix = pivot with rows, columns and subtotals.
• What-if vs field parameter? What-if changes a number. Field parameter changes which field or measure is shown.
• Report vs dashboard? Report = many pages, one model, interactive. Dashboard = one page of pinned tiles from many reports, Service only.
• What is RLS? Row-Level Security: users see only their rows. Dynamic RLS uses USERPRINCIPALNAME().
• When do you need a gateway? When the Service must reach on-premises data.
• How many scheduled refreshes? 8 a day on Pro, 48 a day on PPU or capacity.
• Free vs Pro? Free can't share. Pro can share and collaborate.
• How do you make a report faster? Performance Analyzer, remove columns, star schema, measures over calculated columns.

Learning Path

Go step by step. Build something small at each step. Install Power BI Desktop (free) first.

1. Excel basics — sorting, filtering, pivot tables. Power BI ideas build on these.
2. Get data + Power Query — load a CSV and an Excel file, clean them, merge and append.
3. First report — cards, bar, line, slicers, a table. Learn interactions.
4. Data modelling — fact and dimension tables, star schema, relationships, a date table.
5. DAX part 1 — SUM, SUMX, COUNTROWS, DIVIDE, measures vs columns.
6. DAX part 2 — CALCULATE, ALL, FILTER, time intelligence, RANKX, VAR.
7. Report polish — drill-through, tooltips, bookmarks, conditional formatting, themes.
8. Service — publish, workspaces, apps, RLS, scheduled refresh (a free Fabric trial helps).
9. Project — build the sales dashboard from section 32 on a public dataset (e.g. a superstore sales sample). Put screenshots in your portfolio.
10. Then — practise SQL alongside, and consider the Microsoft PL-300 (Power BI Data Analyst) certification.