Top 5 Useful DAX Functions For Beginners

Top 5 DAX Functions for Beginners in Power BI

• The five essential DAX functions: CALCULATE, SUMX, FILTER, RELATED, ALL.
• Next tier after mastering the five: DIVIDE, VALUES/DISTINCT, SWITCH, DATEADD, SAMEPERIODLASTYEAR, TOTALYTD, RANKX, SELECTEDVALUE.
• Best practice: use measures for aggregations and KPIs; use calculated columns only for row-level values needed for sorting, filtering, or slicing.
• DAX operates on tables and columns within filter contexts — fundamentally different from Excel's cell-by-cell model.
• EPC Group founder Errin O'Connor authored four bestselling Microsoft Press books including comprehensive guides to DAX and Power BI enterprise deployments.

1. CALCULATE: The Most Powerful DAX Function

CALCULATE is the most important DAX function to learn. It evaluates an expression in a modified filter context. This lets you override, add, or remove filters on your data. Nearly every advanced DAX pattern builds on CALCULATE.

Common uses:

• Basic filter: Sales in West = CALCULATE([Total Sales], Region[Area] = "West") — calculates total sales filtered to the West region, regardless of any slicer selections on Region
• Multiple filters: Premium West Sales = CALCULATE([Total Sales], Region[Area] = "West", Product[Tier] = "Premium") — applies both filters simultaneously (AND logic)
• Remove filters: Total All Sales = CALCULATE([Total Sales], ALL(Products)) — removes all filters on the Products table, giving the grand total regardless of product selections

CALCULATE is the gateway to time intelligence, percentage-of-total calculations, year-over-year comparisons, and virtually every dynamic calculation in Power BI.

2. SUMX: Row-by-Row Iteration

SUMX is an iterator function. It evaluates an expression for each row in a table, then sums the results. Use SUMX when you need to calculate a value at the row level before aggregating — for example, computing line item totals (quantity times price) before summing all lines.

Common uses:

• Line total calculation: Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — multiplies quantity by price for each row, then sums all line totals
• Weighted average: Weighted Avg Price = DIVIDE(SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]), SUM(Sales[Quantity])) — calculates a true weighted average price
• Conditional sum: High Value Sales = SUMX(FILTER(Sales, Sales[Amount] > 10000), Sales[Amount]) — sums only sales exceeding $10,000

SUMX vs SUM: SUM simply aggregates a single column. SUMX evaluates multi-column expressions and applies row-level logic before aggregating. Prefer SUM when summing a single column — it performs faster. Use SUMX when row-level logic is required.

3. FILTER: Dynamic Table Filtering

FILTER returns a table containing only the rows that meet a specified condition. It is commonly used as an argument within CALCULATE, SUMX, COUNTROWS, and other functions to create dynamic subsets of data.

Common uses:

• Basic filter: FILTER(Products, Products[Category] = "Electronics") — returns only electronics products
• Complex condition: FILTER(Sales, Sales[Amount] > AVERAGE(Sales[Amount])) — returns only rows where sale amount exceeds the overall average
• Used with CALCULATE: Above Avg Sales = CALCULATE([Total Sales], FILTER(ALL(Products), Products[Price] > 100)) — calculates sales for products priced above $100 regardless of current filters

Performance tip: FILTER iterates every row in the table. For simple equality filters inside CALCULATE, use direct column filters (e.g., Products[Category] = "Electronics") instead of FILTER for better performance on large datasets.

4. RELATED: Cross-Table Lookups

RELATED retrieves a value from a related table by following an established relationship. It works like VLOOKUP in Excel but uses the data model's defined relationships. This makes it more reliable and better performing.

Common uses:

• Basic lookup: In a calculated column on the Sales table: Product Category = RELATED(Products[Category]) — pulls the category from the Products table for each sales row
• Multi-hop relationship: RELATED can traverse multiple relationships. If Sales relates to Products, and Products relates to Suppliers, then Supplier Name = RELATED(Suppliers[Name]) works if the full relationship chain is established

Direction matters: RELATED follows many-to-one relationships (from the "many" side to the "one" side). Use RELATEDTABLE for one-to-many lookups (from the "one" side to the "many" side).

5. ALL: Removing Filters for Context Manipulation

ALL removes all filters from a table or column. It returns the complete set of values regardless of the current filter context. ALL is essential for calculating percentages of total, grand totals, and comparative measures.

Common uses:

• Percentage of total: % of Total Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products))) — shows each product's share of total sales
• Grand total reference: Grand Total = CALCULATE([Total Sales], ALL(Sales)) — always returns the unfiltered total regardless of slicers or visual-level filters
• ALL on a column: CALCULATE([Total Sales], ALL(Products[Color])) — removes only the Color filter while preserving all other filters; useful for comparing individual values against a partially filtered total
• ALLEXCEPT alternative: ALLEXCEPT(Products, Products[Category]) — removes all filters except Category; useful for percentage-of-category-total calculations
• ALLSELECTED: Similar to ALL but respects external slicer filters while removing visual-level filters

A Real-World Example: Combining All Five Functions

This measure calculates the percentage contribution of each product to its category's total sales — but only for products with above-average revenue. It uses CALCULATE, FILTER, ALL, and SUMX together:

% of Category (Above Avg Only) = 
DIVIDE(
  CALCULATE(
    [Total Sales],
    FILTER(
      ALL(Products),
      Products[Price] > AVERAGE(Products[Price])
    )
  ),
  CALCULATE([Total Sales], ALL(Products[Category]))
)

Breaking this down:

• FILTER(ALL(Products), ...) — creates a table of products with above-average price, ignoring current filters
• CALCULATE(..., FILTER(...)) — applies that filtered table as the filter context for Total Sales
• ALL(Products[Category]) — removes the Category filter to calculate the category-wide total as the denominator

What to Learn After These Five Functions

After mastering CALCULATE, SUMX, FILTER, RELATED, and ALL, these are the next-tier functions to learn:

• DIVIDE — safe division (avoids divide-by-zero errors)
• VALUES / DISTINCT — unique value lists
• SWITCH — multi-condition branching (cleaner than nested IF)
• DATEADD, SAMEPERIODLASTYEAR, TOTALYTD — time intelligence functions for period comparisons
• RANKX — ranking measures within a filtered context
• SELECTEDVALUE — detecting a single slicer selection for dynamic titles or conditional logic

Why EPC Group for Power BI Training

EPC Group brings 29 years of Microsoft consulting expertise to Power BI training and development. Founder Errin O'Connor authored four bestselling Microsoft Press books, including comprehensive guides to DAX and Power BI for enterprise deployments. We have trained thousands of analysts across Fortune 500 organizations.

Frequently Asked Questions

How do I learn DAX effectively as a beginner?

Start with CALCULATE, SUMX, FILTER, RELATED, and ALL. Practice building measures in Power BI Desktop using sample datasets like AdventureWorks or Contoso.

Resources like the SQLBI.com DAX Guide, Microsoft Learn modules, and EPC Group training programs provide structured learning paths. Focus on understanding filter context and row context — these concepts underpin all DAX formulas.

What is the difference between a calculated column and a measure?

A calculated column is evaluated row by row when data loads or refreshes, and is stored in the model consuming memory. A measure is evaluated dynamically at query time based on the current filter context.

Best practice: use measures for aggregations and KPIs, and use calculated columns only when you need a row-level value for sorting, filtering, or slicing.

How is DAX different from Excel formulas?

DAX shares some syntax similarities with Excel (function names like SUM, IF, AVERAGE) but the underlying evaluation model is fundamentally different. Excel operates cell by cell.

DAX operates on tables and columns within filter contexts. The concept of filter context, row context, and context transition via CALCULATE has no Excel equivalent — this is the primary learning curve for Excel users transitioning to DAX.

Why does my CALCULATE measure return unexpected results?

The most common cause is misunderstanding filter context. CALCULATE modifies the filter context by adding or replacing filters. If you use a column filter like Products[Color] = "Red", it replaces any existing filter on that column.

Use KEEPFILTERS() to add filters without replacing existing ones. Test measures in a simple table visual with minimal filters to validate behavior before adding to complex dashboards.