Power BI Essentials: Basic DAX Concepts for a Reliable Model

DAX as the Calculation Layer (Measures First)

DAX (Data Analysis Expressions) is the calculation layer of Power BI. Your model stores clean, related tables; DAX defines how numbers are computed in reports. For a reliable model, prioritize dependable measures that are easy to read, validate, and reuse across visuals, rather than writing complex one-off formulas.

A practical mindset: build a small set of “core” measures (Sales, Orders, Customers, Margin, etc.), validate them, then compose additional measures from those building blocks. This approach reduces errors and keeps performance predictable.

Calculated Columns vs Measures (When to Use Which)

Calculated Columns

A calculated column is computed row-by-row and stored in the model. It behaves like a regular column: you can slice, group, and filter by it. Because it is stored, it increases model size and refresh time.

• Use calculated columns when you need a value per row for grouping or filtering (for example, a “Customer Segment” label, a “YearMonth” key, or a “IsLateDelivery” flag).
• Avoid calculated columns for aggregations that should respond to report filters (for example, “Total Sales” should be a measure, not a column).

Measures

A measure is calculated at query time based on the filter context of the visual (slicers, rows/columns in a matrix, cross-filtering). Measures are not stored as a column of values; they compute only what is needed for the current visual.

• Use measures for totals, ratios, KPIs, and anything that must respond to filters.
• Measures scale better because they do not add stored data to the model and are evaluated only for the requested context.

Rule of Thumb

• If you need to slice by it (a category/label per row), consider a calculated column.
• If you need to summarize it (a number that changes with filters), use a measure.

Measure Naming and Formatting Conventions

Consistent Naming

Adopt a convention that makes measures easy to find and interpret. One practical pattern is to use a prefix and clear business language.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

• Prefix: start measures with m_ (or another consistent prefix) to distinguish them from columns.
• Business-first names: m_Sales, m_Orders, m_Customers, m_Avg Order Value.
• Time variants: m_Sales (Selected Period), m_Sales (Prior Period) if you later add them.
• Avoid ambiguous names: prefer m_Sales Amount over m_Total.

Formatting Rules

• Currency: set to Currency with appropriate decimals (often 2) and symbol.
• Whole counts: set to Whole number (0 decimals).
• Ratios/percentages: set to Percentage with 1–2 decimals.
• Do not format in DAX using FORMAT() for numeric measures unless you specifically need text output; it turns numbers into text and can break sorting and aggregation.

Essential Functions and Reliable Patterns

SUM: Additive Totals

Use SUM when you have a numeric column that should add up (for example, line-level sales amount).

m_Sales = SUM ( Sales[SalesAmount] )

Practical check: place m_Sales in a Card visual, then slice by product, region, or date to confirm it responds as expected.

COUNTROWS: Counting Rows (Orders, Transactions, Records)

COUNTROWS counts rows in a table after filters are applied. It is reliable for counting facts like orders or transactions when each row represents one event.

m_Orders = COUNTROWS ( Sales )

If your fact table contains multiple rows per order (for example, one row per order line), COUNTROWS counts lines, not orders. In that case, count distinct order IDs instead (see DISTINCTCOUNT).

DISTINCTCOUNT: Counting Unique Entities

DISTINCTCOUNT counts unique values in a column under the current filter context. Use it for unique customers, unique orders (if order ID repeats across lines), unique products sold, etc.

m_Customers = DISTINCTCOUNT ( Sales[CustomerID] )

m_Orders (Distinct) = DISTINCTCOUNT ( Sales[OrderID] )

Practical tip: ensure the column used for DISTINCTCOUNT is stable and not blank-heavy; blanks count as a distinct value in some scenarios, so validate with a blank check if needed.

DIVIDE: Safe Division for Ratios

Use DIVIDE instead of the / operator to avoid errors and control what happens when the denominator is zero or blank.

m_Avg Order Value = DIVIDE ( [m_Sales], [m_Orders (Distinct)] )

You can provide an alternate result (for example, 0) when division is not possible.

m_Conversion Rate = DIVIDE ( [m_Orders (Distinct)], [m_Visits], 0 )

Building Measures Step-by-Step (A Repeatable Workflow)

Step 1: Start with Base Measures

Create a small set of base measures that map directly to business facts. Example set:

• m_Sales = SUM ( Sales[SalesAmount] )
• m_Cost = SUM ( Sales[CostAmount] )
• m_Orders (Distinct) = DISTINCTCOUNT ( Sales[OrderID] )
• m_Customers = DISTINCTCOUNT ( Sales[CustomerID] )

Step 2: Compose Derived Measures from Base Measures

Derived measures should reference base measures (not raw columns) whenever possible. This improves consistency and makes changes safer.

m_Gross Profit = [m_Sales] - [m_Cost]

m_Gross Margin % = DIVIDE ( [m_Gross Profit], [m_Sales] )

Step 3: Apply Formatting

Set formatting in the measure properties:

• m_Sales, m_Cost, m_Gross Profit: Currency
• m_Orders (Distinct), m_Customers: Whole number
• m_Gross Margin %: Percentage

Basic Time-Aware Thinking (Without Deep Time Intelligence)

You do not need advanced time intelligence to think “time-aware.” The key idea is that measures should behave correctly when a report is filtered to a period (month, quarter, year) and when it is not filtered at all.

Selected Period vs All-Time

Most measures naturally respect the selected period because visuals filter the model. For example, [m_Sales] will show sales for the selected month if the visual is sliced by month.

Sometimes you need a comparison to an “all-time” total (or “all selected” total). A simple, practical pattern is to create a measure that ignores date filters to provide a stable reference point. This is time-aware thinking because you are explicitly controlling whether the date filter applies.

m_Sales (All Dates) = CALCULATE ( [m_Sales], REMOVEFILTERS ( 'Date' ) )

Then you can compute the share of total relative to all dates:

m_Sales Share of All Time = DIVIDE ( [m_Sales], [m_Sales (All Dates)] )

Use this carefully: removing date filters changes the question you are answering. Validate with stakeholders that “all-time” is the intended comparison.

Running Totals: Keep It Conceptual

Running totals are time-aware because they depend on an ordering of dates. If you have a proper Date table, a running total can be built, but treat it as an optional pattern and validate thoroughly because it is sensitive to date filtering and granularity.

m_Sales (Running) = CALCULATE ( [m_Sales], FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Practical guidance: only use a running total on visuals that have a clear date axis (day, month, etc.). If you place it in a card without a date context, it will typically return the total up to the latest date in the current filter context.

Testing Measures Against Known Totals (A Simple Validation Method)

Reliable measures are tested, not assumed correct. Use a repeatable validation method whenever you create or modify measures.

1) Create a “Test Page” with Control Visuals

• A Card for each base measure: m_Sales, m_Orders (Distinct), m_Customers.
• A table visual showing key identifiers and amounts (for example, OrderID, SalesAmount) for spot checks.
• Slicers for Date, Product, Region (whatever your model uses most).

2) Compare to a Known Total

Pick a small, well-defined slice you can verify externally (for example, one month and one region). Then compare:

• Power BI result: the card value for m_Sales.
• Known total: a trusted export, finance report, or a manual sum from a limited dataset extract.

Keep the slice small enough that you can manually reconcile differences.

3) Add Diagnostic Measures When Numbers Don’t Match

Create temporary measures to isolate issues. Examples:

• Check row count vs distinct order count to detect duplicated lines:

m_Sales Rows = COUNTROWS ( Sales )

m_OrderIDs Distinct = DISTINCTCOUNT ( Sales[OrderID] )

• Check for blanks in key columns (blanks can affect distinct counts and relationships):

m_Blank CustomerIDs = CALCULATE ( COUNTROWS ( Sales ), Sales[CustomerID] = BLANK () )

After resolving the issue, remove or hide diagnostic measures to keep the model tidy.

4) Validate Additivity Across Levels

Test that totals behave correctly when you change granularity:

• Put m_Sales in a matrix by Month, then by Day, then by Product.
• Confirm that subtotals and grand totals align with expectations.
• If a measure is non-additive (like an average or ratio), confirm that you understand how totals are computed and that the behavior matches the business definition.