Best Practices

Best Practices Skill

This skill helps apply Power BI best practices, Best Practice Analyzer (BPA) rules, and enterprise standards to semantic models and reports.

When to Use This Skill

• Validating model quality against BPA rules
• Applying naming conventions
• Optimizing model performance
• Adding documentation and descriptions
• Enforcing enterprise governance patterns
• Reviewing DAX measures for quality

Best Practice Analyzer (BPA) Overview

BPA rules are organized into categories:

1. Performance - Optimization and efficiency rules
2. DAX Expressions - Measure and calculation quality
3. Formatting - Naming and organization standards
4. Maintenance - Housekeeping and cleanup rules
5. Error Prevention - Validation and safety rules

Quick Validation Checklist

Model Structure

• All tables have descriptions
• Relationships are one-to-many (no bi-directional unless required)
• Key columns are marked as hidden
• Date table is marked as date table
• No unused columns or tables

DAX Measures

• All measures have descriptions (/// comments)
• DIVIDE() used instead of / operator
• Variables used for repeated expressions
• Measures organized in display folders
• No implicit measures (summarizations)

Naming Conventions

• Tables: PascalCase, singular nouns
• Columns: PascalCase with spaces allowed
• Measures: Business-friendly names
• No special characters or reserved words

Performance

• No calculated columns where measures work
• Integer keys instead of string keys
• Narrow tables preferred (fewer columns)
• Appropriate data types used

Performance Rules

HIGH IMPACT

Avoid bi-directional relationships

Bi-directional relationships cause performance overhead and can create ambiguity.

Instead of:

relationship {{guid}}
	fromColumn: TableA.'Key'
	toColumn: TableB.'Key'
	crossFilteringBehavior: bothDirections

Do this:

relationship {{guid}}
	fromColumn: TableA.'Key'
	toColumn: TableB.'Key'

// Use DAX CROSSFILTER() in measures when needed
measure 'Filtered Value' =
	CALCULATE(
	    [Base Measure],
	    CROSSFILTER(TableA[Key], TableB[Key], Both)
	)

Use integer keys instead of strings

Integer comparisons are faster than string comparisons.

Instead of:

column 'Product Key'
	dataType: string

Do this:

column 'Product Key'
	dataType: int64

Minimize calculated columns

Calculated columns consume memory and slow refresh. Use measures when possible.

Instead of calculated column:

column 'Profit Margin' =
	DIVIDE(Sales[Profit], Sales[Revenue], 0)

Use a measure:

measure 'Profit Margin' =
	DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)

MEDIUM IMPACT

Remove unused columns

Unused columns waste memory. Delete columns not used in:

• Relationships
• Measures
• Visuals
• Filters

Avoid high cardinality columns

Columns with many unique values increase model size. Consider:

• Grouping/binning high cardinality data
• Moving detail columns to a separate table
• Using calculated columns only when necessary

Use appropriate data types

DAX Rules

REQUIRED

Use DIVIDE instead of /

Bad:

Margin = Sales[Profit] / Sales[Revenue]

Good:

Margin = DIVIDE(Sales[Profit], Sales[Revenue], 0)

Use VAR for repeated expressions

Bad:

Growth % =
(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date])))
/ CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

Good:

Growth % =
VAR CurrentSales = SUM(Sales[Amount])
VAR PriorSales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))
RETURN
    DIVIDE(CurrentSales - PriorSales, PriorSales)

Add descriptions to measures

/// Calculates total sales revenue
/// Excludes returns and cancellations
measure 'Total Sales' =
	SUM(Sales[Amount])

RECOMMENDED

Avoid IFERROR with aggregations

IFERROR masks data quality issues.

Bad:

Total = IFERROR(SUM(Sales[Amount]), 0)

Good:

Total = SUM(Sales[Amount])

// Handle blanks explicitly if needed
Total = COALESCE(SUM(Sales[Amount]), 0)

Use REMOVEFILTERS instead of ALL

REMOVEFILTERS is more explicit about intent.

Prefer:

All Sales = CALCULATE([Total Sales], REMOVEFILTERS(Products))

Avoid nested CALCULATE

Deeply nested CALCULATE is hard to maintain.

Bad:

Measure =
CALCULATE(
    CALCULATE(
        CALCULATE([Base], Filter1),
        Filter2
    ),
    Filter3
)

Good:

Measure =
CALCULATE(
    [Base],
    Filter1,
    Filter2,
    Filter3
)

Formatting Rules

Table Naming

Column Naming

Measure Naming

Display Folders

Organize measures into logical folders:

├── Core Metrics
│   ├── Total Sales
│   ├── Total Cost
│   └── Total Profit
├── Time Intelligence
│   ├── Sales YTD
│   ├── Sales PY
│   └── Sales YoY %
├── Percentages
│   ├── Gross Margin %
│   └── % of Total
└── KPIs
    ├── Target
    └── Achievement

Maintenance Rules

Remove unused objects

Delete these if not used:

• Tables with no relationships or references
• Columns not in visuals, measures, or relationships
• Measures not in visuals or other measures
• Hidden columns not used in calculations

Document everything

Every object should have a description:

/// Customer dimension table
/// Contains customer demographics and segmentation
table Customers
	lineageTag: ...

	/// Unique identifier for each customer
	column 'Customer ID'
		...

	/// Customer's full name (First + Last)
	column 'Customer Name'
		...

Use consistent formatting

• TMDL: Use tabs for indentation
• DAX: Use 4 spaces for indentation
• Follow SQLBI DAX formatting conventions

Error Prevention Rules

Validate relationships

Before creating relationships, verify:

1. "To" column contains unique values
2. Data types match exactly
3. Orphan handling is considered
4. Filter direction is appropriate

Validate measure references

Before using measures in visuals:

1. Test with simple filters
2. Verify time intelligence with date table
3. Check for circular references
4. Validate with EVALUATE in DAX Studio

Test edge cases

Consider these scenarios:

• Empty filter context (grand total)
• Single value context (card visual)
• Multiple selections
• No data (blanks)

Enterprise Governance Patterns

Row-Level Security (RLS)

Define RLS roles in TMDL:

role SalesRep
	modelPermission: read

	tablePermission Sales = 'Sales'[Sales Rep] = USERPRINCIPALNAME()

Data Sensitivity

Mark sensitive columns:

column 'SSN'
	dataType: string
	isHidden
	// Consider not including in model

Audit Trail

Document model changes:

• Use Git commit messages
• Add change log in model description
• Track measure versions with comments

Data Source Governance

Credential Security

NEVER store credentials in source files:

/// BAD: Hardcoded connection string
expression ConnectionString = "Server=prod.database.com;User=admin;Password=secret123"

/// GOOD: Use parameters without credentials
expression ServerName = "prod.database.com" meta [IsParameterQuery=true, Type="Text"]

Credentials should be:

• Configured in Power BI Service after publish
• Managed via gateway for on-premises sources
• Stored in Azure Key Vault for automated scenarios

Connection Parameters

Use parameters for environment-specific values:

/// Server name parameter (update per environment)
expression ServerName = "dev-server.database.windows.net" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

/// Database name parameter
expression DatabaseName = "SalesDB_Dev" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Gateway Configuration

For on-premises or VNet data sources:

Refresh Considerations

Import Mode:

• Data copied into model at refresh
• Schedule refreshes in Power BI Service
• Consider incremental refresh for large tables

DirectQuery:

• Live queries to source
• No refresh needed
• Source must be always available
• Performance dependent on source

Hybrid (Composite):

• Mix of Import and DirectQuery
• Use Import for dimensions, DirectQuery for facts
• Requires careful relationship configuration

Incremental Refresh Setup

1. Create required parameters in Power Query:

RangeStart = #datetime(2020, 1, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime"]
RangeEnd = #datetime(2025, 12, 31, 23, 59, 59) meta [IsParameterQuery=true, Type="DateTime"]

2. Filter query with these parameters:

Table.SelectRows(Source, each [Date] >= RangeStart and [Date] < RangeEnd)

3. Configure refresh policy in Power BI Desktop before publish

Data Source Checklist

• No credentials in source files
• Connection strings use parameters
• Gateway configured for on-premises sources
• Query folding verified for large tables
• Incremental refresh configured where appropriate
• Timeout settings appropriate for data volume
• Error handling in Power Query transformations
• Data source documented in model description

Query Folding Validation

Ensure transformations push to source database:

Check folding: Right-click step > "View Native Query"

Validation Workflow

1. After creating tables

   • Verify naming conventions
   • Check data types
   • Add descriptions

2. After adding relationships

   • Confirm cardinality
   • Verify no bi-directional unless needed
   • Check filter flow

3. After writing measures

   • Run BPA checks
   • Verify DIVIDE usage
   • Add descriptions
   • Test calculations

4. Before deployment

   • Remove unused objects
   • Verify all descriptions
   • Check performance
   • Test with sample data

Integration with Other Skills

After using other skills, apply best practices:

BPA Rule Reference

See bpa-rules.md for complete rule list.

Naming Convention Reference

See naming-conventions.md for detailed naming guidelines.