Home
Categories
Free Tools
Write For UsAbout UsContact

Power BI DAX Basics: Formulas Every Analyst Uses

Learn Power BI DAX basics: measures vs columns, row and filter context, and 12 formulas such as SUM, CALCULATE, DIVIDE, SAMEPERIODLASTYEAR and RANKX.

November 21, 2026 6 min read
Power BI DAX Basics: Formulas Every Analyst Uses

Key takeaways

  • Use measures, not calculated columns, for totals, ratios and anything that must respond to filters.
  • Filter context is the set of filters on a visual, and CALCULATE is the function that changes it.
  • Twelve formulas cover most needs: SUM, DISTINCTCOUNT, DIVIDE, IF, SWITCH, CALCULATE, ALL, SAMEPERIODLASTYEAR, TOTALYTD, DATEADD, RANKX, VAR.
  • Always use DIVIDE instead of the slash sign to avoid divide-by-zero errors.
  • Build a proper date table first, or time intelligence formulas will not work.

DAX, short for Data Analysis Expressions, is the formula language of Power BI. Most reports need only about a dozen formulas: SUM and DISTINCTCOUNT for totals, DIVIDE for ratios, CALCULATE to change filters, SAMEPERIODLASTYEAR and TOTALYTD for time comparisons, ALL for percentage of total, and RANKX for rankings. Learn those and you can handle most business questions.

DAX looks like Excel formulas but behaves differently, because it works on whole columns and responds to the filters on the page. If you are new to the tool, start with what Power BI is and our first-week plan.

What Is the Difference Between a Measure and a Calculated Column?

A calculated column is computed row by row when data loads and is stored in the model. A measure is computed on the fly when a visual needs it and responds to filters. Rule of thumb: if you want to sum, average, count or divide, write a measure. If you need a new value on every row, such as a category label, use a column. Measures keep the file smaller and the answers correct when filters change.

What Are Row Context and Filter Context?

Row context means “the current row”, as in a calculated column. Filter context means “the filters applied right now”, from slicers, rows and columns of a visual. A measure like SUM(Sales[Amount]) returns a different answer in each bar because each bar has a different filter context. Understanding this one idea clears up most DAX confusion. A small way to remember it: filter context is what the viewer has selected, and row context is the pointer in a table.

The 12 DAX Formulas Most Analysts Use

The examples assume a Sales table with Amount, OrderID and CustomerID, a Product table, and a Date table.

No.GoalDAX example
1Total salesTotal Sales = SUM(Sales[Amount])
2Unique ordersOrders = DISTINCTCOUNT(Sales[OrderID])
3Safe ratioAOV = DIVIDE([Total Sales],[Orders])
4Simple conditionStatus = IF([Total Sales]>=[Target],"On track","Behind")
5Many conditionsBand = SWITCH(TRUE(),[Total Sales]>=1000000,"High",[Total Sales]>=500000,"Mid","Low")
6Change a filterNorth Sales = CALCULATE([Total Sales],Region[Name]="North")
7Share of totalShare = DIVIDE([Total Sales],CALCULATE([Total Sales],ALL(Product)))
8Same period last yearSales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
9Year to dateSales YTD = TOTALYTD([Total Sales],'Date'[Date])
10Shift datesSales Last Month = CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH))
11Rank itemsRank = RANKX(ALL(Product[Name]),[Total Sales])
12VariablesMargin % = VAR Profit = [Total Sales]-[Total Cost] RETURN DIVIDE(Profit,[Total Sales])

How Does CALCULATE Work?

CALCULATE evaluates an expression in a modified filter context. The first argument is what to calculate, and the next arguments are the filters to apply. CALCULATE([Total Sales], Region[Name]="North") ignores the page’s region choice and forces North. It is the most important function in DAX, and once you are comfortable with it, ALL, FILTER and time intelligence all start to make sense.

A common beginner error is using FILTER on a whole table when a simple column condition would do. Prefer the simple form, which is faster and clearer.

How Do You Calculate Year-on-Year Growth?

Build it in steps so you can test each part.

StepMeasure
1Total Sales = SUM(Sales[Amount])
2Sales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
3YoY % = DIVIDE([Total Sales]-[Sales LY],[Sales LY])

These formulas need a date table with continuous dates, marked as a date table in the model. If your year-on-year column shows blank, check that the date table and relationship are set up correctly. Our data deduplication tool guide is useful when duplicate rows are inflating your totals.

Which DAX Mistakes Cause the Most Trouble?

MistakeSymptomFix
Using the slash sign instead of DIVIDEError or infinity when the denominator is zeroUse DIVIDE
No date tableTime intelligence returns blanksCreate and mark a date table
Calculated columns for totalsWrong totals when filteredUse a measure
Hard-coded numbersFormula breaks next yearUse a parameter or a column
Filtering a whole tableSlow, hard-to-read formulaFilter a single column
Many-to-many relationshipsUnexpected totalsRework the model to a star schema

How Can You Learn DAX Faster?

Write one formula a day on your own data, and always check the answer against Excel. Use DAX Query View in Power BI Desktop to run quick queries. Read Microsoft’s DAX reference and the free learning path, then try the formulas used by your team. Copilot and other AI helpers can draft DAX, but you should still understand the logic to catch errors; see our look at AI and Copilot in BI dashboards. Our guide to the best Power BI courses and certifications lists structured options.

Related read20 Power BI Dashboard Examples to Learn FromRead →

Free tools for Data & Analytics

FAQs

What is DAX in Power BI?

DAX stands for Data Analysis Expressions. It is the formula language used in Power BI, Excel Power Pivot and Analysis Services to create measures, calculated columns and tables. It looks like Excel formulas but works on columns and tables, and its results change with the filters applied to a report.

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

A calculated column is stored in the table and calculated row by row when data refreshes. A measure is calculated when a visual needs it and responds to filters. Use measures for totals, ratios and percentages, and use columns for row-level labels or values you need to slice by.

What does CALCULATE do in DAX?

CALCULATE evaluates an expression after changing the filters. For example, CALCULATE([Total Sales],Region[Name]="North") returns sales for the North region whatever the slicer says. It is the key function for time intelligence, percentage of total and any calculation that needs custom filters.

Do I need to know DAX to use Power BI?

You can build simple reports without it, because Power BI can create basic sums and counts automatically. But real analysis such as year-on-year growth, running totals and rankings needs DAX. Learning around a dozen formulas gives you most of the benefit.

Why does my DAX time intelligence formula return blank?

The usual cause is a missing or incorrect date table. Time intelligence functions need a continuous date table, marked as a date table, and a relationship to the date column in your fact table. Check that the date table covers all dates in your data and has no gaps.

Is DAX hard to learn for Excel users?

Excel users have a head start because many function names are similar. The hard part is the idea of filter context, which does not exist in Excel formulas. Practice with small examples and check each result against a pivot table. Most people feel comfortable within a few weeks.

Learn One Formula Well

Do not try to memorise every DAX function. Master CALCULATE and the date table, and you can build most of what a business needs. Then check how licences affect what you can share in Power BI pricing and licences explained.

Sources: Microsoft Learn: DAX overview; Microsoft Learn: DAX function reference; Microsoft Learn: Time intelligence functions

Was this guide helpful?
Jinal Mistry
Written by

Jinal Mistry

Jinal Mistry is the Founder of WritoryBuzz and an SEO & Digital Growth Specialist with 5+ years of experience in Technical SEO, AI SEO, International SEO, and organic growth strategy. She helps businesses improve search visibility through data-driven SEO, content optimization, search experience enhancement, and AI-focused search strategies.Having worked with clients across multiple industries and international markets, Jinal specializes in building sustainable organic growth through technical excellence, user-focused optimization, and future-ready digital marketing practices. Through WritoryBuzz, she shares insights on SEO, AI search, content strategy, digital marketing, and emerging search trends to help businesses and marketers stay ahead in the evolving digital landscape.

More from this author

More in Data & Analytics

View all in Data & Analytics →

Keep exploring

Have expertise to share? Write for WritoryBuzz.Read the guidelines and send us your pitch.
Become a contributor
Free tools

63 free tools for writers, SEOs and developers

Open one in your browser and start. Nothing to install, and your text stays on your device.

PrivateInstant resultsNo sign up
Write for us

Have expertise to share?

  • Original, well researched guides
  • Reviewed by our editors
  • 29 categories to publish in
Submit a guest post
© 2026 WritoryBuzz. All rights reserved.Back to top