DAX Cheat Sheet 2026: 50 Essential Power BI Formulas

SUM

Adds all the numbers in a single column. Only works with column references, not expressions.

SUM( <ColumnName> )

Example: Total revenue from a Sales table.

Total Revenue = SUM( Sales[Revenue] )

AVERAGE

Returns the arithmetic mean of all numbers in a column. Ignores blanks and text values.

AVERAGE( <ColumnName> )

Example: Average deal size across all opportunities.

Avg Deal Size = AVERAGE( Opportunities[DealAmount] )

COUNT

Counts the number of cells in a column that contain numbers (non-blank numeric values). Use COUNTA for text.

COUNT( <ColumnName> )

Example: Count how many orders have an amount recorded.

Orders With Amount = COUNT( Orders[OrderAmount] )

COUNTROWS

Counts the total number of rows in a table or table expression. More reliable than COUNT for row counting.

COUNTROWS( <Table> )

Example: Total number of transactions.

Total Transactions = COUNTROWS( Sales )

MIN

Returns the smallest value in a column, or the smaller of two scalar values.

MIN( <ColumnName> )  |  MIN( <Scalar1>, <Scalar2> )

Example: Find the earliest order date.

First Order Date = MIN( Orders[OrderDate] )

MAX

Returns the largest value in a column, or the larger of two scalar values.

MAX( <ColumnName> )  |  MAX( <Scalar1>, <Scalar2> )

Example: Find the most recent sale date.

Last Sale Date = MAX( Sales[SaleDate] )

SUMX

Iterates over each row of a table, evaluates an expression, and returns the sum of all results. Use when you need row-level calculations before aggregating.

SUMX( <Table>, <Expression> )

Example: Calculate total revenue as Quantity times Unit Price per row.

Total Revenue = SUMX( Sales, Sales[Quantity] * Sales[UnitPrice] )

AVERAGEX

Iterates over each row of a table, evaluates an expression, and returns the arithmetic mean. Useful for weighted averages.

AVERAGEX( <Table>, <Expression> )

Example: Average profit margin per product.

Avg Margin = AVERAGEX( Products, Products[Revenue] - Products[Cost] )

COUNTAX

Iterates over each row, evaluates an expression, and counts non-blank results. Combines counting with row-level logic.

COUNTAX( <Table>, <Expression> )

Example: Count customers who have an email address on file.

Customers With Email = COUNTAX( Customers, Customers[Email] )

DISTINCTCOUNT

Counts the number of unique (distinct) values in a column. Blanks count as one distinct value.

DISTINCTCOUNT( <ColumnName> )

Example: Count unique customers who placed orders.

Unique Customers = DISTINCTCOUNT( Sales[CustomerID] )

FILTER

Returns a table that has been filtered by a Boolean expression. Evaluates row-by-row. Use inside CALCULATE for complex conditions.

FILTER( <Table>, <FilterExpression> )

Example: Sum revenue only for high-value orders above $1,000.

High Value Revenue =
CALCULATE(
    SUM( Sales[Revenue] ),
    FILTER( Sales, Sales[Revenue] > 1000 )
)

ALL

Removes all filters from a table or specified columns. Essential for calculating percentages of total and grand totals that ignore slicers.

ALL( <TableOrColumn> [, <Column2>, ...] )

Example: Calculate each category's percentage of total revenue.

% of Total =
DIVIDE(
    SUM( Sales[Revenue] ),
    CALCULATE( SUM( Sales[Revenue] ), ALL( Sales ) )
)

ALLEXCEPT

Removes all filters from a table except the specified columns. Shortcut for removing many filters while keeping a few.

ALLEXCEPT( <Table>, <Column1> [, <Column2>, ...] )

Example: Calculate percentage within each region (keep region filter, remove everything else).

% Within Region =
DIVIDE(
    SUM( Sales[Revenue] ),
    CALCULATE( SUM( Sales[Revenue] ), ALLEXCEPT( Sales, Regions[Region] ) )
)

ALLSELECTED

Removes filters applied by the current visual but keeps filters from slicers and page-level filters. Perfect for "percentage of visible total" calculations.

ALLSELECTED( [<TableOrColumn>] [, <Column2>, ...] )

Example: Percentage of the slicer-filtered total.

% of Filtered Total =
DIVIDE(
    SUM( Sales[Revenue] ),
    CALCULATE( SUM( Sales[Revenue] ), ALLSELECTED() )
)

REMOVEFILTERS

Clears filters from specified tables or columns. Functionally identical to ALL when used inside CALCULATE, but more readable and intentional.

REMOVEFILTERS( [<TableOrColumn>] [, <Column2>, ...] )

Example: Total revenue ignoring any product category filter.

Unfiltered Revenue =
CALCULATE(
    SUM( Sales[Revenue] ),
    REMOVEFILTERS( Products[Category] )
)

KEEPFILTERS

Intersects a new filter with the existing filter context instead of replacing it. Prevents CALCULATE from overriding slicer selections.

KEEPFILTERS( <FilterExpression> )

Example: Show Electronics revenue only when Electronics is also selected in a slicer.

Electronics If Selected =
CALCULATE(
    SUM( Sales[Revenue] ),
    KEEPFILTERS( Products[Category] = "Electronics" )
)

VALUES

Returns a one-column table of distinct values from the specified column, respecting the current filter context. Includes the blank row if present.

VALUES( <ColumnName> )

Example: Count how many distinct categories are visible in the current context.

Visible Categories = COUNTROWS( VALUES( Products[Category] ) )

SELECTEDVALUE

Returns the single value in a column if only one value is in the current filter context; otherwise returns a default. Ideal for dynamic titles and labels.

SELECTEDVALUE( <ColumnName> [, <AlternateResult>] )

Example: Dynamic chart title showing selected region.

Chart Title = "Revenue for " & SELECTEDVALUE( Regions[Region], "All Regions" )

HASONEVALUE

Returns TRUE if the current filter context has exactly one distinct value for the specified column. Use to conditionally change measure behavior.

HASONEVALUE( <ColumnName> )

Example: Show a specific value when one product is selected, otherwise show total.

Dynamic Measure =
IF(
    HASONEVALUE( Products[ProductName] ),
    SUM( Sales[Revenue] ),
    COUNTROWS( Products )
)

TOTALQTD

Evaluates a quarter-to-date running total. Resets at the start of each calendar quarter.

TOTALQTD( <Expression>, <DateColumn> [, <Filter>] )

Example: Quarter-to-date sales.

QTD Sales = TOTALQTD( SUM( Sales[Revenue] ), Calendar[Date] )

TOTALMTD

Evaluates a month-to-date running total. Resets at the start of each calendar month.

TOTALMTD( <Expression>, <DateColumn> [, <Filter>] )

Example: Month-to-date revenue.

MTD Revenue = TOTALMTD( SUM( Sales[Revenue] ), Calendar[Date] )

DATEADD

Returns a table of dates shifted forward or backward by a specified number of intervals (day, month, quarter, year). Foundation of period-over-period analysis.

DATEADD( <DateColumn>, <NumberOfIntervals>, <Interval> )

Example: Revenue from the same month last year.

Prior Year Revenue =
CALCULATE(
    SUM( Sales[Revenue] ),
    DATEADD( Calendar[Date], -1, YEAR )
)

DATESYTD

Returns a table containing dates from the start of the year up to the last date in the current filter context. Used inside CALCULATE as a filter argument.

DATESYTD( <DateColumn> [, <YearEndDate>] )

Example: Year-to-date revenue (equivalent to TOTALYTD but more explicit).

YTD Revenue =
CALCULATE( SUM( Sales[Revenue] ), DATESYTD( Calendar[Date] ) )

SAMEPERIODLASTYEAR

Shifts the current date filter back by exactly one year. Shortcut for DATEADD with -1 YEAR. Most commonly used for year-over-year comparisons.

SAMEPERIODLASTYEAR( <DateColumn> )

Example: Last year's revenue for the same time period.

LY Revenue =
CALCULATE( SUM( Sales[Revenue] ), SAMEPERIODLASTYEAR( Calendar[Date] ) )

PARALLELPERIOD

Returns a full parallel period shifted by a number of intervals. Unlike DATEADD, PARALLELPERIOD always returns complete periods (full months, quarters, or years).

PARALLELPERIOD( <DateColumn>, <NumberOfIntervals>, <Interval> )

Example: Revenue from the entire previous quarter.

Prev Quarter Revenue =
CALCULATE( SUM( Sales[Revenue] ), PARALLELPERIOD( Calendar[Date], -1, QUARTER ) )

PREVIOUSMONTH

Returns a table of dates for the previous month relative to the current filter context. Always returns a full calendar month.

PREVIOUSMONTH( <DateColumn> )

Example: Last month's total revenue.

Last Month Revenue =
CALCULATE( SUM( Sales[Revenue] ), PREVIOUSMONTH( Calendar[Date] ) )

DATESBETWEEN

Returns a table of dates between a specified start and end date. Use for custom date ranges that do not fit standard year/quarter/month patterns.

DATESBETWEEN( <DateColumn>, <StartDate>, <EndDate> )

Example: Revenue for the first half of 2026.

H1 2026 Revenue =
CALCULATE(
    SUM( Sales[Revenue] ),
    DATESBETWEEN( Calendar[Date], DATE(2026, 1, 1), DATE(2026, 6, 30) )
)

LASTDATE

Returns a single-row, single-column table containing the last date in the current filter context. Useful for semi-additive measures like inventory balances.

LASTDATE( <DateColumn> )

Example: Inventory balance as of the last date in the selected period.

Ending Inventory =
CALCULATE( SUM( Inventory[Balance] ), LASTDATE( Calendar[Date] ) )

SUMMARIZE

Creates a summary table grouped by specified columns. Best used for grouping only; avoid adding calculated columns inside SUMMARIZE (use ADDCOLUMNS instead).

SUMMARIZE( <Table>, <GroupByColumn1> [, <GroupByColumn2>, ...] )

Example: List of unique category and subcategory combinations.

Category List =
SUMMARIZE( Products, Products[Category], Products[SubCategory] )

SUMMARIZECOLUMNS

Groups by columns from different tables and adds measures. The preferred function for creating summary tables since it handles cross-table grouping and automatically removes empty rows.

SUMMARIZECOLUMNS( <GroupByColumn1> [, <Filter>], <"Name">, <Expression> )

Example: Revenue by category with a filter for year 2026.

Revenue by Category =
SUMMARIZECOLUMNS(
    Products[Category],
    FILTER( ALL( Calendar[Year] ), Calendar[Year] = 2026 ),
    "Total Revenue", SUM( Sales[Revenue] )
)

ADDCOLUMNS

Adds calculated columns to a table expression. Commonly paired with SUMMARIZE to create enriched summary tables.

ADDCOLUMNS( <Table>, <"NewColumnName">, <Expression> [, ...] )

Example: Category summary with total revenue and order count.

Category Summary =
ADDCOLUMNS(
    SUMMARIZE( Products, Products[Category] ),
    "Total Revenue", CALCULATE( SUM( Sales[Revenue] ) ),
    "Order Count", CALCULATE( COUNTROWS( Sales ) )
)

SELECTCOLUMNS

Returns a table with only the specified columns, optionally renamed or computed. Like a SQL SELECT statement.

SELECTCOLUMNS( <Table>, <"NewName">, <Expression> [, ...] )

Example: Create a clean lookup table of product IDs and names.

Product Lookup =
SELECTCOLUMNS(
    Products,
    "ID", Products[ProductID],
    "Name", Products[ProductName]
)

UNION

Combines two or more tables vertically (appends rows). Tables must have the same number of columns. Does not remove duplicates.

UNION( <Table1>, <Table2> [, <Table3>, ...] )

Example: Combine domestic and international sales into one table.

All Sales = UNION( DomesticSales, InternationalSales )

INTERSECT

Returns rows that exist in both tables. Useful for finding common elements between two sets.

INTERSECT( <Table1>, <Table2> )

Example: Find customers who purchased in both 2025 and 2026.

Repeat Customers =
INTERSECT(
    VALUES( Sales2025[CustomerID] ),
    VALUES( Sales2026[CustomerID] )
)

EXCEPT

Returns rows from the first table that do not exist in the second table. Ideal for finding new or lost customers.

EXCEPT( <Table1>, <Table2> )

Example: Find customers from 2025 who did not purchase in 2026.

Lost Customers =
EXCEPT(
    VALUES( Sales2025[CustomerID] ),
    VALUES( Sales2026[CustomerID] )
)

CROSSJOIN

Returns the Cartesian product (every combination) of two or more tables. Use carefully as results grow exponentially.

CROSSJOIN( <Table1>, <Table2> [, ...] )

Example: Create a matrix of all products and all regions.

Product Region Matrix =
CROSSJOIN( VALUES( Products[ProductName] ), VALUES( Regions[Region] ) )

GENERATE

For each row in the first table, evaluates the second table expression in the row context of the first. Like SQL CROSS APPLY.

GENERATE( <Table1>, <Table2Expression> )

Example: For each category, get the top 3 products by revenue.

Top Products Per Category =
GENERATE(
    VALUES( Products[Category] ),
    TOPN( 3, RELATEDTABLE( Products ), [Total Revenue] )
)

GENERATESERIES

Returns a single-column table containing a sequence of values from start to end with a specified step. Great for creating parameter tables and bins.

GENERATESERIES( <StartValue>, <EndValue> [, <IncrementValue>] )

Example: Create a discount percentage parameter from 0% to 50% in 5% increments.

Discount Steps = GENERATESERIES( 0, 0.50, 0.05 )

IF

Checks a condition and returns one value if TRUE, another if FALSE. The most basic conditional function in DAX.

IF( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )

Example: Flag high-value orders.

Order Status =
IF( SUM( Sales[Revenue] ) > 10000, "High Value", "Standard" )

SWITCH

Evaluates an expression against a list of values and returns the corresponding result. Use SWITCH(TRUE(), ...) for multiple conditions. Much cleaner than nested IF.

SWITCH( <Expression>, <Value1>, <Result1> [, <Value2>, <Result2>, ...] [, <Else>] )

Example: Map region codes to full names.

Region Name =
SWITCH(
    Regions[RegionCode],
    "NA", "North America",
    "EU", "Europe",
    "AP", "Asia Pacific",
    "Other"
)

AND

Returns TRUE if both arguments are TRUE. You can also use the && operator as a shorthand.

AND( <Logical1>, <Logical2> )

Example: Flag high-revenue orders in the current year.

Priority Flag =
IF(
    AND( Sales[Revenue] > 5000, Sales[Year] = 2026 ),
    "Priority",
    "Normal"
)

OR

Returns TRUE if either argument is TRUE. You can also use the || operator as a shorthand.

OR( <Logical1>, <Logical2> )

Example: Identify VIP customers by revenue or order count.

Is VIP =
IF(
    OR( [Total Revenue] > 100000, [Order Count] > 50 ),
    "VIP",
    "Standard"
)

NOT

Reverses the value of a Boolean expression. TRUE becomes FALSE and FALSE becomes TRUE.

NOT( <Logical> )

Example: Count non-cancelled orders.

Active Orders =
CALCULATE( COUNTROWS( Orders ), NOT( Orders[IsCancelled] ) )

CONCATENATE

Joins two text strings into one. For joining more than two strings, use the & operator or CONCATENATEX for tables.

CONCATENATE( <Text1>, <Text2> )

Example: Create a full name from first and last name columns.

Full Name =
CONCATENATE( Customers[FirstName], CONCATENATE( " ", Customers[LastName] ) )

FORMAT

Converts a value to text using a specified format string. Returns a text value, so the result cannot be used in further calculations.

FORMAT( <Value>, <FormatString> )

Example: Format revenue as currency for a dynamic title.

Revenue Label = "Total: " & FORMAT( [Total Revenue], "$#,##0" )

LEFT

Returns the specified number of characters from the start of a text string.

LEFT( <Text>, <NumberOfCharacters> )

Example: Extract the 2-letter country code from a product SKU.

Country Code = LEFT( Products[SKU], 2 )

RIGHT

Returns the specified number of characters from the end of a text string.

RIGHT( <Text>, <NumberOfCharacters> )

Example: Extract the last 4 digits of an order number.

Short Order ID = RIGHT( Orders[OrderNumber], 4 )

BLANK

Returns a blank (null) value. Use to suppress zeros or provide a clean empty state in conditional measures.

BLANK()

Example: Show revenue only if it exceeds a threshold, otherwise blank.

Significant Revenue =
IF( SUM( Sales[Revenue] ) > 100, SUM( Sales[Revenue] ), BLANK() )

Pattern 1: Year-over-Year Growth %

Compares the current period to the same period last year. Requires a date table.

YoY Growth % =
VAR CurrentRevenue = SUM( Sales[Revenue] )
VAR PriorYearRevenue =
    CALCULATE(
        SUM( Sales[Revenue] ),
        SAMEPERIODLASTYEAR( Calendar[Date] )
    )
RETURN
    DIVIDE(
        CurrentRevenue - PriorYearRevenue,
        PriorYearRevenue,
        BLANK()
    )

Pattern 2: Running Total (Cumulative Sum)

Calculates a cumulative running total from the start of the year to the current date in context.

Running Total =
CALCULATE(
    SUM( Sales[Revenue] ),
    FILTER(
        ALL( Calendar[Date] ),
        Calendar[Date] <= MAX( Calendar[Date] )
            && YEAR( Calendar[Date] ) = YEAR( MAX( Calendar[Date] ) )
    )
)

Pattern 3: Percentage of Grand Total

Shows each row's contribution to the overall total, ignoring all visual filters.

% of Grand Total =
VAR CurrentValue = SUM( Sales[Revenue] )
VAR GrandTotal =
    CALCULATE(
        SUM( Sales[Revenue] ),
        ALL( Sales )
    )
RETURN
    DIVIDE( CurrentValue, GrandTotal, 0 )

Pattern 4: Dynamic Ranking

Ranks items by a measure value within the current filter context. Updates dynamically as users apply slicers.

Product Rank =
IF(
    HASONEVALUE( Products[ProductName] ),
    RANKX(
        ALL( Products[ProductName] ),
        [Total Revenue],
        ,
        DESC,
        DENSE
    )
)

Pattern 5: 3-Month Moving Average

Smooths out fluctuations by averaging the current month and the two preceding months. Adjust the -2 to change the window.

3M Moving Avg =
AVERAGEX(
    DATESINPERIOD(
        Calendar[Date],
        MAX( Calendar[Date] ),
        -3,
        MONTH
    ),
    CALCULATE( SUM( Sales[Revenue] ) )
)