Power BI DAX Functions Cheat Sheet: 20 Formulas Every Analyst Must Know

Direct Answer: DAX (Data Analysis Expressions) is the formula language that powers every calculated column, measure, and calculated table inside Power BI. The 20 DAX functions every analyst must know fall into five categories: Aggregation (SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN, MAX), Filter (CALCULATE, FILTER, ALL, ALLEXCEPT, KEEPFILTERS), Time Intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD), Text/Logic (IF, SWITCH, CONCATENATEX, FORMAT), and Table (SUMMARIZE, ADDCOLUMNS). Master these 20 functions and you can build 90% of the dashboards companies actually need. Power BI holds 36% market share in India, and analysts with DAX proficiency earn 30% more than those who only use the drag-and-drop interface.

What Is DAX and Why Every Power BI Analyst Needs It

DAX stands for Data Analysis Expressions. It is the formula language built into Power BI (and also used in Excel Power Pivot and SQL Server Analysis Services). When you create a measure like Total Revenue = SUM(Sales[Amount]), you are writing DAX. When you build a year-over-year comparison that dynamically adjusts when a user clicks a slicer, you are writing DAX. When you create a calculated column that categorises customers into segments based on purchase behaviour, you are writing DAX.

Power BI’s drag-and-drop interface can build simple charts. But every dashboard that goes beyond basic visualisation — dynamic calculations, filtered aggregations, time comparisons, conditional formatting logic — requires DAX. Microsoft’s own data shows that Power BI holds 36% market share among BI tools in India, and the tool’s adoption is accelerating across BFSI, IT services, manufacturing, and retail sectors. Companies are not just buying Power BI licenses — they are hiring analysts who can write DAX measures that answer real business questions.

20 Essential DAX Functions: The Complete Cheat Sheet

Category 1: Aggregation Functions (6 Functions)

Aggregation functions are the foundation. They summarise data across rows and are the building blocks for every measure you will ever write.

1. SUM

Total Sales = SUM(Sales[Amount])

Adds up all values in a column. The most basic and most used DAX function. Use it for revenue totals, quantity sums, and any additive metric.

2. AVERAGE

Avg Order Value = AVERAGE(Sales[Amount])

Returns the arithmetic mean. Use it for average deal size, average customer spend, average delivery time — any metric where the mean matters more than the total.

3. COUNT

Order Count = COUNT(Sales[OrderID])

Counts non-blank values in a column. Use it to count transactions, records, or entries. Note: COUNT works only on columns with numbers or dates. For text columns, use COUNTA.

4. DISTINCTCOUNT

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Counts unique values only. This is the function you reach for when you need “how many unique customers” rather than “how many transactions”. Critical for customer analytics, product coverage, and market penetration dashboards.

5. MIN

First Order Date = MIN(Sales[OrderDate])

Returns the smallest value. Use it to find earliest dates, lowest prices, minimum quantities, or floor values in any dataset.

6. MAX

Latest Order Date = MAX(Sales[OrderDate])

Returns the largest value. Use it for latest dates, highest prices, peak quantities, or ceiling values. MIN and MAX together define ranges for dynamic reporting.

Category 2: Filter Functions (5 Functions)

Filter functions control context — they determine which rows are included in a calculation. This is where DAX becomes genuinely powerful and where most beginners struggle.

7. CALCULATE

Online Sales = CALCULATE(SUM(Sales[Amount]), Sales[Channel] = "Online")

The single most important DAX function. CALCULATE evaluates an expression in a modified filter context. It takes a measure as the first argument and one or more filter conditions after it. Every time you need “total sales but only for region X” or “average revenue but only for Q4”, you need CALCULATE. Understanding CALCULATE’s filter context behaviour is the dividing line between beginner and intermediate DAX.

8. FILTER

High Value Orders = CALCULATE(COUNT(Sales[OrderID]), FILTER(Sales, Sales[Amount] > 10000))

Returns a table of rows that meet a condition. Unlike CALCULATE’s simple column filters, FILTER can evaluate complex row-by-row conditions. Use it when your filter logic involves multiple columns or calculated expressions.

9. ALL

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

Removes all filters from a table or column. Essential for percentage-of-total calculations. When a user selects “Electronics” in a slicer, ALL(Sales) ignores that selection and returns the grand total — giving you the denominator for percentage calculations.

10. ALLEXCEPT

Category Share = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Category])))

Removes all filters except the ones you specify. If you want to calculate each product’s share within its category (keeping the category filter but removing everything else), ALLEXCEPT is cleaner than combining ALL with specific filter additions.

11. KEEPFILTERS

Filtered Sales = CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Region] = "South"))

Adds a filter that intersects with existing filters instead of replacing them. By default, CALCULATE’s filters override existing context. KEEPFILTERS preserves existing slicer selections and adds your condition on top. Use it when you want additive, not replacement, filtering.

Category 3: Time Intelligence Functions (4 Functions)

Time intelligence functions handle date-based calculations — year-to-date totals, same-period comparisons, and date shifting. They require a proper date table (a continuous table of dates with no gaps) marked as a date table in your data model.

12. TOTALYTD

YTD Revenue = TOTALYTD(SUM(Sales[Amount]), DateTable[Date])

Calculates the year-to-date total. If a user is looking at March, TOTALYTD automatically sums January + February + March. No manual date filtering needed. Essential for every financial dashboard.

13. SAMEPERIODLASTYEAR

Last Year Sales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(DateTable[Date]))

Shifts the current date context back by exactly one year. Use it inside CALCULATE to create year-over-year comparisons. If the user is viewing Q2 2026, this function returns Q2 2025 values. The backbone of every YoY growth analysis.

14. DATEADD

Prior Quarter Sales = CALCULATE(SUM(Sales[Amount]), DATEADD(DateTable[Date], -1, QUARTER))

Shifts dates by a specified interval (day, month, quarter, year). More flexible than SAMEPERIODLASTYEAR because you control the interval. Use it for month-over-month, quarter-over-quarter, or any custom period comparison.

15. DATESYTD

YTD Date Range = DATESYTD(DateTable[Date])

Returns a table of dates from the start of the year to the current date in context. Unlike TOTALYTD (which wraps an aggregation), DATESYTD returns the date range itself — useful when you need the YTD date set for more complex calculations or when combining with other filter functions.

Category 4: Text and Logic Functions (4 Functions)

16. IF

Performance = IF(Sales[Amount] > 100000, "High", "Standard")

Conditional logic. Returns one value when a condition is true, another when false. Use it for categorisation, conditional formatting, and status tagging. Can be nested but avoid more than 3 levels — use SWITCH instead.

17. SWITCH

Region Label = SWITCH(Sales[RegionCode], "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")

Evaluates an expression against a list of values and returns the matching result. Cleaner than nested IF statements when you have multiple conditions. The last argument (without a matching value) is the default.

18. CONCATENATEX

Product List = CONCATENATEX(Products, Products[Name], ", ")

Concatenates values from a table column into a single string with a delimiter. Use it when you need a comma-separated list of products, categories, or regions in a single cell. Extremely useful for tooltip text and summary tables.

19. FORMAT

Month Name = FORMAT(Sales[OrderDate], "MMMM YYYY")

Converts a value to a formatted text string. Use it for custom date labels (“January 2026”), currency formatting, or any situation where you need precise control over how a value displays. Note: FORMAT returns text, not a number — do not use it inside calculations.

Category 5: Table Functions (2 Functions)

20. SUMMARIZE

Category Summary = SUMMARIZE(Sales, Sales[Category], "Total", SUM(Sales[Amount]), "Orders", COUNT(Sales[OrderID]))

Groups a table by specified columns and adds aggregated columns. Think of it as Power BI’s equivalent of SQL’s GROUP BY. Use it to create summary tables, intermediate calculations, and grouped analyses.

21. ADDCOLUMNS

Enhanced Products = ADDCOLUMNS(Products, "Revenue", CALCULATE(SUM(Sales[Amount])), "Order Count", CALCULATE(COUNT(Sales[OrderID])))

Adds calculated columns to an existing table expression. Unlike SUMMARIZE which groups, ADDCOLUMNS keeps every row and enriches it with new computed columns. Use it for building virtual tables that feed into other DAX calculations or for creating detailed lookup tables with computed metrics.

Complete DAX Function Reference Table

Real Dashboard Scenarios: DAX Functions in Action

Scenario 1: Executive Sales Dashboard

An e-commerce company needs a dashboard showing total revenue, average order value, unique customers, YTD performance, and YoY growth — all responding to date and region slicers.

• Total Revenue: SUM(Sales[Amount])
• Average Order Value: AVERAGE(Sales[Amount])
• Unique Customers: DISTINCTCOUNT(Sales[CustomerID])
• YTD Revenue: TOTALYTD(SUM(Sales[Amount]), DateTable[Date])
• YoY Growth %: DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(DateTable[Date])), CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(DateTable[Date])))

Five functions, one dashboard. This combination covers 80% of what executives ask for.

Scenario 2: Product Performance Matrix

A retail chain wants to see each product category’s contribution to total sales, with the ability to drill down without losing the percentage context.

• Category Sales: SUM(Sales[Amount]) — automatically filtered by slicer context
• Total Sales (ignoring category filter): CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category]))
• Category Share: DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales[Category])))
• Category Products List: CONCATENATEX(VALUES(Products[Name]), Products[Name], ", ")

The CALCULATE + ALL pattern is doing the heavy lifting here — removing the category filter to compute the denominator while letting every other slicer (date, region, channel) still apply.

Scenario 3: HR Headcount and Attrition Tracker

An HR team needs to track active employees, monthly joiners, monthly exits, and attrition rate — with the ability to filter by department and location.

• Active Employees: CALCULATE(DISTINCTCOUNT(Employees[EmpID]), FILTER(Employees, Employees[JoinDate] <= MAX(DateTable[Date]) && (ISBLANK(Employees[ExitDate]) || Employees[ExitDate] > MAX(DateTable[Date]))))
• Monthly Exits: CALCULATE(COUNT(Employees[EmpID]), DATEADD(DateTable[Date], 0, MONTH), NOT ISBLANK(Employees[ExitDate]))
• Department Label: IF(DISTINCTCOUNT(Employees[Department]) = 1, VALUES(Employees[Department]), "Multiple")

This scenario shows how FILTER handles row-level logic that simple CALCULATE filters cannot express.

Case Study: DAX Transforms a Manual Reporting Process

Before

Priya, a business analyst at a mid-sized FMCG company in Chennai, spent 12 hours every Monday creating weekly sales reports in Excel. She manually calculated region-wise sales, compared them to last year’s numbers using VLOOKUP across multiple spreadsheets, computed YTD totals by filtering date ranges, and formatted everything into a presentation deck. The reports were frequently late, occasionally had formula errors, and could not be filtered interactively by the leadership team.

The DAX Implementation

After enrolling in a Power BI programme and learning DAX, Priya rebuilt the entire reporting process in Power BI over three weeks. She created a proper date table, built measures using SUM, CALCULATE, TOTALYTD, and SAMEPERIODLASTYEAR, and used SWITCH to map region codes to readable labels. She used ALLEXCEPT to calculate each region’s share of national sales. The data model connected directly to the company’s SQL database, refreshing automatically every morning.

Result

Monday reporting time dropped from 12 hours to zero — the dashboard updated itself. Leadership could filter by region, product line, and time period without asking Priya. Formula errors were eliminated because measures were defined once and applied consistently. Priya’s salary increased from ₹5.5 LPA to ₹9 LPA within six months, and she was promoted to Senior BI Analyst. Her manager’s feedback: “The DAX measures Priya built replaced five separate Excel files and gave us answers in seconds instead of days.”

Common DAX Mistakes and How to Avoid Them

1. Mistake: Not creating a proper date table.
   Time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD) require a continuous date table with no gaps, marked as a date table in the data model. Using a date column directly from your fact table causes incorrect or blank results. Always create a dedicated date dimension table.
2. Mistake: Using CALCULATE without understanding filter context.
   CALCULATE replaces existing filter context by default. Writing CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North") overrides any region slicer selection — the user clicking “South” will still see North’s numbers. Use KEEPFILTERS when you want additive behaviour instead of replacement.
3. Mistake: Dividing without DIVIDE.
   Using [Measure A] / [Measure B] causes divide-by-zero errors when the denominator is zero or blank. Always use DIVIDE([Measure A], [Measure B], 0) — the third argument is the alternate result when division fails. This single habit eliminates the most common DAX error in production dashboards.
4. Mistake: Using FORMAT in calculations.
   FORMAT converts a value to text. If you write FORMAT(SUM(Sales[Amount]), "#,##0") and then try to sort or compare it, the result is alphabetical text sorting, not numerical. Use FORMAT only for display labels. Keep calculations in numeric form and apply formatting in the visual’s settings.
5. Mistake: Overusing calculated columns instead of measures.
   Calculated columns are computed row-by-row and stored in memory. Measures are computed at query time based on the filter context. If your formula does not need to be evaluated for every row (e.g., totals, averages, percentages), make it a measure, not a calculated column. Overusing calculated columns bloats your data model and slows performance.
6. Mistake: Nesting IF statements beyond 3 levels.
   Deeply nested IF statements are unreadable and error-prone. Use SWITCH for multi-condition logic. SWITCH(TRUE(), Condition1, Result1, Condition2, Result2, DefaultResult) is cleaner than nesting 5 IF functions and far easier to debug.

DAX Proficiency and Salary Impact

The 30% salary premium for DAX proficiency is not a vague claim — it directly reflects the gap between visual-only Power BI users (₹3-4 LPA) and analysts who write production-grade DAX measures (₹10-18 LPA). The PL-300 certification (Microsoft Certified: Power BI Data Analyst Associate) increasingly appears as a requirement in Indian job postings, and DAX constitutes 25-30% of the exam content.

Frequently Asked Questions

What is the most important DAX function to learn first?

CALCULATE. It is the foundation of almost every advanced DAX measure. CALCULATE modifies filter context, which means it controls which rows are included in any calculation. Once you understand CALCULATE and how it interacts with slicers and row context, every other DAX function becomes easier to learn and apply.

Do I need to know DAX to get a Power BI job?

For entry-level MIS or reporting roles, basic Power BI without DAX may be enough. But for any role titled “BI Analyst”, “Power BI Developer”, or “Analytics Consultant”, DAX proficiency is expected. Indian job postings for Power BI roles increasingly list specific DAX functions (CALCULATE, time intelligence) as requirements. DAX is tested in interviews and on the PL-300 certification exam.

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

A calculated column is evaluated row-by-row when data is loaded and stored in the data model — it adds a new column to your table. A measure is evaluated at query time based on the current filter context (slicers, filters, row/column in a visual). Use calculated columns for row-level categorisation or lookups. Use measures for aggregations, percentages, and any value that should change when a user interacts with the dashboard.

How long does it take to learn DAX?

Basic DAX (SUM, COUNT, IF): 1-2 weeks. Intermediate DAX (CALCULATE, ALL, time intelligence): 4-6 weeks of focused practice. Advanced DAX (complex filter context, virtual tables, optimisation): 2-3 months. The key is writing measures daily against real datasets, not just reading documentation. Build a practice dashboard and add one new measure every day.

Do time intelligence functions work without a date table?

No. Functions like TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, and DATESYTD require a dedicated date table — a continuous table of dates with no gaps that is marked as a date table in your Power BI data model. Using date columns from your fact table directly will produce incorrect results or errors. Creating a proper date table is step one before writing any time intelligence measure.

Is DAX similar to Excel formulas?

DAX syntax looks similar to Excel (function names like SUM, IF, and FORMAT exist in both), but the underlying engine is fundamentally different. Excel formulas operate on individual cells. DAX operates on entire columns and tables, with filter context determining which rows participate in a calculation. The biggest mental shift: in DAX, you do not reference cell addresses (A1, B2). You reference columns (Table[Column]) and the filter context determines the result.

What is filter context in DAX?

Filter context is the set of active filters applied to a calculation at any given moment. When a user selects “2026” in a year slicer and “North” in a region slicer, every measure on the dashboard is evaluated within that filter context — only rows matching 2026 and North are included. CALCULATE modifies filter context. ALL removes filter context. Understanding filter context is the single most important concept in DAX, more important than memorising individual functions.

Can I use DAX in Excel?

Yes. DAX is used in Excel Power Pivot and Excel data models. If you create a Power Pivot table in Excel and add measures, you write DAX. The syntax is identical to Power BI DAX. Learning DAX for Power BI simultaneously makes you proficient in Excel Power Pivot — two skills for the price of one.

Conclusion

DAX is not optional for Power BI analysts in 2026 — it is the skill that determines whether you build dashboards that display data or dashboards that drive decisions. The 20 functions in this cheat sheet — from foundational aggregations (SUM, AVERAGE, COUNT) through filter context manipulation (CALCULATE, ALL, ALLEXCEPT) to time intelligence (TOTALYTD, SAMEPERIODLASTYEAR) — cover 90% of what companies actually need from their BI teams. Power BI holds 36% market share in India and growing. The PL-300 certification is increasingly required. And the salary gap between visual-only users and DAX-proficient analysts is 30% or more.

The best way to learn DAX is not to memorise syntax — it is to build a dashboard with real data and add one new measure every day. Start with SUM and AVERAGE. Move to CALCULATE + ALL for percentage calculations. Add TOTALYTD and SAMEPERIODLASTYEAR for time comparisons. Within 6 weeks of daily practice, you will be writing measures that most analysts in India cannot.

Chat with a GrowAI Counsellor on WhatsApp