Power BI Basics Cheat Sheet
Covers Power BI's data model, DAX measures, star schema design, and common visuals for building interactive business intelligence reports.
2 PagesBeginnerMar 18, 2026
Core Concepts
The building blocks of a Power BI report.
- Power Query- ETL editor (M language) used to import, clean, and shape data before it loads into the model
- Data model- Collection of tables and relationships that Power BI queries visuals against
- DAX- Data Analysis Expressions; formula language for measures and calculated columns
- Measure vs. calculated column- Measures compute at query time based on filter context; columns compute once at refresh and are stored per row
- Report vs. Dashboard- A report is a set of interactive pages built from one dataset; a dashboard pins tiles from one or more reports
- Filter context- The combination of slicers, filters, and row/column context that determines what a DAX measure calculates
DAX Measures
Common measure patterns using CALCULATE and time intelligence.
dax
// Basic measure: total salesTotal Sales = SUM(Sales[Amount])// Measure with filter contextUS Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "US")// Year-over-year growthYoY Growth =DIVIDE( [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])))// Running totalRunning Total =CALCULATE( [Total Sales], FILTER(ALLSELECTED('Date'), 'Date'[Date] <= MAX('Date'[Date])))// Calculated column (stored per row, not a measure)Full Name = Customers[First Name] & " " & Customers[Last Name]
Common Visuals
Frequently used chart types in Power BI reports.
- Card- Displays a single aggregated value (e.g. Total Sales)
- Slicer- Interactive filter control on the report canvas
- Matrix- Pivot-table-like visual supporting row/column grouping and drill-down
- Clustered column chart- Compares values across categories, grouped side by side
- Line chart- Shows trends over a continuous axis, typically time
Iterator Functions & Variables
SUMX/AVERAGEX evaluate an expression per row before aggregating; variables make multi-step DAX readable and avoid re-evaluation.
dax
// Iterator: revenue computed per row, then summed (handles Qty * Price correctly)Total Revenue =SUMX(Sales, Sales[Quantity] * Sales[Unit Price])// AVERAGEX for a weighted-style row-level averageAvg Order Value =AVERAGEX(VALUES(Sales[Order ID]), CALCULATE(SUM(Sales[Amount])))// Variables avoid recomputing the same measure multiple timesMargin % =VAR TotalRev = [Total Revenue]VAR TotalCost = SUMX(Sales, Sales[Quantity] * Sales[Unit Cost])RETURN DIVIDE(TotalRev - TotalCost, TotalRev)// RANKX for a leaderboard measureProduct Rank =RANKX(ALL(Products[Product Name]), [Total Revenue], , DESC)
ALL, ALLEXCEPT, and Relationship Functions
Modify or navigate filter context explicitly rather than relying on implicit visual-level filtering.
dax
// % of overall total, ignoring every filter on the visualPct of Total =DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(Sales)))// Keep only the Region filter, clear every other filter on the Sales tableRevenue by Region Only =CALCULATE([Total Revenue], ALLEXCEPT(Sales, Sales[Region]))// Pull a single related value across a many-to-one relationshipCustomer Segment = RELATED(Customers[Segment])// Aggregate the many side from the one side of a relationshipOrder Count = COUNTROWS(RELATEDTABLE(Orders))// USERELATIONSHIP to activate an inactive relationship for one calculationShipped Sales =CALCULATE([Total Revenue], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]))
Power Query (M) Transformations
Common M patterns for shaping data before it lands in the model, beyond the point-and-click UI.
text
let Source = Sql.Database("server", "db"), Filtered = Table.SelectRows(Source, each [Amount] > 0 and [Region] <> null), Custom = Table.AddColumn(Filtered, "AmountUSD", each [Amount] * [FxRate], type number), Grouped = Table.Group(Custom, {"Region"}, {{"TotalAmount", each List.Sum([AmountUSD]), type number}}), // Parameterized incremental-refresh-friendly date filter RangeStart = #datetime(2024, 1, 1, 0, 0, 0), RangeEnd = #datetime(2024, 12, 31, 0, 0, 0), DateFiltered = Table.SelectRows(Grouped, each true), // Unpivot wide month columns into long format Unpivoted = Table.UnpivotOtherColumns(DateFiltered, {"Region"}, "Month", "Sales")in Unpivoted
Advanced Modeling & Security
Concepts used once a report moves beyond a single flat table into a governed enterprise model.
- Star schema- Fact table of transactional measures surrounded by dimension tables joined on single-column surrogate keys, minimizing DAX ambiguity
- Row-level security (RLS)- Roles defined with DAX filter expressions (e.g. [Region] = USERPRINCIPALNAME()) restrict which rows a user sees at query time
- Calculation groups- Reusable sets of DAX logic (e.g. Time Intelligence: MTD/YTD/YoY) applied to any measure without duplicating formulas per measure
- Composite models- Mix Import and DirectQuery storage modes across tables in the same model, trading freshness for performance per source
- Aggregations- Pre-summarized import tables that Power BI automatically substitutes for DirectQuery detail tables when a query can be answered at summary grain
- VertiPaq (xVelocity) engine- Columnar, compressed in-memory store behind Import mode; column cardinality and data type directly drive model size and query speed
Pro Tip
Build a proper star schema with a dedicated Date dimension table marked as a date table - time intelligence DAX functions like SAMEPERIODLASTYEAR and TOTALYTD require a continuous marked date table to work correctly.
Was this cheat sheet helpful?
Explore Topics
#PowerBIBasics#PowerBIBasicsCheatSheet#DataScience#Beginner#CoreConcepts#DAXMeasures#CommonVisuals#IteratorFunctionsVariables#Databases#MachineLearning#CheatSheet#SkillVeris
