Power BI Cheat Sheet: Quick Reference Guide

Power BI is Microsoft’s powerful business intelligence tool for data visualization and analytics. This comprehensive cheat sheet provides quick references for DAX formulas, keyboard shortcuts, best practices, and common tasks.

Table of Contents

• DAX Functions
• Power Query M Functions
• Keyboard Shortcuts
• Data Modeling Best Practices
• Visualization Tips
• Performance Optimization
• Common DAX Patterns
• Publishing and Sharing
• Quick Tips
• Common Errors and Solutions
• Resources
• Machine Learning Integration
• Python and R Scripting
• Advanced Analytics Visuals
• Time Series Forecasting
• Anomaly Detection and Alerts
• Conclusion

DAX Functions

Aggregation Functions

// Basic aggregations
SUM([Column])
AVERAGE([Column])
MIN([Column])
MAX([Column])
COUNT([Column])
COUNTROWS(Table)
DISTINCTCOUNT([Column])

// Count non-blank values
COUNTA([Column])

// Count blank values
COUNTBLANK([Column])

Calculate Function

// Basic CALCULATE syntax
Total Sales = CALCULATE(
    SUM(Sales[Amount]),
    Sales[Year] = 2023
)

// Multiple filters
Filtered Sales = CALCULATE(
    SUM(Sales[Amount]),
    Sales[Year] = 2023,
    Sales[Region] = "West"
)

// ALL - Remove filters
Total All Sales = CALCULATE(
    SUM(Sales[Amount]),
    ALL(Sales)
)

// ALLEXCEPT - Remove all filters except specified
Sales By Category = CALCULATE(
    SUM(Sales[Amount]),
    ALLEXCEPT(Sales, Sales[Category])
)

Time Intelligence Functions

// Year to Date
YTD Sales = TOTALYTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

// Previous Year
PY Sales = CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Month to Date
MTD Sales = TOTALMTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

// Year over Year Growth
YoY Growth = 
DIVIDE(
    [Total Sales] - [PY Sales],
    [PY Sales]
)

// Date Add (shift dates)
Last Year Sales = CALCULATE(
    SUM(Sales[Amount]),
    DATEADD('Date'[Date], -1, YEAR)
)

Filter Functions

// FILTER - Returns a table with filtered rows
High Value Sales = CALCULATE(
    [Total Sales],
    FILTER(Sales, Sales[Amount] > 1000)
)

// KEEPFILTERS - Preserve existing filters
Adjusted Sales = CALCULATE(
    SUM(Sales[Amount]),
    KEEPFILTERS(Sales[Category] = "Electronics")
)

// REMOVEFILTERS - Remove specific filters
All Time Sales = CALCULATE(
    SUM(Sales[Amount]),
    REMOVEFILTERS('Date')
)

Logical Functions

// IF statement
Status = IF(
    [Total Sales] > 10000,
    "High",
    "Low"
)

// Multiple conditions with AND/OR
Category = 
IF(
    AND([Total Sales] > 10000, [Units Sold] > 100),
    "Premium",
    IF(
        OR([Total Sales] > 5000, [Units Sold] > 50),
        "Standard",
        "Basic"
    )
)

// SWITCH (cleaner than nested IFs)
Rating = 
SWITCH(
    TRUE(),
    [Score] >= 90, "Excellent",
    [Score] >= 75, "Good",
    [Score] >= 60, "Average",
    "Poor"
)

Text Functions

// Concatenate
Full Name = [First Name] & " " & [Last Name]

// Format
Formatted Date = FORMAT('Date'[Date], "MMM DD, YYYY")
Formatted Number = FORMAT([Amount], "$#,##0.00")

// Upper/Lower case
UPPER([Column])
LOWER([Column])

// Substring
LEFT([Column], 5)
RIGHT([Column], 3)
MID([Column], 2, 4)

// Replace
SUBSTITUTE([Column], "Old", "New")

// Trim spaces
TRIM([Column])

Relationship Functions

// RELATED - Get value from related table (many-to-one)
Product Category = RELATED(Products[Category])

// RELATEDTABLE - Get table from related table (one-to-many)
Total Order Items = COUNTROWS(RELATEDTABLE(OrderDetails))

// USERELATIONSHIP - Activate inactive relationship
Sales Shipped Date = CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])
)

Table Functions

// SUMMARIZE - Create summary table
Summary = 
SUMMARIZE(
    Sales,
    Sales[Category],
    Sales[Region],
    "Total", SUM(Sales[Amount])
)

// ADDCOLUMNS - Add calculated columns to table
Extended = 
ADDCOLUMNS(
    Products,
    "Margin", Products[Price] - Products[Cost]
)

// VALUES - Get unique values
Unique Categories = VALUES(Sales[Category])

// DISTINCT - Get distinct values (includes blank)
Distinct Customers = DISTINCT(Sales[CustomerID])

Statistical Functions

// Standard Deviation
STDEV.P([Column])  // Population
STDEV.S([Column])  // Sample

// Variance
VAR.P([Column])    // Population
VAR.S([Column])    // Sample

// Median
MEDIAN([Column])

// Percentile
PERCENTILEX.INC(Table, [Column], 0.95)

Ranking Functions

// RANKX - Rank values
Product Rank = 
RANKX(
    ALL(Products[ProductName]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

// TOPN - Get top N rows
Top 10 Products = 
TOPN(
    10,
    ALL(Products),
    [Total Sales],
    DESC
)

Power Query M Functions

Common Transformations

// Remove columns
Table.RemoveColumns(Source, {"Column1", "Column2"})

// Rename columns
Table.RenameColumns(Source, {{"OldName", "NewName"}})

// Change data type
Table.TransformColumnTypes(Source, {{"Column", Int64.Type}})

// Filter rows
Table.SelectRows(Source, each [Amount] > 100)

// Add custom column
Table.AddColumn(Source, "NewColumn", each [Column1] * [Column2])

// Replace values
Table.ReplaceValue(Source, "Old", "New", Replacer.ReplaceText, {"Column"})

// Merge queries (Join)
Table.NestedJoin(Source1, "Key", Source2, "Key", "NewColumn", JoinKind.Inner)

// Append queries (Union)
Table.Combine({Source1, Source2})

// Group by
Table.Group(Source, {"Category"}, {{"Total", each List.Sum([Amount]), type number}})

// Pivot column Table.Pivot(Source, List.Distinct(Source[Category]), “Category”, “Value”)

// Unpivot columns Table.UnpivotOtherColumns(Source, {“ID”}, “Attribute”, “Value”)

### Date Functions

```m
// Get current date/time
DateTime.LocalNow()
Date.From(DateTime.LocalNow())

// Date parts
Date.Year([Date])
Date.Month([Date])
Date.Day([Date])
Date.DayOfWeek([Date])

// Date calculations
Date.AddDays([Date], 7)
Date.AddMonths([Date], 1)
Date.AddYears([Date], 1)

// Date difference
Duration.Days([EndDate] - [StartDate])

Keyboard Shortcuts

General Shortcuts

Visual Manipulation

Data View

Formatting

Data Modeling Best Practices

Star Schema Design

Fact Table (Sales)
├── Date Key → Dim Date
├── Product Key → Dim Product
├── Customer Key → Dim Customer
└── Store Key → Dim Store

Best Practices:

• Keep fact tables narrow (only keys and measures)
• Create dimension tables for descriptive attributes
• Use surrogate keys (integers) for relationships
• Avoid many-to-many relationships when possible
• Create a dedicated Date dimension table

Relationship Types

• One-to-Many (1:*): Most common, from dimension to fact table
• Many-to-One (*:1): Opposite direction of one-to-many
• One-to-One (1:1): Rare, usually indicates poor design
• Many-to-Many (:): Use bridge tables or DAX instead

Cardinality Best Practices

✓ Good: Dimension (1) → Fact (*)
✗ Avoid: Fact (*) ← (*) Fact
✓ Use: Bridge tables for many-to-many
✓ Mark: Date tables as date table

Visualization Tips

Chart Selection Guide

Color Best Practices

• Limit colors: Use 5-7 colors maximum
• Consistency: Use same colors for same categories across visuals
• Accessibility: Ensure sufficient contrast (WCAG 2.0 AA)
• Meaning: Use red for negative, green for positive
• Branding: Incorporate company colors

Design Principles

1. Less is More: Remove unnecessary elements
2. Alignment: Align visuals in a grid
3. White Space: Don’t overcrowd the canvas
4. Hierarchy: Most important metrics at top-left
5. Consistency: Use same fonts, colors, sizes

Performance Optimization

DAX Optimization

// ✗ Slow - Calculated column
TotalCost = Sales[Quantity] * Sales[UnitPrice]

// ✓ Fast - Measure
Total Cost = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

// ✗ Slow - Row context iteration
BadMeasure = SUMX(Sales, CALCULATE(SUM(Costs[Amount])))

// ✓ Fast - Filter context
GoodMeasure = SUM(Costs[Amount])

Query Optimization

Best Practices:

• Remove unnecessary columns in Power Query
• Filter data early in the transformation
• Use query folding when possible
• Disable auto date/time hierarchy
• Use aggregated tables for large datasets
• Implement incremental refresh

Data Model Optimization

✓ Use integer keys instead of text
✓ Remove unused columns and tables
✓ Use calculated columns sparingly
✓ Prefer measures over calculated columns
✓ Sort columns by numeric keys
✓ Reduce cardinality where possible
✓ Use VertiPaq compression

Report Optimization

• Limit visuals per page (8-10 maximum)
• Use bookmarks instead of multiple pages
• Avoid high-cardinality visuals
• Use Performance Analyzer to identify slow visuals
• Optimize custom visuals or replace with native ones
• Reduce filter interactions

Common DAX Patterns

Percentage of Total

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

Running Total

Running Total = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Same Period Last Year

SPLY = 
CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, YEAR)
)

Moving Average

3M Moving Avg = 
AVERAGEX(
    DATESINPERIOD(
        'Date'[Date],
        LASTDATE('Date'[Date]),
        -3,
        MONTH
    ),
    [Total Sales]
)

ABC Classification

ABC Class = 
VAR CurrentRank = [Product Rank]
VAR TotalProducts = COUNTROWS(ALL(Products))
RETURN
    SWITCH(
        TRUE(),
        CurrentRank <= TotalProducts * 0.2, "A",
        CurrentRank <= TotalProducts * 0.5, "B",
        "C"
    )

Publishing and Sharing

Workspace Roles

Deployment Process

1. Development: Create in Power BI Desktop
2. Testing: Publish to dev workspace
3. UAT: Move to test workspace
4. Production: Deploy to production workspace
5. Schedule: Set up refresh schedules

Gateway Types

• Personal: For individual use
• Enterprise: For organizational data sources
• VNet: For Azure services

Quick Tips

💡 Pro Tips:

• Press Ctrl + Alt + V to see DAX query behind visual
• Use ALT key to see visual interaction effects
• Create reusable measures in separate table
• Document complex DAX with comments (//)
• Use measure groups for organization
• Enable “Show items with no data” for complete analysis
• Use BLANK() instead of 0 for cleaner visuals
• Create calculation groups for time intelligence
• Use field parameters for dynamic visuals

Common Errors and Solutions

Resources

• Official Documentation: docs.microsoft.com/power-bi
• DAX Guide: dax.guide
• Community: community.powerbi.com
• Training: Microsoft Learn Power BI

Machine Learning Integration

• AutoML in Dataflows: In the dataflow editor, select Add ML model, choose the target column, and let AutoML train classification or regression models; review accuracy reports before enabling scheduled refresh.
• Azure ML Integration: Use the Azure Machine Learning connector to score datasets with deployed models; store the endpoint URL and key securely in parameters.
• AI Insights in Power BI Desktop: Apply Text Analytics and Vision functions (Sentiment, Key Phrase Extraction, Language Detection) from the Transform tab when connected to Premium or PPU capacity.
• Cognitive Services with Synapse Link: Push large datasets to Azure Synapse via Synapse Link and call Cognitive Services for richer feature engineering before re-importing results.
• Data Privacy Considerations: Set proper data privacy levels (Organizational vs. Public) to avoid query folding being disabled during ML transformations.

Python and R Scripting

• Enable Scripts: Go to File > Options and settings > Options > Security and enable Python and R scripting; point to local interpreters under Python scripting or R scripting.
• Recommended Packages: Keep pandas, numpy, scikit-learn, and matplotlib updated for Python; use tidyverse, forecast, and ggplot2 for R.
• Passing Data: Power BI sends the dataset to a dataframe named dataset; avoid returning more than 150k rows for smooth visuals.
• Sample Python Visualization

# dataset: Power BI dataframe
import pandas as pd
import seaborn as sns

numeric = dataset.select_dtypes(include="number")
corr = numeric.corr()
sns.heatmap(corr, annot=True, cmap="viridis")

• Deployment Tip: Install the same libraries on the Power BI gateway machine to refresh Python/R visuals in the service.

Advanced Analytics Visuals

• Tip: When using AI visuals on Premium/PPU, monitor capacity metrics to ensure they remain responsive on large datasets.

Time Series Forecasting

• Built-in Forecast: On a line chart, enable Forecast under the Analytics pane; set Confidence interval (default 95%), Forecast length, and Seasonality (auto-detect or manual).
• Preprocessing: Ensure the date column is continuous (no gaps), sort ascending, and aggregate to the appropriate grain (daily, weekly, monthly).
• Custom DAX Forecast

12M Forecast = 
VAR GrowthRate = 0.05
RETURN
    CALCULATE(
        [Total Sales] * (1 + GrowthRate),
        DATEADD('Date'[Date], 12, MONTH)
    )

• Evaluation: Compare forecast vs. actual with KPI visuals and add slicers to stress-test scenarios.
• Seasonal Models: For complex seasonality, export to Python/R scripts with prophet or forecast packages and re-import the results as a table.

Anomaly Detection and Alerts

• Anomaly Detection: Insert a line chart, enable Find anomalies in the Analytics pane, choose a sensitivity level, and review the explanation tooltips.
• Data Alerts: In the Power BI service, pin a visual to a dashboard and create an alert with thresholds and frequency (hourly/daily) to notify teams.
• Metrics Hub: Use Metrics (formerly Goals) to track KPIs, define status rules, and assign owners for automated follow-up.
• Monitoring: Combine anomaly flags with measure-based conditional formatting to highlight outliers directly in tables or matrices.
• Automation: Connect alerts to Power Automate flows for escalations, ticket creation, or Teams notifications.

Conclusion

This cheat sheet covers the essential Power BI concepts, DAX formulas, and best practices. Bookmark this page for quick reference when working on your Power BI projects. Remember that mastery comes with practice—experiment with different formulas and techniques to find what works best for your specific use cases.

Happy analyzing! 📊