How much, how often, or how many distinct: choosing the right DAX aggregate

Almost every Power BI report starts with three questions: how much is the total, how often did something happen, and how many distinct things were involved. Each question has a matching DAX aggregate, and once you know which question you are actually asking, the function choice falls into place.

Take one Orders table with three columns: CustomerID, OrderID, and Amount. A stakeholder comes to you with three questions that look similar at first glance. Put them side by side, and they turn out to be three completely different calculations, where the right measure follows naturally once the question is precise.

How much: SUM

“How much revenue did we generate?” is a question about a single numeric column: what is the total of everything in it? That is exactly what SUM does.

Total Revenue = SUM(Orders[Amount])

SUM adds all values in the specified column within the current filter context. If you need to calculate something row by row before adding, like Quantity times Price, you use SUMX: it iterates row by row and then aggregates. For “what is the total of one existing column,” SUM is the shortest and most readable route.

How often: COUNTROWS

“How many orders were placed?” is a question about rows: every row in Orders represents one order, so you count the rows of the table.

Order Count = COUNTROWS(Orders)

COUNTROWS counts the number of rows in a table or table expression within the current filter context. Beginners often reach for COUNT instead, but COUNT works on a column and silently skips blank values. If one cell in the Amount column is empty, COUNT(Orders[Amount]) comes up one short, while COUNTROWS counts the rows themselves and stays reliable. That gap sounds small, but in a filtered report, one blank value can produce the wrong number at exactly the point where someone is making a decision based on it.

How many distinct: DISTINCTCOUNT

“How many unique customers were involved?” is the question that causes the most reporting mistakes, and it is about unique values in a column.

Unique Customers = DISTINCTCOUNT(Orders[CustomerID])

DISTINCTCOUNT counts the number of unique values in the specified column within the current filter context. Two things to know from the start. First, DISTINCTCOUNT counts a blank value as one unique value: to exclude that blank, use DISTINCTCOUNTNOBLANK. Second, DISTINCTCOUNT is semantically equivalent to COUNTROWS(DISTINCT(Orders[CustomerID])): both return the same result on the same data, but DISTINCTCOUNT is the shorthand for this specific case. The longer COUNTROWS(DISTINCT(…)) form shows up once you start building more complex expressions where you need to filter before counting.

Watch it happen in the table

Below is such an Orders table, small enough to count by hand. Click a measure and see which cells take part. One blank CustomerID and one blank Amount are planted on purpose: you get to watch both nuances from above happen instead of memorising them. Then set the slicer to a region and watch every number move.

Filter context connects all three

All three measures respond to filter context: they calculate their result within whatever filters are active when the visual renders. Put “Unique Customers” next to a country filter and DISTINCTCOUNT counts only the customers in that country, and the same measure in a different visual gives a different number. The same applies to SUM on Amount next to a month filter, or COUNTROWS on Orders next to a region filter.

That is why the choice between SUM, COUNTROWS, and DISTINCTCOUNT is not just a syntax question, it is a question question. Filter context decides which number you see each time, and only the right measure gives the right answer for the right question. Day 5 of the DAX basics series goes into this in depth.

The mental check

If a measure feels off, reframe the question underneath it first. How much, how often, or how many distinct? Those three prompts resolve nine out of ten beginner doubts before you write a single line of DAX.

The Microsoft Learn pages for SUM, COUNTROWS, and DISTINCTCOUNT are the primary references if you want the exact function definitions.

Want to work through the full DAX basics series? You will find the complete learning path at trainerbjorn.nl.