This post is part of a series of excerpts from my forthcoming book “Microsoft Power BI Data Analyst Associate Study Guide” from O’Reilly Press; from Part 2: “Using Power BI in the Real World“. As of this post, the online book is available as an early release through the O’Reilly subscription service and scheduled to be delivered through booksellers like Amazon early this year.

Semantic Model Design Checklist

Use this checklist to make sure your model is complete, free of clutter and designed according to best practice standards:

Naming and Organization

• Make sure every object uses business friendly names
• Add measures to a common container table, such as the _Measures table
• Organize measures into display folders

Field Management

• Set field type categorization
• Hide key columns and utility columns
• Hide all numeric columns used to define metrics (implicit measures) and then create explicit measures
• Set all visible numeric columns to summarize or not summarize correctly

Measure Development

• Create base and simple measures (e.g. COUNT() & SUM()) as basis for complex measures
• Create base measures to replace all default implicit measure columns
• Format all measures with thousand separator and decimal places
• Format all measure values
• Create row count measures for every table in the model to support data validation and simple unit testing
• When applicable, use calculation groups to apply standard measure variations such as time intelligence functions

DAX Code Standards

• Format all DAX code according to DAXFormatter.com standards
• Add code annotations to explain DAX code logic in all cases except simple, obvious calculations
• Use DAX variables to break down calculation logic

Model Structure

• Create multiple layouts (model diagram layouts to organize tables for readability & presentation)
• Mark date dimensions (either imported or generated in Power Query) as a date table
• Model tables using the star schema pattern
• Whenever possible, model tables using single-direction, one-to-many relationships
• Avoid bidirectional relationship filters when not necessary
• Avoid using many-to-many relationships
• Hide “true” fact table(s) that only have key and numeric columns
• Create hierarchies to support drill-down and visual axis groupings

Validation and Optimization

• Use the Tabular Editor Best Practice Analyzer to find optimization issues and recommendations
• Iterate on model design to add supporting dimensional tables & relationships, rather than working-around functional gaps using complex DAX

Project and Solution Management Strategy

• CI/CD, co-development and version control
• Deployment strategy
• Quality, validation, testing and sign-off
• Governance and certification

Checklist Details

Use Tabular Editor or TMDL View to make property changes for multiple objects. Note that the terms “column” and “field” are often used interchangeably, however “column” is most often used to refer to database table columns and “field” is most often used to refer to the fields (e.g. “columns”) of semantic model tables.

Make sure every object uses business friendly names

In addition to making data fast and efficient for reporting, an important purpose for a semantic model is to present business data in a consumable, user-friendly format for report designers and business data consumers. The goal is for every object in the model that is visible to report designers and self-service report users to be named so it is simple and self-describing, in plain, non-technical language. Internal key and utility fields with technical naming conventions do not need to be renamed since they are not exposed to users for reporting.

Hide key columns and utility columns

Hide all fields that don’t have a business purpose for reporting. This includes all key fields that are used to define relationships, fields used for sorting other fields, and fields used to create explicit measures.

Hide all numeric columns used to define metrics (implicit measures) and then create explicit measures

All numeric fields that are used for aggregate reporting should be hidden and then explicit measures should be created for reporting purposes. Power BI automatically sets the Summarization for numeric fields and displays them in the field list with a sigma icon. Set every one of these fields to be hidden. Replacement explicit measures will need to use a variation of the implicit field name. For example, the “SalesAmount” field may be used to define a measure named “Sales Amount”, but you cannot use the “Sales Amount” field to create a measure named “Sales Amount”. Use a naming convention that identify the business purpose in the first few characters and aggregation or time series variations at the end of the name, so measures are naturally sorted and grouped. Examples:

• Gross Profit
• Gross Profit MTD
• Gross Profit PY
• Gross Profit PY %
• Gross Profit Margin %
• Net Profit
• Net Profit MTD
• Net Profit PY
• Net Profit PY %
• Net Profit Margin %

Set field type categorization

Fields used to visualize values for any of these types should be set with the proper Data Category to ensure proper visualization and grouping:

• Address
• City
• Continent
• Country/Region
• County
• Latitude
• Longitude
• Place
• Postal Code
• State or Province
• Web URL
• Image URL
• Barcode

Create base and simple measures as basis for complex measures

Create simple measures to replace all implicit columns with aggregate expressions such as SUM(), AVERAGE() and COUNT(), etc. Reference base measures in advanced measures rather than repeating aggregate expressions.

Set all visible numeric columns to summarize or not summarize correctly

For all numeric columns that are not hidden nor intended to be aggregated or summarized, set the Summarization property to Don’t Aggregate. For example, if the numeric type Year or Month Number fields in the Date table are left with default settings, add them to a visual will inappropriately summarize the values.

Add measures to a common container table

In simple semantic models with only one fact table, measures can be defined within the fact table but this is confusing in models with more than one fact table. As a rules, create a container table to contain all measures organized using display folders. A common approach is to create an empty table named: _Measures which always sorts to the top of the field list.

Format all measures with thousand separator and decimal places

For every measure, set an appropriate FormatString with a thousand separator and specified number of decimal positions.

Organize measures into display folders

Create row count measures for every table

In the model to support data validation and simple unit testing. Table row counts are a convenient initial data validation test and method to ensure data is loaded as expected.

When applicable, use calculation groups to apply standard measure variations such as time intelligence functions

Format all DAX code according to DAXFormatter.com standards

Example:

Flight Count PY =

    CALCULATE( [Flight Count],

        SAMEPERIODLASTYEAR( ‘Flight Date'[Flight Date] ) 

    )

Add code annotations to explain DAX code logic in all cases except simple, obvious calculations

Power Query/M:

let
    /*    
    Customer Dimension – Gold Layer
        Source: sales.Customer
        Excludes test customers (ID < 1000)
    /

    // Connect to source
    Source = Sql.Database(“Server”, “DB”),                       
    CustomerTable = Source{[Schema=”sales”,Item=”Customer”]}[Data],

// Remove test data
FilteredRows = Table.SelectRows(

        CustomerTable,
        each [CustomerID] >= 1000                                
    ),

    // Set data types
    ChangedType = Table.TransformColumnTypes(
        FilteredRows,
        {{“CustomerID”, Int64.Type}, {“Amount”, Currency.Type}}  
    )
in
    ChangedType

DAX:

Net Revenue =
/*
    =============================================================
    Calculate net revenue after returns and discounts
    Business Rules:
    – Returns are already negative in fact table
    – Discounts applied at line level
    – Exclude cancelled orders (Status <> ‘Cancelled’)
    Version History:
    v1.0 – 2024-12-01 – Initial version
    v1.1 – 2025-01-02 – Added cancelled order filter
    =============================================================
*/

// Get gross sales:
VAR GrossSales =
    CALCULATE(
        [Sales],
        ‘Orders'[Status] <> “Cancelled”  // Filter active orders only
    )

VAR Returns = [Total Returns]           // Already negative values)

VAR Discounts = [Total Discounts] // Calculate total discounts given

RETURN
    GrossSales + Returns – Discounts  // Net: Returns already negative

Use DAX variables to break down calculation logic

The prevailing convention for variable naming in DAX is driven by simplicity and flexibility. Objects in a semantic model must be unique. To avoid duplicating table names, prefix variables with an underscore character followed by a descriptive name in Pascal case. Here is a simple example:

Combined Sales Qty % Same Per LY =

VAR _ThisQty         = [Combined Sales Qty]

VAR _LastYearQty     = [Combined Sales Qty Same Per LY]

VAR _PercentDiff     = DIVIDE(_ThisQty, _ LastYearQty )

RETURN

    _PercentDiff

Most importantly, the best standard is the one that you and members of your team use consistently. With or without the underscore, use variable names that are self-describing and simple, mixed case and no spaces.

Create multiple layouts

In the data model diagram view, create one layout per fact table, including all related tables (typically dimension and bridge tables). Organize the table in the layouts for readability & presentation.

Mark date dimensions (either imported or generated in Power Query) as a date table

Date tables should always use a Date type field as the key. Do not use DateTime or Integer.

Model tables using the star schema pattern

The key to building a scalable, high-performing data model to support interactive, analytic reporting scenarios, is to establish a foundational dimensional model. There are bound to be exceptions in a real, production solution and that’s fine. Follow the rules first and then figure out where you need to break them in to support exceptional requirements.

Whenever possible, model tables using single-direction, one-to-many relationships

Create secondary dimension tables (e.g. Snowflake schema), many-to-many relationships and dimension bridging tables only when needed to satisfy specific reporting requirements on an exceptional basis.

Avoid bidirectional relationship filters when not necessary

With increased data volume and high cardinality, bidirectional relationships are will slow report performance and should be avoided when unnecessary. Use only to satisfy specific report requirements when no alternative is possible. Bi-directional relationships can be avoided using visual and slicer filtering techniques.

Avoid using many-to-many relationships

In rare cases to meet specific M2M requirements, use a bridge table and bidirectional relationship rather than the many-to-many relationship type.

Hide “true” fact table(s) that only have key and numeric columns

In an optimal star schema model, fact tables contain only foreign key columns and numeric columns used to define base measures. There are legitimate exceptions to this rule but consider the trade-off implications of simplicity vs over-engineering a solution (for example, defining separate dimensions for fields with only a few values). When possible, hide the entire fact table and use a common container table for all measures, using display folders to organize measures. Hiding the entire table will in-effect hide all contained columns.

Create hierarchies to support drill-down and visual axis groupings

To support drill-down navigation on grouped visual axes (like a column chart or matrix), group fields to define natural hierarchies. Name hierarchies describe the business purpose for the attribute collection. Create multiple hierarchies with the same fields to support common reporting scenarios  (e,g, Y-M-D, Y-Q-M-D, FY-FP-D, Region Geography, etc.) Simple hierarchies are a group of fields from the same dimension table arranged in hierarchal order. Complex hierarchies might consist of fields from related tables that would be brought into a single table within the semantic model to form a hierarchy (like Product Category, Subcategory, Product).

Use the Tabular Editor Best Practice Analyzer to find optimization issues and recommendations

Export model metadata to VPAX file per https://docs.microsoft.com/en-us/power-bi/guidance/star-schema.

Project and Solution Management Strategy

There is no one-size-fits-all strategy for managing Power BI projects because BI projects span a vast range of use cases, organizational discipline and readiness. The most important consideration is that you adopt a strategy that works for you and your organization in the following areas, according to you your organization and environment scale which are defined as:

• Small org – Informal BI solutions, single BI developer, without formal data governance policies, low data volumes.
• Medium – Small collaborative BI team, IT managed data & BI environment, informal governance approval, simple DevOps & deployment stages (e.g. DEV & PROD).
• Enterprise – IT managed data warehouse or data lake environments, medallion architecture, separate data engineering & BI deployments, formal CI/CD & DevOps process, separate deployment environments (e.g. DEV, TEST, PROD), data governance policies.

CI/CD, co-development and version control

• Small org – Manage master copies of PBIX files in shared file system folders backed by OneDrive or SharePoint with versioning. Deploy semantic mode and reports separately to create “thin reports” & common published models. Use query parameters to control data volume on desktop for development and in the service to populate large tables.
  Perform code reviews with at least a second team member, with a backup/DR and ownership transition plan. For critical models & reports, consider using Power BI Project (.PBIP) with GitHub to manage master project files.
  Use Power BI Desktop for development and only use the browser-based model & report editor to update critical assets if workspace is synced with Git repo.
• Medium – Save all models and reports as PBIP project format synced with a GitHub or Azure DevOps repository. Use VS Code to manage changes and versions. Using branching, pull requests and merging to manage versions and deployment. Sync workspace with Git (GitHub or Azure DevOps).
  Use branches to manage feature sets & co-development.
  Tools: Power BI Desktop, Tabular Editor, DAX Studio, browser-based model & report editor.
• Enterprise – Manage all semantic models and reports in PBIP projects with Git integration (synced with developer desktop and DEV workspace). Use VS Code to push feature & developer branches using pull requests & merges.
  

Deployment strategy

• Small – Publish to separate DEV and Production workspaces. Use query parameters to control data volume on desktop for development and in the service to populate large tables.
• Medium – Sync workspace with Git (GitHub or Azure DevOps).
  Use deployment pipeline to separate DEV from PROD workspace or coordinate manual deployment to separate DEV and PROD workspaces.
  Package and deploy view-only organizational reports and models using workspace apps.
• Enterprise – Use deployment pipelines to manage separate DEV, TEST & PROD workspaces. Coordinate CI/CD with overall solution organizational DevOps strategy depending on tool preference (e.g. GitHub, Az DevOps) and promotion method (e.g. manual “click ops” deployment, DevOps pipeline actions, API script, etc.)
  Consider using native Fabric deployment support features such as Fabric Deployment Pipeline rules and Data Factory pipeline variable libraries. Ensure that any non-native DevOps orchestration approach is tested and supported.

Quality, validation, testing and sign-off

Governance and certification

Separate “certified” from “non-certified” reports and models using asset endorsements as part of the organization’s data governance strategy. Define criteria, ownership and process for certification. Provide separate workspaces for ad hoc and non-certified model and report development, separate from IT-managed Power BI projects.