DAX Cheat Sheet: 50 Functions Every Power BI User Should Know

SUM adds up every numeric value in a column, automatically ignoring blanks.

Total Sales = SUM(Sales[Revenue])

When to use: Use SUM when the value you want to total already exists as a physical numeric column in your table. Don't reach for SUM when the calculation needs to happen row by row first — that's SUMX.

SUMX iterates through a table row by row, evaluates an expression for each row, then sums the results.

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

When to use: Use SUMX whenever the value you're summing doesn't already exist in a single column — for example, when multiplying quantity by unit price across related tables before adding the results up.

AVERAGE returns the arithmetic mean of a numeric column, automatically excluding blank values from both the numerator and the denominator.

Avg Order Value = AVERAGE(Sales[OrderValue])

When to use: The blank-exclusion behavior matters: a column with 10 rows but 2 blanks is averaged over 8 values, not 10. If you want blanks treated as zero, replace them with 0 before averaging.

AVERAGEX evaluates an expression for each row in a table, then returns the average of those computed results.

Avg Line Total = AVERAGEX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))

When to use: Use AVERAGEX when the value being averaged needs to be calculated row by row across multiple columns or relationships first.

COUNT counts rows where a specific column contains a numeric value, ignoring blanks and text strings.

Orders With Qty = COUNT(Sales[Quantity])

When to use: If your column contains text (like customer names), use COUNTA instead — COUNT will not count text values.

COUNTA counts rows where a column contains any value — numbers, text, or dates — ignoring only true blanks.

Total Customers = COUNTA(Customers[CustomerName])

When to use: Use COUNTA for text and mixed-type columns. Use COUNT specifically when counting only numeric values and explicitly excluding text.

COUNTROWS counts the total number of rows in a table, regardless of what any individual column contains.

Total Orders = COUNTROWS(Sales)

When to use: COUNTROWS is the fastest, cleanest function when the question is simply "how many rows exist in this table". Use DISTINCTCOUNT when the question is "how many unique entities".

DISTINCTCOUNT counts how many unique values exist in a column, treating repeated values as one.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

When to use: Ten transactions made by four distinct customers returns 4, not 10. This is the foundation of customer retention and unique entity metrics.

CALCULATE evaluates an expression inside a modified filter context — it is how you tell DAX to compute something under conditions different from what visual slicers currently specify.

Sales 2025 = CALCULATE([Total Sales], Sales[Year] = 2025)

When to use: CALCULATE is the central pillar of DAX. It performs context transition (converting row context into filter context) and accepts filter modifiers like ALL, REMOVEFILTERS, and KEEPFILTERS.

FILTER iterates a table and returns a subset table containing only the rows that satisfy a boolean condition.

High Value Orders = FILTER(Sales, Sales[OrderValue] > 10000)

When to use: Most commonly used inside CALCULATE when your filter condition compares multiple columns or requires a measure evaluation per row.

ALL removes all active filters from a table or column, returning every row regardless of what filters are selected in the visual or slicers.

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

When to use: The foundation of all "percent of grand total" metrics. The denominator clears all slicer selections to compute the unfiltered grand baseline.

ALLEXCEPT removes every filter from a table except for the specific columns explicitly preserved in the argument list.

Regional % = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Sales, Sales[Region])))

When to use: Use when clearing product or date filters while keeping a regional partition intact.

ALLSELECTED removes filters added internally inside a matrix visual row/column, while still honoring user-selected outer slicers and page filters.

% of Visible Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales)))

When to use: Reach for ALLSELECTED when your "percent of total" should dynamically sum up to 100% across the items currently visible under the user's active slicers.

REMOVEFILTERS removes filters from specified columns or tables inside a CALCULATE call — providing a clear, self-documenting syntax.

Total Ignoring Region = CALCULATE([Total Sales], REMOVEFILTERS(Sales[Region]))

When to use: Preferred in modern enterprise DAX over ALL when used strictly as a filter modifier inside CALCULATE because its name explicitly states the operational intent.

KEEPFILTERS forces a filter argument inside CALCULATE to intersect with existing filters rather than overriding and replacing them.

Sales North = CALCULATE([Total Sales], KEEPFILTERS(Sales[Region] = "North"))

When to use: Use when you want a calculation to respect visual-level exclusions without silently overriding user filter selections.

SELECTEDVALUE returns the single value of a column when exactly one value is filtered in context, returning a fallback string or blank if multiple values exist.

Dynamic Header = "Sales for " & SELECTEDVALUE(Sales[Region], "All Regions")

When to use: Essential for dynamic chart titles, KPI card subtitles, and conditional measures driven by slicers.

HASONEVALUE returns TRUE when a column has exactly one distinct value in the current filter context, and FALSE otherwise.

Region Label = IF(HASONEVALUE(Sales[Region]), VALUES(Sales[Region]), "Multiple")

When to use: Guard calculations that should only execute on individual matrix rows, hiding confusing results on total rows.

TOTALYTD calculates a year-to-date running total from the beginning of the year through the latest date in the current filter context.

YTD Sales = TOTALYTD([Total Sales], 'Calendar'[Date])

When to use: For custom fiscal years ending on June 30, supply the optional third parameter: TOTALYTD([Total Sales], 'Calendar'[Date], "06-30").

TOTALQTD calculates a quarter-to-date running total from the first day of the current quarter through the active date context.

QTD Sales = TOTALQTD([Total Sales], 'Calendar'[Date])

When to use: Pair with TOTALYTD and TOTALMTD to build complete executive timeframe matrices.

SAMEPERIODLASTYEAR shifts the dates in the current filter context exactly one full year backward.

LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))

When to use: The standard industry function for Year-over-Year (YoY) revenue and margin comparisons.

DATEADD shifts the dates in the current filter context forward or backward by a specified number of intervals (DAY, MONTH, QUARTER, YEAR).

Sales 3M Ago = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -3, MONTH))

When to use: Much more versatile than SAMEPERIODLASTYEAR when building rolling 3-month or custom multi-interval shifts.

DATESYTD returns a single-column table of dates starting from the beginning of the year through the latest date in context.

YTD Filtered = CALCULATE([Total Sales], DATESYTD('Calendar'[Date]), Products[Category] = "Audio")

When to use: Use inside CALCULATE when combining YTD date ranges with multiple explicit dimension filters.

PREVIOUSMONTH returns a table containing all calendar dates from the previous complete calendar month.

PM Sales = CALCULATE([Total Sales], PREVIOUSMONTH('Calendar'[Date]))

When to use: Cleaner and more readable than DATEADD('Calendar'[Date], -1, MONTH) for month-over-month variances.

PARALLELPERIOD shifts a date selection forward or backward by a number of full calendar periods, always expanding to complete periods.

PY Total Sales = CALCULATE([Total Sales], PARALLELPERIOD('Calendar'[Date], -1, YEAR))

When to use: If your active context is June 10–20, DATEADD(-1, MONTH) evaluates May 10–20, but PARALLELPERIOD(-1, MONTH) evaluates the full month of May (1–31).

DATESBETWEEN returns a table of dates between a specified start date and end date.

Promo Sales = CALCULATE([Total Sales], DATESBETWEEN('Calendar'[Date], DATE(2026,11,20), DATE(2026,11,30)))

When to use: Ideal for fixed marketing campaigns, Black Friday sales windows, or custom corporate budget cycles.

IF evaluates a logical test and returns one value if true, and a second value if false.

Status = IF([Total Sales] > 50000, "On Target", "Needs Attention")

When to use: Use for simple binary conditions. If you find yourself nesting more than 2 IF statements, refactor immediately to SWITCH.

SWITCH evaluates an expression against a list of values or multiple boolean conditions, returning the result of the first matching branch.

Tier = 
SWITCH(TRUE(),
    [Total Sales] > 100000, "Platinum",
    [Total Sales] > 50000, "Gold",
    [Total Sales] > 20000, "Silver",
    "Bronze"
)

When to use: The SWITCH(TRUE(), ...) pattern is the gold standard for readable tier classification and performance band logic.

AND returns TRUE only when both tested conditions evaluate to true.

HighValueActive = AND([Total Sales] > 10000, [Order Count] > 5)
// Or using inline operator:
HighValueActive2 = [Total Sales] > 10000 && [Order Count] > 5

When to use: The && operator syntax is standard across professional DAX code bases for chaining multiple conditions.

OR returns TRUE if at least one of the conditions evaluates to true.

PriorityRegion = [Region] = "North" || [Region] = "East"

When to use: Use || for multiple conditions, or Sales[Region] IN {"North", "East"} for checking sets of discrete values.

COALESCE evaluates arguments from left to right and returns the first non-blank value.

CustomerDisplayName = COALESCE(Customer[PreferredName], Customer[LegalName], "Anonymous")

When to use: Modern, performant replacement for messy nested IF(ISBLANK(...), ...) fallback chains.

CONCATENATE joins two text strings into a single text output.

FullName = Customer[FirstName] & " " & Customer[LastName]

When to use: Use the & ampersand operator to concatenate more than two elements in a single readable line.

FORMAT converts numeric or date values into formatted text strings according to a format string pattern.

MonthLabel = FORMAT('Calendar'[Date], "MMM YYYY") // e.g. "Sep 2026"
SalesCard = FORMAT([Total Sales], "$#,##0.00")

When to use: Essential for card labels and dynamic titles. Remember that the output is text, so it cannot be aggregated numerically.

LEFT returns a specified number of characters from the start of a text string.

AreaCode = LEFT(Customers[Phone], 3)

When to use: Use alongside RIGHT and MID for parsing structured keys or prefix codes.

LEN counts the number of characters in a text string.

IsValidSKU = IF(LEN(Products[SKU]) = 8, "Valid", "Invalid Length")

When to use: Data validation, error flagging, and dynamic position parsing.

DIVIDE performs division and automatically returns a fallback value or blank instead of throwing a divide-by-zero exception.

Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)

When to use: Always use DIVIDE rather than the / forward slash operator across all DAX measures to prevent report visual crashes.

ABS returns the absolute positive magnitude of a numeric number.

VarianceMagnitude = ABS([Actual Sales] - [Budget Sales])

When to use: Variance tolerance checks, error thresholding, and distance metrics.

ROUND rounds a number to a specified number of decimal places.

RoundedK = ROUND([Total Revenue] / 1000, 1)

When to use: Precision clipping and financial summary rounding. Use ROUNDUP or ROUNDDOWN for ceiling/floor control.

RANKX evaluates an expression across a table and assigns a rank ordinal according to sorted values.

ProductRank = RANKX(ALL(Products[ProductName]), [Total Sales], , DESC, Dense)

When to use: Top N filtering, salesperson leaderboards, and regional rankings. Use Dense tie-breaking for consecutive rank values.

RELATED retrieves a value from a lookup table following an existing active relationship from the many side to the one side.

CategoryName = RELATED(Products[Category])

When to use: Inside row contexts (such as calculated columns or iterator expressions like SUMX) on a fact table.

RELATEDTABLE returns all rows from a related table that correspond to the current row context.

ProductOrderCount = COUNTROWS(RELATEDTABLE(Sales))

When to use: Used on the one-side dimension table (like Products) to count or aggregate rows in the related transaction fact table.

LOOKUPVALUE retrieves a value from a target table matching one or more search conditions without requiring a model relationship.

Manager = LOOKUPVALUE(Managers[Name], Managers[RegionID], Sales[RegionID])

When to use: Use when tables cannot be joined directly in the semantic model due to ambiguity or circular dependencies.

USERELATIONSHIP temporarily activates an inactive relationship between two columns for the duration of a single CALCULATE evaluation.

OrdersByShipDate = CALCULATE([Total Orders], USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date]))

When to use: Crucial when fact tables contain multiple date keys (OrderDate, ShipDate, DeliveryDate) linked to a single calendar table.

VALUES returns a single-column table of unique values respecting the current filter context, including a blank row for unmatched keys.

ActiveRegions = VALUES(Sales[Region])

When to use: Feeding lists of values into table iterators or as filter arguments inside CALCULATE.

DISTINCT returns a table of unique values from a column or table, strictly excluding the automated blank row generated by relationship mismatches.

UniqueProducts = DISTINCT(Sales[ProductID])

When to use: Use DISTINCT when unmatched relationship keys should not produce an artificial blank row in intermediate tables.

SUMMARIZE creates a virtual summary table grouped by one or more dimension columns.

RegionSummary = SUMMARIZE(Sales, Sales[Region], "Revenue", SUM(Sales[Revenue]))

When to use: Use to group data before feeding into higher-level iterators like AVERAGEX or MINX.

ADDCOLUMNS appends one or more calculated columns to an existing table expression inside a virtual variable block.

VirtualTable = 
ADDCOLUMNS(
    VALUES(Products[Category]),
    "CategorySales", [Total Sales],
    "TargetSales", [Budget Sales]
)

When to use: Best practice pattern is combining SUMMARIZE for grouping and ADDCOLUMNS for measure evaluation.

MINX iterates through a table row by row, evaluates an expression for each row, and returns the smallest value.

LowestOrderValue = MINX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))

When to use: When calculating the minimum of an expression calculated across multiple columns or relationships.

MAXX iterates through a table, evaluates an expression per row, and returns the highest resulting value.

HighestDailySales = MAXX(VALUES('Calendar'[Date]), [Total Sales])

When to use: Finding single-day peak sales across a multi-month period.

VAR evaluates and stores the result of an expression once inside a formula; RETURN yields the final output using those variables.

YoY Growth % = 
VAR CurrentSales = [Total Sales]
VAR PriorSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
RETURN
    DIVIDE(CurrentSales - PriorSales, PriorSales)

When to use: Use variables in every non-trivial DAX formula. They prevent redundant recalculations and make testing step-by-step intermediate results effortless.

ISBLANK returns TRUE if an expression evaluates to blank or null, and FALSE otherwise.

SalesDisplay = IF(ISBLANK([Total Sales]), "No Transactions", FORMAT([Total Sales], "$#,##0"))

When to use: Cleaning up blank scorecard tiles and replacing empty cells with informative business messages.