Top 20 DAX Functions Every Analyst Should Know

DAX has several hundred functions, but most reports are built from a few dozen. The twenty below, grouped by the job they do, cover the large majority of everyday measures. For each, there is a short example written for a typical sales model, with Sales, Product, Customer and Date tables, and the mistake analysts most often make with it. The examples are written in DAX syntax; the descriptions follow Microsoft's DAX reference.

Counting and summing

1. SUM adds a column in the current filter context: Sales Amount = SUM ( Sales[Amount] ). It sums one column only; for quantity times price, use SUMX.
2. DISTINCTCOUNT counts distinct values: Customers = DISTINCTCOUNT ( Sales[CustomerId] ). Do not add distinct counts across rows of a table visual; customers active in several months would be counted more than once.
3. COUNTROWS counts rows in a table: Order Lines = COUNTROWS ( Sales ). It counts rows of the fact table, so decide whether you mean lines or orders; orders need DISTINCTCOUNT on the order number.

Iterators

1. SUMX returns the sum of an expression evaluated for each row in a table (DAX reference: SUMX): Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] ). Iterate the smallest table that gives the right answer; iterating a huge fact table with a complex expression is slow.
2. AVERAGEX averages an expression over a table, often a table of groups: Avg Revenue per Customer = AVERAGEX ( VALUES ( Customer[CustomerId] ), [Revenue] ). Averaging over customers is not the same as averaging over order lines; choose the table deliberately.

Filter context

1. CALCULATE evaluates an expression in a modified filter context (DAX reference: CALCULATE): UK Revenue = CALCULATE ( [Revenue], Customer[Country] = "United Kingdom" ). A filter on a column that is already filtered replaces the existing filter; wrap it in KEEPFILTERS to intersect instead. DAX CALCULATE explained shows the difference with real numbers.
2. FILTER returns the rows of a table that meet a condition: CALCULATE ( [Revenue], FILTER ( VALUES ( Product[Product] ), [Revenue] > 1000 ) ). Use it for conditions involving measures; for a simple column condition, a Boolean filter is faster.
3. REMOVEFILTERS, and its older relative ALL, clear filters: Revenue All Countries = CALCULATE ( [Revenue], REMOVEFILTERS ( Customer[Country] ) ). Removing filters from a whole table also discards slicers on its other columns.
4. ALLSELECTED keeps filters from outside the visual, such as slicers, while ignoring the visual's own: useful for "share of what the user selected". DAX FILTER and ALL compares the three with real numbers.

Relationships

1. RELATED returns a related value from another table (DAX reference: RELATED), from the many side to the one side: Sales[Category] = RELATED ( Product[Category] ) in a calculated column. It needs a regular relationship; across a limited relationship it cannot fetch values.
2. USERELATIONSHIP activates an inactive relationship inside CALCULATE: Revenue by Ship Date = CALCULATE ( [Revenue], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) ). The relationship must already exist in the model, marked inactive.

Logic and safe arithmetic

1. DIVIDE divides and returns blank, or an alternative, when the denominator is zero: Margin % = DIVIDE ( [Profit], [Revenue] ). Prefer it to the slash operator in measures.
2. IF returns one value when a condition is true and another otherwise: Status = IF ( [Revenue] >= [Target], "On target", "Below target" ). Nesting many IFs quickly becomes unreadable; use SWITCH.
3. SWITCH evaluates an expression against a list of values and returns the matching result (DAX reference: SWITCH). The SWITCH ( TRUE (), ... ) form tests conditions in order, which suits banding: the first true condition wins, so order them carefully.

Reading the current selection

1. SELECTEDVALUE returns a column's value when the context has been filtered to exactly one distinct value, otherwise an alternative (DAX reference: SELECTEDVALUE): Selected Country = SELECTEDVALUE ( Customer[Country], "All countries" ). It is the standard way to build dynamic titles.
2. VALUES returns the distinct values of a column in the current context, as a one-column table, often fed to an iterator or FILTER. Remember it can include a blank value when the model has unmatched keys.

Time intelligence

1. TOTALYTD evaluates year to date: Revenue YTD = TOTALYTD ( [Revenue], 'Date'[Date] ), with an optional year end such as "3/31" for fiscal years. It needs a proper date table.
2. SAMEPERIODLASTYEAR shifts the dates in context back one year: Revenue PY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ). With an incomplete current month, it can compare a partial month with a full one; time intelligence in DAX shows the fix.
3. DATESINPERIOD returns a run of dates for rolling windows: Revenue 3M = CALCULATE ( [Revenue], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) ). DATEADD is its partner for shifting periods by any interval.

Ranking

1. RANKX returns the ranking of a value in a list for each row of a table (DAX reference: RANKX): Product Rank = RANKX ( ALL ( Product[Product] ), [Revenue] ). The ALL matters: without it, each product is ranked only against itself in the visual's context, and every rank comes out as 1.

How these combine

Most real measures are two or three of these working together: an aggregation or iterator for the number, CALCULATE with a modifier for the context, DIVIDE for the ratio, and a variable to keep it readable. For example, share of the selected countries:

Share of Selected Countries =
VAR ThisCountry = [Revenue]
VAR Selected = CALCULATE ( [Revenue], ALLSELECTED ( Customer[Country] ) )
RETURN DIVIDE ( ThisCountry, Selected )

DAX variables covers the VAR and RETURN pattern, and the DAX guide covers the concepts, measures and context, that every function above relies on.

Honourable mentions

ISBLANK, for guard conditions; CONCATENATEX, for joining the selected values into a text label; COALESCE, for replacing blanks with a default; and TREATAS, for applying a filter from an unrelated table. Learn them once the twenty above are second nature.

DAX fundamentals, aggregate, conditional and time intelligence functions are part of the DAX module of the Power BI course.

Sources

• SUMX function (DAX reference, Microsoft Learn) — Microsoft Learn
• CALCULATE function (DAX reference, Microsoft Learn) — Microsoft Learn
• RELATED function (DAX reference, Microsoft Learn) — Microsoft Learn
• SELECTEDVALUE function (DAX reference, Microsoft Learn) — Microsoft Learn
• RANKX function (DAX reference, Microsoft Learn) — Microsoft Learn
• SWITCH function (DAX reference, Microsoft Learn) — Microsoft Learn