XLsheetAI
Excel to DAX: The Formula Translation Guide for Power BI
- DAX doesn't calculate cell by cell — it calculates over an entire table at once, filtered by whatever the report is currently showing. That single difference is why most "just translate the formula" attempts fail.
- Direct swaps: VLOOKUP → RELATED() or LOOKUPVALUE(). SUMIF/SUMIFS → CALCULATE(SUM(), condition). COUNTIF → CALCULATE(COUNTROWS(), condition). Nested IF → SWITCH(TRUE(), ...).
- The concept that actually unlocks DAX is filter context versus row context — not memorizing function names. Once that clicks, the translation table below stops feeling like guesswork.
- Calculated columns and measures aren't interchangeable, even when the formula looks identical. Using the wrong one is the single most common mistake Excel users make in their first month of DAX.
The short answer
Most Excel formulas have a DAX equivalent, and the table further down maps the common ones directly. But copying a formula name across rarely works on the first try, because DAX measures recalculate based on whatever filters a report visual applies, while an Excel formula always calculates against the exact cells you typed into it. Learn that distinction first and the function-to-function mapping becomes mechanical.
Why Excel formulas don't translate 1:1
An Excel formula is anchored to specific cells: =SUM(B2:B50) always sums those 49 rows, no matter what else is on the sheet. A DAX measure like SUM(Sales[Amount]) sums the entire column by default, then Power BI automatically narrows that to whichever rows match the current visual — a specific region, a specific month, a specific product category — without you writing any filtering logic yourself. That automatic narrowing is called filter context, and it's the part of DAX that has no real Excel counterpart.
The practical effect: the same DAX measure produces a different number in every cell of a matrix visual, because each cell applies its own row and column filters to the same formula. In Excel, getting that same behavior would require a different SUMIFS in every cell. In DAX, you write the measure once.
The complete Excel-to-DAX translation table
These are the formulas people search for most when moving from Excel to Power BI, matched to the DAX pattern that actually behaves the same way in a report — not just the function with the closest-sounding name.
| Excel formula | DAX equivalent | Note |
|---|---|---|
| VLOOKUP / XLOOKUP | RELATED() or LOOKUPVALUE() | Use RELATED() if a relationship exists; LOOKUPVALUE() if it doesn't |
| SUMIF | CALCULATE(SUM(col), condition) | CALCULATE is what applies the condition as a filter |
| SUMIFS | CALCULATE(SUM(col), cond1, cond2, ...) | Each extra condition is just another CALCULATE argument |
| COUNTIF / COUNTIFS | CALCULATE(COUNTROWS(table), condition) | COUNTROWS counts rows, not a specific column's values |
| AVERAGEIF | CALCULATE(AVERAGE(col), condition) | Same CALCULATE pattern as SUMIF |
| IF | IF(condition, true_result, false_result) | Works the same and nests the same way |
| Nested IF (3+ levels) | SWITCH(TRUE(), cond1, result1, cond2, result2, ...) | Reads far more clearly than nested IFs past two levels |
| IFERROR | IFERROR(expression, fallback) | Same name, but DAX errors and blanks aren't identical to Excel's #N/A |
| CONCATENATE / & | CONCATENATE() or & | Behaves the same as Excel |
| COUNTA / COUNT | COUNTROWS() / COUNT() | COUNTROWS counts table rows; COUNT counts non-blank values in a column |
| UNIQUE | DISTINCT() or VALUES() | VALUES() also respects the current filter context; DISTINCT() ignores row context |
| RANK.EQ | RANKX(table, expression) | Needs a table and an expression, not a fixed range |
| Running total (SUM($A$1:A1)) | CALCULATE(SUM(col), FILTER(ALL(table), table[date] <= MAX(table[date]))) | No drag-fill trick in DAX — the filter has to be built explicitly |
| Pivot table (group + aggregate) | A measure dragged onto any matrix visual | Write the calculation once; regrouping the visual doesn't require rewriting it |
Row context vs. filter context, in one example
Row context is what a calculated column has: as DAX evaluates a formula for one row, it can see that row's other columns, the same way an Excel formula in row 12 can reference other cells in row 12. Filter context is what a measure has: it doesn't walk row by row at all, it evaluates one aggregation against whichever subset of rows the report has currently filtered down to.
Calculated columns vs. measures
Excel doesn't force this choice — a formula is a formula whether it's summarizing a range or transforming one cell. DAX does force it, and picking wrong is the most common reason a report shows a number that looks plausible but is quietly incorrect.
Build a calculated column when the value has to be fixed per row no matter how the report is sliced — flagging a product as "high margin," extracting a year from a date, categorizing a transaction. Build a measure when the value should change depending on what's being viewed — a total, an average, a percentage of a total, anything that should recompute as someone filters or regroups a visual. A calculated column is stored on disk for every row; a measure is computed on demand, which is also why measures scale better on large models.
Common DAX mistakes Excel users make
These account for the majority of "my Power BI number is wrong" questions, and every one of them comes from applying an Excel mental model where DAX needs a different one.
- Writing SUM() where CALCULATE(SUM()) was needed. A bare SUM() ignores any condition you meant to apply — it just totals the whole column. If the Excel version of the formula was a SUMIF or SUMIFS, the DAX version needs CALCULATE wrapped around the SUM.
- Forgetting ALL() for "percentage of total" calculations. To compute a row's share of a grand total, the denominator has to ignore the current filters on purpose — that's what ALL() does. Without it, the denominator shrinks along with the numerator and every percentage comes out as 100%.
- Putting a measure-style calculation in a calculated column. A calculated column can't see the report's filters, so a formula that depends on filter context returns the same value in every row instead of the value that changes per view.
- Assuming blank behaves like #N/A. DAX propagates BLANK() through most arithmetic without erroring, where Excel would often throw #N/A or #DIV/0!. A number that silently shows blank instead of erroring is easy to miss during testing.
- Not using DIVIDE() for division. Plain division errors on a zero denominator; DIVIDE(numerator, denominator, 0) returns a safe fallback instead, which is the DAX equivalent of wrapping a division in IFERROR.
A worked example: percentage of category total
In Excel, showing each product's share of its category's total sales usually means a SUMIFS for the category denominator, referenced from every row: =B2/SUMIFS($B$2:$B$500,$C$2:$C$500,C2). It works, but it has to be re-entered — or at least re-verified — every time the row count changes.
The DAX measure version is written once and works at any grouping level in the report:
| Step | DAX |
|---|---|
| Total sales, ignoring filters on category | Category Total = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category])) |
| Share of that total | % of Category = DIVIDE(SUM(Sales[Amount]), [Category Total]) |
Drop % of Category into a matrix next to Product, Category, or Region and it recalculates correctly at every level, because it was never anchored to specific cells to begin with — it's anchored to the filter context the report provides.
Time intelligence: the DAX category with no Excel shortcut
Comparing this month to last month, or this year to the same period last year, usually means a helper column of manually shifted dates in Excel, or an OFFSET-based formula that breaks the moment a row gets inserted. DAX has a dedicated family of functions built for exactly this, and none of them require a helper column at all.
SAMEPERIODLASTYEAR() shifts the current filter context back exactly one year, so wrapping a measure in CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) gives last year's number for whatever period is currently selected, at any grouping level. DATEADD('Date'[Date], -1, MONTH) does the same for a one-month shift, and TOTALYTD() produces a year-to-date running total without any manual date range at all. All three require a proper date table marked as such in the model — without one, time intelligence functions either error or silently return blanks, which is the single most common reason someone's "works in the example, breaks in my model" experience happens.
Why Power BI measures don't need a table name in front of every column
Excel formulas always reference cells directly, so =A2+B2 never needs to explain which sheet A2 lives on unless it's a cross-sheet reference. DAX measures instead reference table-qualified columns, like Sales[Amount], because a Power BI model usually has several related tables and DAX needs to know exactly where a column lives even when its name isn't ambiguous. This also means renaming a table breaks every measure that references it by name — a maintenance cost Excel formulas never have, since a cell reference doesn't care what the sheet is called.
Still writing the Excel side of this by hand?
Describe the formula you need in plain English and XLsheetAI writes it for you, explains the logic, and lets you practise it hands-on — the exact groundwork worth getting right before it becomes a DAX measure.
Deciding whether you need Power BI at all yet? See the full Excel vs Power BI breakdown, or check the XLOOKUP vs VLOOKUP comparison before you translate a lookup formula into RELATED() or LOOKUPVALUE().
FAQ
Is there a direct DAX equivalent for VLOOKUP?
Yes, two of them. If a relationship already exists between the two tables, use RELATED() to pull a column across it. If there's no relationship, use LOOKUPVALUE(), which works like VLOOKUP but can match on multiple conditions at once.
Why does my SUM() measure return the wrong number in a pivot?
A plain SUM() ignores whatever filters the visual is applying and adds up every row in the table. If you need the total to change based on a condition Excel would have handled with SUMIF or SUMIFS, wrap it in CALCULATE() with that condition as an argument.
What's the DAX equivalent of nested IF statements?
IF() itself works the same as in Excel and nests the same way, but once you're past two or three conditions, SWITCH(TRUE(), condition1, result1, condition2, result2, ...) reads far more clearly and is the pattern most experienced DAX writers reach for.
Should I build a calculated column or a measure?
Use a calculated column when the value must be fixed per row regardless of what's on the report, like categorizing a product. Use a measure when the value should recalculate based on whatever filters or grouping the report applies, like a total or a percentage.
Do I need to learn DAX if I already know Power Pivot in Excel?
You already know it. Power Pivot in Excel and Power BI's data model both run on the same DAX language and the same VertiPaq engine, so measures and calculated columns you write in one work the same way in the other.