DAX for Beginners: 12 Power BI Functions Explained With One Sales Model

In almost every DAX for beginners class, someone asks a version of this in the second week: "Why does my total change every time I click a slicer, and how do I make one number ignore it?" The answer is DAX itself, and it gets much easier once you see that a dozen functions do most of the daily work.

This explainer walks through those 12 functions using one small practice model, so every formula connects to the one before it. You can rebuild the whole thing in the free Power BI Desktop app.

In this guide, you'll learn:

• What DAX is and how a measure differs from a calculated column
• The counting, ratio and row-by-row functions behind most report cards
• How CALCULATE changes filters, and how to remove one on purpose
• How to set up time intelligence for Pakistan's July to June financial year
• A sensible order for learning the rest

What Is DAX, and Why Does Power BI Need It?

DAX stands for Data Analysis Expressions. It's the formula language you use inside Power BI to create measures, calculated columns and calculated tables. If you've written Excel formulas, the syntax will look familiar. Microsoft's guide to measures in Power BI Desktop says the DAX library holds over 200 functions, operators and constructs, and that they're built for related tables rather than a single grid of cells.

That last point is the real difference from Excel. An Excel formula points at cells. A DAX formula points at columns and tables, and the result depends on which filters are active when a visual asks for it. Click a branch in a slicer and every measure on the page recalculates for that branch. Nothing needs to be copied down. Most of DAX for beginners comes down to understanding those active filters.

Power BI can add up a numeric column on its own when you drag it onto a chart. Microsoft's tutorial on creating your own measures calls these automatic measures, with Sum as the default. They're fine for a quick look. As soon as you need net sales, a margin, a share of total or last year's figure, you write the DAX yourself, and that's where Power BI measures come in.

The Practice Model Used in This Guide

To keep things concrete, picture this example: a rice and cooking-oil distributor with three branches, in Abbottabad, Mansehra and Haripur. The owner keeps sales in Excel and wants a dashboard. Your model has three tables:

Sales links to Products through ProductID and to Date through OrderDate. Both are many-to-one relationships: many invoice lines point to one product and one day. Keep this shape in mind, because several functions below only work when these links exist.

Measure or Calculated Column: Which Should You Write?

The calculated column vs measure choice is the first decision you make, and beginners often get it backwards. A calculated column works row by row and gives every row of a table its own stored value. A measure doesn't belong to any row. It's worked out when a visual needs it, using whatever filters the report has at that moment.

A simple rule works for most cases. If the number should respond to slicers and filters, such as total sales, average order value or profit margin, write a measure. If you need a label you can put on an axis, a slicer or a row heading, such as a price band like "Under Rs. 1,000", write a calculated column.

When in doubt, start with a measure. Most of the numbers a manager asks for are totals and ratios, and those belong in measures.

Counting and Adding: SUM, COUNTROWS and DISTINCTCOUNT

These three answer the most common dashboard questions: how much, how many lines and how many different things.

SUM

SUM adds up one numeric column in the current filter context. Here's a measure for total units sold:

Units Sold = SUM ( Sales[Qty] )

SUM only accepts a column. You can't multiply two columns inside it, which is why SUMX appears later in this guide.

COUNTROWS

COUNTROWS counts the rows of a table. Because each Sales row is an invoice line, this gives you the number of lines sold:

Invoice Lines = COUNTROWS ( Sales )

DISTINCTCOUNT

DISTINCTCOUNT counts unique values in a column. The distributor wants to know how many different shops bought from each branch this month:

Active Customers = DISTINCTCOUNT ( Sales[CustomerID] )

Put all three in a table visual next to Branch and you already have a useful first report.

Safe Ratios With DIVIDE

Ratios cause beginners more errors than anything else, usually because a branch or month has no sales and the formula tries to divide by zero. DIVIDE handles that for you. Microsoft's DAX best-practice note on DIVIDE explains that it returns BLANK when the denominator is zero or BLANK, unless you supply a third argument as an alternate result.

Avg Units per Line = DIVIDE ( [Units Sold], [Invoice Lines] )

The same note recommends DIVIDE over a longer IF test because it's shorter and better optimized. It also suggests keeping the plain / operator when you divide by a constant, such as [Units Sold] / 12, since that division can't fail. Leaving the result as BLANK is usually the better design too: visuals hide blank groups by default, so empty branches don't clutter the chart.

Row-by-Row Math With SUMX and RELATED

The Sales table has quantity and unit price but no revenue column. You could add one as a calculated column, but an iterator is cleaner.

SUMX

According to Microsoft's SUMX reference page, SUMX takes a table, evaluates an expression for each row, then adds the results. It only counts numbers and ignores blanks, text and logical values.

Revenue =
SUMX ( Sales, Sales[Qty] * Sales[UnitPrice] - Sales[Discount] )

The page also advises using plain SUM when you don't need a row-by-row calculation. Treat SUMX as the tool for "multiply first, then add".

RELATED

Profit needs the cost price, which sits in the Products table. RELATED fetches a value from the "one" side of a many-to-one relationship. The RELATED documentation notes that it needs a row context, which means it works in a calculated column or inside an iterator such as SUMX or FILTER.

Gross Profit =
SUMX (
    Sales,
    Sales[Qty] * ( Sales[UnitPrice] - RELATED ( Products[CostPrice] ) )
        - Sales[Discount]
)

If RELATED returns an error, check the relationship between Sales and Products first. In class, a missing relationship is the usual cause. Now a margin measure is one line: Margin % = DIVIDE ( [Gross Profit], [Revenue] ).

CALCULATE: How Do You Change the Filters on a Number?

This is the function that answers the question at the top of this guide. Microsoft's CALCULATE reference describes it as evaluating an expression in a modified filter context. Each filter you pass is added if that column isn't filtered yet, and replaces the existing filter if it is.

Cooking Oil Revenue =
CALCULATE ( [Revenue], Products[Category] = "Cooking Oil" )

Place this next to Branch and each row shows cooking-oil revenue for that branch. The branch filter stays. Only the category filter is forced.

FILTER for Conditions on a Measure

A simple column condition, like the one above, goes straight into CALCULATE. When the condition is more complex, such as "products whose revenue is above Rs. 500,000", wrap a table in FILTER and pass that instead:

Revenue From Big Sellers =
CALCULATE (
    [Revenue],
    FILTER ( VALUES ( Products[ProductName] ), [Revenue] > 500000 )
)

REMOVEFILTERS for Share of Total

To show each branch's share of company revenue, the denominator must ignore the branch filter. REMOVEFILTERS does exactly that:

Branch Share % =
DIVIDE (
    [Revenue],
    CALCULATE ( [Revenue], REMOVEFILTERS ( Sales[BranchID] ) )
)

The CALCULATE reference lists REMOVEFILTERS beside ALL and ALLEXCEPT, and prefers REMOVEFILTERS where it's supported. If you'd rather add a filter without overwriting the one already there, wrap it in KEEPFILTERS. That's the full answer to "how do I make one number ignore the slicer".

Time Intelligence for a July to June Financial Year

Owners in Pakistan think in financial years. The tax year here runs from 1 July to 30 June, as the overview of taxation in Pakistan notes, so "year to date" in a sales meeting usually means since 1 July, not since 1 January. Time intelligence DAX handles this, but only with a proper date table.

Build the Date Table First

Microsoft's date table guidance lists the rules: one date column with unique values, no blanks, no missing dates, and full years. For classic time intelligence, you also mark it as a date table. CALENDARAUTO builds one from every date in your model, and its optional argument sets the last month of the year, so 6 fits a June year-end:

Date = CALENDARAUTO ( 6 )

If you'd rather set the range yourself, CALENDAR takes a start and an end date. Either way, add Month, Quarter and FiscalYear columns afterwards, then link Date[Date] to Sales[OrderDate].

TOTALYTD

TOTALYTD returns a running total from the start of the year. The TOTALYTD reference shows an optional year-end argument written as "month/day", which defaults to 31 December. For a Pakistani business, pass "6/30":

Revenue FYTD = TOTALYTD ( [Revenue], 'Date'[Date], "6/30" )

SAMEPERIODLASTYEAR

For growth against last year, SAMEPERIODLASTYEAR shifts the current dates back by one year. Microsoft's SAMEPERIODLASTYEAR page says the result is the same as DATEADD with minus one year. You always use it inside CALCULATE:

Revenue LY =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Growth % = DIVIDE ( [Revenue] - [Revenue LY], [Revenue LY] )

Notice how Growth % reuses three earlier measures. That's the habit worth building: small, named measures that you combine, instead of one giant formula nobody can debug.

DAX for Beginners: The 12 Functions at a Glance

Here's the full set from this guide in one table. Keep it open while you practice.

In What Order Should You Learn DAX Functions?

A good DAX for beginners plan works in layers. Each layer depends on the one beneath it, and skipping ahead is why many people find CALCULATE confusing.

1. Week 1, aggregates: SUM, COUNTROWS, DISTINCTCOUNT and DIVIDE. Get comfortable with how a slicer changes their results.
2. Week 2, iterators: SUMX and RELATED. Learn what "row context" means by writing Revenue and Gross Profit yourself.
3. Weeks 3 and 4, filter control: CALCULATE, FILTER and REMOVEFILTERS. Build share-of-total and category measures until you can predict results before you press Enter.
4. Week 5, time: a marked date table, then TOTALYTD and SAMEPERIODLASTYEAR.

This order also follows Microsoft's exam outline. The PL-300 study guide, with skills effective from 20 April 2026, weights "Model the data" at 25 to 30% and lists single aggregation measures, the CALCULATE function and time intelligence measures under it. If certification is your goal, our earlier guide to Power BI certification in Pakistan covers the exam itself.

Quick measures can help along the way. Microsoft's tutorial points out that Power BI shows the DAX a quick measure writes, so you can create one, then read its formula and rewrite it by hand.

Which Mistakes Slow Beginners Down Most?

From marking student files, the same few problems come up again and again:

• Using a calculated column for a total. It won't respond to slicers the way you expect. Turn it into a measure.
• Relying on the automatic date hierarchy. Microsoft's date table guidance doesn't recommend Auto date/time for most models. Build your own Date table early.
• Writing one huge formula. Split it into named measures, as Growth % does above.
• Forgetting the relationship. RELATED and most cross-table filters fail quietly without one.
• Hard-coding the year-end. If the client runs a July to June year, every year-to-date measure needs "6/30".

Still weighing up tools? Read our Power BI vs Excel breakdown to see which job suits each. Many analysts also pull data with SQL first, which our SQL vs PL/SQL guide introduces.

What Does Power BI Cost While You Learn?

Nothing, for the part covered here. Microsoft's Power BI pricing page says Power BI Desktop costs nothing to download, and every DAX formula in this guide runs in Desktop. Sharing reports online with colleagues needs a paid license: the same page prices Pro at $14 a user each month and Premium Per User at $24, with yearly billing. For practice and a portfolio, Desktop is enough.

Practicing DAX on Real Datasets at PSTA

DAX clicks fastest when someone checks your measures against the numbers you expected. If you're looking for a Power BI course in Pakistan that works this way, our Power BI Course: Data Analytics & Dashboards is an 8-week, beginner-level program. Its modules move from data connectivity and Power Query into data modeling, a dedicated DAX module on measures and columns, dashboards, the Power BI Service and security, and they end with business case projects.

Tuition for this course is listed at Rs. 32,000 at the time of writing. Our campus sits in Mandian, Abbottabad, near FIMS College on Old College Road. If you live outside the city, live online seats are open too, and a new group begins monthly. If Excel itself still feels shaky, consider starting with the Office Automation course, which builds those basics first. To reserve a seat, use the admission form, browse all courses, or send your questions on WhatsApp to +92 314 5424399.

Rebuild the Practice Model From a Blank File

Here's one practical exercise to close this DAX for beginners guide. Open Power BI Desktop, type a 20-row Sales table and a five-row Products table by hand, link them, and write Units Sold, Revenue, Gross Profit and Branch Share % from this guide. Then add a slicer and check each result with a calculator. Once those four numbers match, the core ideas of DAX are working on your own data, and the rest of this list will follow much faster.