DAX vs SQL: A Translation Guide for SQL Users
· 7 min read
The core difference in DAX vs SQL: in SQL you write the whole query, including the GROUP BY. In DAX you usually write only the aggregate (a measure), and the report visual supplies the grouping and filters. The same measure then answers "revenue by category", "revenue by month" and "revenue for West" without being rewritten.
-- The measure: the "SELECT SUM(...) WHERE ..." part
Completed Revenue =
CALCULATE ( SUM ( Sales[Line Revenue] ), Sales[Status] = "completed" )
-- A DAX query: the full equivalent of a SQL GROUP BY
EVALUATE
SUMMARIZECOLUMNS ( Products[Category], "Completed Revenue", [Completed Revenue] )Below: the same question in both languages on Brightline Supply Co. data, a mapping table from SQL clauses to DAX, and the translation mistake AI assistants make when they convert SQL to DAX.
The example data
Brightline's model is a star schema: a Sales fact table with one row per order line, related to Products, Customers and Date. In SQL these would be four tables joined by keys. In Power BI the joins are defined once, as relationships.
| OrderID | ProductID | Category (from Products) | Status | Line Revenue |
|---|---|---|---|---|
| 50001 | 201 | Coffee & Kitchen | completed | 96.00 |
| 50002 | 101 | Paper & Print | completed | 49.50 |
| 50003 | 303 | Cleaning | completed | 75.00 |
| 50004 | 103 | Paper & Print | completed | 112.10 |
| 50005 | 301 | Cleaning | cancelled | 84.00 |
| 50006 | 204 | Coffee & Kitchen | completed | 99.50 |
| 50007 | 303 | Cleaning | completed | 180.00 |
| 50008 | 201 | Coffee & Kitchen | refunded | 48.00 |
Same question, both languages
"Completed revenue by product category."
In SQL:
SELECT p.category,
SUM(s.line_revenue) AS completed_revenue
FROM sales AS s
JOIN products AS p ON p.product_id = s.product_id
WHERE s.status = 'completed'
GROUP BY p.category;In DAX, as a query you can run in Power BI Desktop's DAX query view:
EVALUATE
SUMMARIZECOLUMNS (
Products[Category],
"Completed Revenue", CALCULATE ( SUM ( Sales[Line Revenue] ), Sales[Status] = "completed" )
)
ORDER BY Products[Category]| Category | Completed Revenue |
|---|---|
| Cleaning | 255.00 |
| Coffee & Kitchen | 195.50 |
| Paper & Print | 161.60 |
Notice what is missing from the DAX: there is no JOIN. Products[Category] filters Sales through the relationship. And in a report you would not write the query at all. You drag Products[Category] onto a table visual and add the measure. Power BI generates a SUMMARIZECOLUMNS query like the one above for you.
DAX for SQL users: the mapping
| SQL | DAX |
|---|---|
SUM(col), AVG(col), MIN, MAX | SUM, AVERAGE, MIN, MAX |
SUM(a * b) | SUMX ( Table, Table[a] * Table[b] ) |
COUNT(*) | COUNTROWS ( Table ) |
COUNT(DISTINCT col) | DISTINCTCOUNT ( Table[col] ) |
WHERE | Filter arguments of CALCULATE, or FILTER |
JOIN | Relationships in the model, RELATED for row-level lookups |
GROUP BY | Columns on the visual, or SUMMARIZECOLUMNS in a query |
HAVING | FILTER over a grouped table |
CASE WHEN | SWITCH ( TRUE (), ... ) or IF |
ORDER BY ... LIMIT n | TOPN ( n, table, expression ) |
RANK() OVER (...) | RANKX |
SUM(...) OVER (ORDER BY date) | TOTALYTD, or CALCULATE with a date filter |
LEFT JOIN ... WHERE b.key IS NULL | FILTER ( Customers, ISBLANK ( [Revenue] ) ) |
Each row of this table has its own guide: CALCULATE for WHERE, SUM vs SUMX for row-level arithmetic, RELATED for joins, IF and SWITCH for CASE WHEN, and the date table for running totals over time.
The DAX equivalent of GROUP BY
There are three ways to group in a DAX query:
-- 1. SUMMARIZECOLUMNS: the default, and what visuals use
EVALUATE
SUMMARIZECOLUMNS (
Products[Category],
Customers[Region],
"Completed Revenue", [Completed Revenue]
)
-- 2. SUMMARIZE for the groups, ADDCOLUMNS for the values
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( Sales, Products[Category] ),
"Completed Revenue", [Completed Revenue]
)
-- 3. GROUPBY with CURRENTGROUP, for aggregating rows directly
EVALUATE
GROUPBY (
Sales,
Products[Category],
"Line Revenue", SUMX ( CURRENTGROUP (), Sales[Line Revenue] )
)The first two return the same table for Completed Revenue. The third has no status filter, so its numbers include cancelled and refunded lines (Cleaning 339.00, Coffee & Kitchen 243.50, Paper & Print 161.60). Two differences from SQL to remember:
- SUMMARIZECOLUMNS drops groups where every value is BLANK, much like an inner join would. A category with no completed sales disappears unless you handle blanks.
- Grouping by several columns from different tables works without a join, as long as they are connected to the fact table. The SQL version needs both joins. See GROUP BY multiple columns in SQL.
HAVING in DAX
SQL filters groups with HAVING:
SELECT p.category, SUM(s.line_revenue) AS completed_revenue
FROM sales AS s
JOIN products AS p ON p.product_id = s.product_id
WHERE s.status = 'completed'
GROUP BY p.category
HAVING SUM(s.line_revenue) > 170;In DAX you build the grouped table, then FILTER it:
EVALUATE
FILTER (
SUMMARIZECOLUMNS ( Products[Category], "Completed Revenue", [Completed Revenue] ),
[Completed Revenue] > 170
)Both return Cleaning (255.00) and Coffee & Kitchen (195.50). The WHERE/HAVING distinction maps cleanly: CALCULATE filters act before aggregation (WHERE), FILTER on the grouped table acts after (HAVING). More on that in HAVING vs WHERE.
The biggest mental shift: filter context
A SQL query runs once and returns one result. A DAX measure runs once per cell of the visual. For each cell, Power BI sets up a filter context: the category on that row, the slicer selections, the page filters. The measure is evaluated inside it.
That is why a measure has no FROM or WHERE of its own for the grouping. It is also why relationships replace most joins: a filter on Products[Category] flows to Sales automatically, and you never get the duplicated rows a careless SQL join produces when it counts order lines instead of orders (see COUNT DISTINCT in SQL).
Common mistakes
An AI assistant translates the SQL query into a measure
Paste the SQL above into an AI assistant and ask for "the DAX measure", and you often get:
Completed Revenue =
SUMMARIZE (
FILTER ( Sales, Sales[Status] = "completed" ),
Products[Category],
"Revenue", SUM ( Sales[Line Revenue] )
)This fails with "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." A measure must return one value, not a table. The assistant copied the GROUP BY into the measure. How to spot it: if a measure contains SUMMARIZE, SUMMARIZECOLUMNS or GROUPBY at the top level, it is a query, not a measure. Keep only the aggregate and filter (CALCULATE ( SUM ( ... ), Sales[Status] = "completed" )) and let the visual group by category.
Translating WHERE into FILTER over the whole table
The second translation habit is CALCULATE ( SUM ( Sales[Line Revenue] ), FILTER ( ALL ( Sales ), Sales[Status] = "completed" ) ). In SQL, WHERE never removes your GROUP BY. In DAX, ALL ( Sales ) removes the category filter coming from the visual, so every row shows 612.10. Filter the column instead.
Expecting ORDER BY to sort the visual
ORDER BY in a DAX query only sorts the query result. In a report, sorting is a property of the visual (or of the column's "sort by column" setting).
FAQ
Is DAX harder than SQL?
The syntax is simpler, but the evaluation model is new for SQL users. Once filter context and context transition make sense, most of DAX is short formulas.
Can I use SQL in Power BI?
Yes, when you import data you can write a SQL query against a database source, and Power Query can push steps down to the source. But measures in the model are written in DAX.
What is the DAX equivalent of GROUP BY?
SUMMARIZECOLUMNS in a DAX query. In a report, the columns you put on a visual's rows or axis are the GROUP BY, and measures are the aggregates.
Should I learn SQL or DAX first?
SQL first if you can. Most analyst roles query databases, and understanding joins and grouping makes DAX's relationships and filter context easier to follow.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Units sold by region” →