Power BI Skill
Master Microsoft Power BI development including DAX formulas, Power Query transformations, data modeling, and report optimization.
Quick Start (5 minutes)
// 3 essential DAX patterns:
// 1. Basic measure
Total Sales = SUM(Sales[Amount])
// 2. Time intelligence
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
// 3. Safe division
Profit Margin = DIVIDE([Profit], [Revenue], 0)
Core Concepts
DAX Evaluation Context
┌─────────────────────────────────────────────────────────────┐
│ CONTEXT IN DAX │
├─────────────────────────────────────────────────────────────┤
│ │
│ ROW CONTEXT FILTER CONTEXT │
│ ──────────── ────────────── │
│ Created by: Created by: │
│ • Calculated columns • Slicers │
│ • Iterators (SUMX, etc.) • Filters │
│ • Row-level security • Rows/Columns in visual │
│ • CALCULATE │
│ Accesses: Accesses: │
│ • Current row values • Filtered table │
│ │
│ CALCULATE triggers CONTEXT TRANSITION │
│ (Row context → Filter context) │
│ │
└─────────────────────────────────────────────────────────────┘
Measure vs Calculated Column
| Aspect | Measure | Calculated Column | |--------|---------|-------------------| | Calculated | Query time | Refresh time | | Storage | None | In model | | Context | Filter | Row | | Use when | Aggregations | Row-level values | | Performance | Better for large data | Impacts model size |
CALCULATE Deep Dive
// CALCULATE structure
CALCULATE(
<expression>, // What to calculate
<filter1>, // Modify filter context
<filter2>, // Additional filters
...
)
// CALCULATE actions:
// 1. Context transition (row → filter)
// 2. Apply filters (modify filter context)
// 3. Evaluate expression (in new context)
// Examples:
Sales All Products = CALCULATE([Total Sales], ALL(Products))
Sales Current Region = CALCULATE([Total Sales], REMOVEFILTERS())
Sales Top Category = CALCULATE([Total Sales], TOPN(1, Categories, [Total Sales]))
Code Examples
Time Intelligence Measures
// Year-to-Date
Sales YTD =
TOTALYTD(
[Total Sales],
'Date'[Date]
)
// Prior Year
Sales PY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
)
// Year-over-Year Growth
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = [Sales PY]
RETURN
DIVIDE(
CurrentSales - PriorSales,
PriorSales,
BLANK()
)
// Rolling 12 Months
Sales Rolling 12M =
CALCULATE(
[Total Sales],
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
)
// Month-to-Date
Sales MTD =
TOTALMTD(
[Total Sales],
'Date'[Date]
)
// Previous Month
Sales PM =
CALCULATE(
[Total Sales],
PREVIOUSMONTH('Date'[Date])
)
Dynamic Segmentation
// Customer Segment based on Sales
Customer Segment =
VAR CustomerSales = [Total Sales]
RETURN
SWITCH(
TRUE(),
CustomerSales >= 100000, "Platinum",
CustomerSales >= 50000, "Gold",
CustomerSales >= 10000, "Silver",
"Bronze"
)
// ABC Analysis (Pareto)
ABC Category =
VAR CurrentProduct = SELECTEDVALUE(Products[ProductName])
VAR AllProducts =
ADDCOLUMNS(
ALLSELECTED(Products[ProductName]),
"@Sales", [Total Sales]
)
VAR RankedProducts =
ADDCOLUMNS(
AllProducts,
"@Rank", RANKX(AllProducts, [@Sales],, DESC, DENSE),
"@CumSales", SUMX(
FILTER(AllProducts, RANKX(AllProducts, [@Sales],, DESC, DENSE) <= RANKX(AllProducts, [@Sales],, DESC, DENSE)),
[@Sales]
)
)
VAR TotalSales = SUMX(AllProducts, [@Sales])
VAR CumPct =
MAXX(
FILTER(RankedProducts, [Products[ProductName]] = CurrentProduct),
[@CumSales] / TotalSales
)
RETURN
SWITCH(
TRUE(),
CumPct <= 0.8, "A",
CumPct <= 0.95, "B",
"C"
)
Power Query M Patterns
// Incremental Refresh Setup
let
Source = Sql.Database("server", "database"),
// RangeStart and RangeEnd are incremental refresh parameters
FilteredRows = Table.SelectRows(
Source,
each [ModifiedDate] >= RangeStart and [ModifiedDate] < RangeEnd
)
in
FilteredRows
// Dynamic Column Pivoting
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(
Source,
{"Product"},
"Attribute",
"Value"
)
in
Unpivoted
// Error Handling
let
Source = try Sql.Database("server", "db")
otherwise #table({"Status"}, {{"Connection Failed"}}),
Result = if Table.RowCount(Source) > 0
then Source
else error "No data returned"
in
Result
// Custom Function
let
CalculateMargin = (Revenue as number, Cost as number) as number =>
if Revenue = 0 then 0 else (Revenue - Cost) / Revenue
in
CalculateMargin
Best Practices
DAX Formatting Standard
// Format: Sentence case with units
Revenue per Customer ($) =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR CustomerCount = DISTINCTCOUNT(Sales[CustomerID])
RETURN
DIVIDE(
TotalRevenue,
CustomerCount,
0 // Alternate result for division by zero
)
Model Optimization Checklist
□ Remove unused columns
□ Disable Auto Date/Time
□ Use star schema (avoid snowflake)
□ Create a proper Date table (mark as date table)
□ Use integer keys for relationships
□ Avoid bidirectional filtering (use only when necessary)
□ Set column data types correctly
□ Hide foreign key columns from report view
□ Group measures in display folders
□ Add descriptions to measures
Performance Anti-Patterns
// ❌ AVOID: FILTER with large tables
Sales Filtered =
CALCULATE([Total Sales], FILTER(ALL(Products), Products[Category] = "A"))
// ✓ BETTER: Direct filter
Sales Filtered =
CALCULATE([Total Sales], Products[Category] = "A")
// ❌ AVOID: SUMX over entire table
Total Margin =
SUMX(Sales, Sales[Revenue] - Sales[Cost])
// ✓ BETTER: Pre-calculated column or simpler aggregate
Total Margin =
SUM(Sales[Revenue]) - SUM(Sales[Cost])
Common Patterns
Conditional Formatting Values
// Return values for conditional formatting
Status Icon =
VAR Actual = [Total Sales]
VAR Target = [Target]
VAR Variance = DIVIDE(Actual - Target, Target)
RETURN
SWITCH(
TRUE(),
Variance >= 0.1, "▲", // Green up
Variance >= 0, "●", // Yellow neutral
"▼" // Red down
)
// Background color value
KPI Color =
VAR Variance = [Variance %]
RETURN
SWITCH(
TRUE(),
Variance >= 0.1, "#22C55E",
Variance >= 0, "#F59E0B",
"#EF4444"
)
Dynamic Top N
// With parameter table
Top N Sales =
VAR TopNValue = SELECTEDVALUE('Parameters'[TopN], 10)
VAR RankedProducts =
ADDCOLUMNS(
SUMMARIZE(Sales, Products[ProductName]),
"@Sales", [Total Sales],
"@Rank", RANKX(ALL(Products[ProductName]), [Total Sales],, DESC)
)
RETURN
SUMX(
FILTER(RankedProducts, [@Rank] <= TopNValue),
[@Sales]
)
Disconnected Table Pattern
// Create slicer options without relationships
// DateGranularity table: Day, Week, Month, Quarter, Year
Selected Granularity Label =
VAR Selection = SELECTEDVALUE('DateGranularity'[Granularity], "Month")
RETURN
SWITCH(
Selection,
"Day", FORMAT([Date], "MMM DD, YYYY"),
"Week", "Week " & WEEKNUM([Date]),
"Month", FORMAT([Date], "MMM YYYY"),
"Quarter", "Q" & QUARTER([Date]) & " " & YEAR([Date]),
"Year", FORMAT([Date], "YYYY")
)
Retry Logic
const refreshDataset = async (datasetId: string) => {
const retryConfig = {
maxRetries: 3,
backoffMs: [60000, 120000, 300000] // 1min, 2min, 5min
};
for (let attempt = 0; attempt <= retryConfig.maxRetries; attempt++) {
try {
return await powerBIService.refreshDataset(datasetId);
} catch (error) {
if (attempt === retryConfig.maxRetries) throw error;
if (error.code === 'REFRESH_ALREADY_IN_PROGRESS') {
await waitForRefreshComplete(datasetId);
return;
}
await sleep(retryConfig.backoffMs[attempt]);
}
}
};
Logging Hooks
const powerBIHooks = {
onMeasureEvaluate: (measureName, duration) => {
console.log(`[DAX] ${measureName} evaluated in ${duration}ms`);
if (duration > 1000) {
console.warn(`[DAX] Slow measure: ${measureName}`);
}
},
onRefreshStart: (datasetId) => {
console.log(`[REFRESH] Starting: ${datasetId}`);
},
onRefreshComplete: (datasetId, status, duration) => {
console.log(`[REFRESH] ${datasetId}: ${status} in ${duration}s`);
}
};
Unit Test Template
describe('Power BI Skill', () => {
describe('DAX Measures', () => {
it('should calculate YoY correctly', async () => {
const result = await evaluateMeasure('YoY Growth %', {
'Date[Year]': 2024
});
expect(result).toBeCloseTo(0.15, 2);
});
it('should handle division by zero', async () => {
const result = await evaluateMeasure('Profit Margin', {
'Sales[Amount]': 0
});
expect(result).toBe(0); // Not error
});
});
describe('Data Model', () => {
it('should have star schema structure', () => {
const relationships = getModelRelationships();
const factsToFacts = relationships.filter(
r => r.fromTable.startsWith('Fact_') && r.toTable.startsWith('Fact_')
);
expect(factsToFacts.length).toBe(0);
});
});
});
Troubleshooting
| Issue | Cause | Solution | |-------|-------|----------| | Circular dependency | Self-referencing | Use VAR or EARLIER() | | Wrong totals | Filter context leakage | Use ALL() or REMOVEFILTERS() | | Slow visuals | Complex iterator | Use SUMMARIZECOLUMNS | | Blank results | Missing relationship | Check model relationships | | #ERROR | Division by zero | Use DIVIDE() function |
Resources
- SQLBI: DAX Patterns and best practices
- DAX Guide: Complete function reference
- Power Query M Reference: Microsoft documentation
- DAX Studio: Performance analysis tool
Version History
| Version | Date | Changes | |---------|------|---------| | 1.0.0 | 2024-01 | Initial release | | 2.0.0 | 2025-01 | Production-grade with optimization |