When QueryMode is set to DAX, the provider translates SQL queries into DAX (Data Analysis Expressions) for execution against tabular data models. This page provides a comprehensive reference for how SQL constructs map to DAX queries.
SELECT Queries
The SELECT statement maps to different DAX functions depending on the query structure. Simple table scans and column projections use
EVALUATE and
SELECTCOLUMNS:
| |
| SQL Query | DAX Query |
SELECT * FROM Sales |
EVALUATE 'Sales' |
SELECT EmpName, Title
FROM Employees |
EVALUATE
SELECTCOLUMNS(
'Employees',
"EmpName", 'Employees'[EmpName],
"Title", 'Employees'[Title]
) |
SELECT EmpName AS Name, Title AS Role
FROM Employees |
EVALUATE
SELECTCOLUMNS(
'Employees',
"Name", 'Employees'[EmpName],
"Role", 'Employees'[Title]
) |
WHERE Filters
WHERE conditions are translated to DAX
FILTER expressions. The provider automatically determines whether values need quoting based on the column data type: string values are quoted with double quotes, while numeric, boolean, and date values are unquoted.
| |
| SQL Query | DAX Query |
SELECT EmpName
FROM Employees
WHERE Title = 'Rep' |
EVALUATE
SELECTCOLUMNS(
FILTER('Employees',
'Employees'[Title] = "Rep"),
"EmpName", 'Employees'[EmpName]
) |
SELECT * FROM Sales
WHERE Amount > 100 |
EVALUATE
SELECTCOLUMNS(
FILTER('Sales', 'Sales'[Amount] > 100),
"col1", 'Sales'[col1],
"coln", 'Sales'[coln],
) |
SELECT City FROM Stores
WHERE Country = 'USA'
OR Country = 'UK' |
EVALUATE
SELECTCOLUMNS(
FILTER('Stores',
'Stores'[Country] = "USA"
|| 'Stores'[Country] = "UK"),
"City", 'Stores'[City]
) |
SELECT City FROM Stores
WHERE City IN ('NYC', 'London') |
EVALUATE
SELECTCOLUMNS(
FILTER('Stores',
'Stores'[City] IN {"NYC", "London"}),
"City", 'Stores'[City]
) |
SELECT City FROM Stores
WHERE Country NOT IN ('UK') |
EVALUATE
SELECTCOLUMNS(
FILTER('Stores',
NOT('Stores'[Country] IN {"UK"})),
"City", 'Stores'[City]
) |
SELECT EmpName FROM Employees
WHERE Title IS NOT NULL |
EVALUATE
SELECTCOLUMNS(
FILTER('Employees',
'Employees'[Title] <> BLANK()),
"EmpName", 'Employees'[EmpName]
) |
Note: LIKE filters are not natively supported in DAX. The provider retrieves all rows and applies LIKE filtering on the client side.
Measure Queries
In DAX mode, pre-defined model measures are exposed in a virtual
Measures table. Querying measures uses
SUMMARIZECOLUMNS, which leverages the model's built-in table relationships to automatically resolve data slicing.
| |
| SQL Query | DAX Query |
SELECT m.[Total Amount]
FROM Measures m |
EVALUATE
SUMMARIZECOLUMNS(
"Total Amount", [Total Amount]
) |
SELECT s.Country, m.[Total Amount]
FROM Stores s
CROSS JOIN Measures m |
EVALUATE
SUMMARIZECOLUMNS(
'Stores'[Country],
"Total Amount", [Total Amount]
) |
SELECT e.Title, s.Country,
m.[Total Amount], m.[Total Units]
FROM Employees e
CROSS JOIN Stores s
CROSS JOIN Measures m |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[Title],
'Stores'[Country],
"Total Amount", [Total Amount],
"Total Units", [Total Units]
) |
SELECT e.EmpName, m.[Total Amount]
FROM Employees e
CROSS JOIN Measures m
WHERE e.Title = 'Rep' |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[EmpName],
FILTER('Employees',
'Employees'[Title] = "Rep"),
"Total Amount", [Total Amount]
) |
Many BI tools require explicit JOIN syntax with an ON clause. The provider adds a virtual MeasuresId column to every table for this purpose. An INNER JOIN on MeasuresId is treated identically to a CROSS JOIN with the Measures table:
| |
| SQL Query | DAX Query |
SELECT s.Country, m.[Total Amount]
FROM Stores s
INNER JOIN Measures m
ON s.MeasuresId = m.MeasuresId |
EVALUATE
SUMMARIZECOLUMNS(
'Stores'[Country],
"Total Amount", [Total Amount]
) |
Implicit Aggregations
SQL aggregate functions applied to regular table columns are translated to their native DAX equivalents. When no GROUP BY is present, the result is a single scalar row using
ROW. When GROUP BY is specified,
SUMMARIZECOLUMNS is used.
| |
| SQL Query | DAX Query |
SELECT SUM(Amount) FROM Sales |
EVALUATE
ROW("SUM", SUM('Sales'[Amount])) |
SELECT COUNT(*) FROM Sales |
EVALUATE
ROW("COUNT", COUNTROWS('Sales')) |
SELECT COUNT(Amount) FROM Sales |
EVALUATE
ROW("COUNT", COUNT('Sales'[Amount])) |
SELECT s.Country, SUM(sa.Amount)
FROM Stores s
INNER JOIN Sales sa
ON s.StoreID = sa.StoreID
GROUP BY s.Country |
EVALUATE
SUMMARIZECOLUMNS(
'Stores'[Country],
"SUM", SUM('Sales'[Amount])
) |
SELECT s.Country,
AVG(sa.Amount),
MIN(sa.Units),
MAX(sa.Amount)
FROM Stores s
INNER JOIN Sales sa
ON s.StoreID = sa.StoreID
GROUP BY s.Country |
EVALUATE
SUMMARIZECOLUMNS(
'Stores'[Country],
"AVG", AVERAGE('Sales'[Amount]),
"MIN", MIN('Sales'[Units]),
"MAX", MAX('Sales'[Amount])
) |
When SUM aggregate function contains an arithmetic expression rather than a single column, the provider uses the SUMX function:
| |
| SQL Query | DAX Query |
SELECT e.EmpName,
SUM(sa.Amount * sa.Units)
FROM Employees e
INNER JOIN Sales sa
ON e.EmpID = sa.EmpID
GROUP BY e.EmpName |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[EmpName],
"SUM", SUMX('Sales',
'Sales'[Amount] * 'Sales'[Units])
) |
The following table summarizes the SQL-to-DAX aggregate function mapping:
| |
| SQL Function | DAX Function |
SUM(col) |
SUM('Table'[col]) |
COUNT(*) |
COUNTROWS('Table') - Counts all rows |
COUNT(col) |
COUNT('Table'[col]) - Ignores blanks |
COUNT(DISTINCT col) |
DISTINCTCOUNT('Table'[col]) |
AVG(col) |
AVERAGE('Table'[col]) |
MIN(col) |
MIN('Table'[col]) |
MAX(col) |
MAX('Table'[col]) |
SUM(expr) |
SUMX('Table', expr) - Iterator version for expressions |
JOIN Queries
The provider supports INNER JOIN, LEFT JOIN, and CROSS JOIN in DAX mode. Each maps to a different DAX pattern depending on whether the query involves aggregation.
INNER JOIN
Non-aggregated INNER JOINs use
NATURALINNERJOIN, which relies on model relationships to merge tables:
| |
| SQL Query | DAX Query |
SELECT e.EmpName, sa.Amount
FROM Employees e
INNER JOIN Sales sa
ON e.EmpID = sa.EmpID |
EVALUATE
SELECTCOLUMNS(
NATURALINNERJOIN('Employees', 'Sales'),
"EmpName", 'Employees'[EmpName],
"Amount", 'Sales'[Amount]
) |
SELECT e.EmpName, s.City, sa.Amount
FROM Employees e
INNER JOIN Sales sa
ON e.EmpID = sa.EmpID
INNER JOIN Stores s
ON sa.StoreID = s.StoreID |
EVALUATE
SELECTCOLUMNS(
NATURALINNERJOIN(
NATURALINNERJOIN(
'Employees', 'Sales'),
'Stores'),
"EmpName", 'Employees'[EmpName],
"City", 'Stores'[City],
"Amount", 'Sales'[Amount]
) |
LEFT JOIN
LEFT JOINs with aggregation use
ADDCOLUMNS combined with
VALUES and
CALCULATE to preserve rows from the left table that have no matching data:
| |
| SQL Query | DAX Query |
SELECT e.EmpName, SUM(sa.Amount)
FROM Employees e
LEFT JOIN Sales sa
ON e.EmpID = sa.EmpID
GROUP BY e.EmpName |
EVALUATE
ADDCOLUMNS(
VALUES('Employees'[EmpName]),
"SUM", CALCULATE(SUM('Sales'[Amount]))
) |
CROSS JOIN
CROSS JOINs between regular tables (not involving the Measures table) use
GENERATE to produce a Cartesian product:
| |
| SQL Query | DAX Query |
SELECT e.EmpName, s.City
FROM Employees e
CROSS JOIN Stores s |
EVALUATE
GENERATE(
SELECTCOLUMNS('Employees',
"EmpName", 'Employees'[EmpName]),
SELECTCOLUMNS('Stores',
"City", 'Stores'[City])
) |
Note: RIGHT JOIN and FULL OUTER JOIN are not supported in DAX mode. The provider returns an error if these join types are used.
HAVING Clause
HAVING filters are applied after aggregation by wrapping the
SUMMARIZECOLUMNS expression in a
FILTER:
| |
| SQL Query | DAX Query |
SELECT s.Country, m.[Total Amount]
FROM Stores s
CROSS JOIN Measures m
HAVING m.[Total Amount] > 500 |
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
'Stores'[Country],
"Total Amount", [Total Amount]
),
[Total Amount] > 500
) |
SELECT s.City, SUM(sa.Amount)
FROM Stores s
INNER JOIN Sales sa
ON s.StoreID = sa.StoreID
GROUP BY s.City
HAVING SUM(sa.Amount) > 500
AND COUNT(sa.Units) < 10 |
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
'Stores'[City],
"SUM", SUM('Sales'[Amount])
),
([SUM] > 500 && COUNT('Sales'[Units]) < 10)
) |
ORDER BY
ORDER BY is translated to the DAX
ORDER BY clause:
| |
| SQL Query | DAX Query |
SELECT EmpName, Title
FROM Employees
ORDER BY Title ASC |
EVALUATE
SELECTCOLUMNS(
'Employees',
"EmpName", 'Employees'[EmpName],
"Title", 'Employees'[Title]
)
ORDER BY [Title] ASC |
SELECT e.EmpName, m.[Total Amount]
FROM Employees e
CROSS JOIN Measures m
ORDER BY m.[Total Amount] DESC |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[EmpName],
"Total Amount", [Total Amount]
)
ORDER BY [Total Amount] DESC |
If the ORDER BY column is not in the SELECT list, the provider automatically includes it in the DAX projection so it is available for sorting.
LIMIT and OFFSET
LIMIT is translated to DAX's
TOPN function, which executes server-side:
| |
| SQL Query | DAX Query |
SELECT EmpName FROM Employees
ORDER BY EmpName DESC
LIMIT 2 |
EVALUATE
TOPN(2,
SELECTCOLUMNS(
'Employees',
"EmpName", 'Employees'[EmpName]
),
[EmpName], DESC
) |
SELECT EmpID FROM Employees
LIMIT 2 |
EVALUATE
TOPN(2,
SELECTCOLUMNS(
'Employees',
"EmpID", 'Employees'[EmpID]
)
) |
DAX does not have a native OFFSET. The provider handles offset by requesting LIMIT + OFFSET rows via TOPN and discarding the initial OFFSET rows before returning results.
DISTINCT
DISTINCT is handled server-side:
| |
| SQL Query | DAX Query |
SELECT DISTINCT Title
FROM Employees |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[Title]
) |
Queries that use SUMMARIZECOLUMNS (such as measure queries or GROUP BY) already return distinct results, so DISTINCT is not applied redundantly in those cases.
Subquery IN Clause
Subqueries in IN clauses are translated server-side:
| |
| SQL Query | DAX Query |
SELECT e.EmpName
FROM Employees e
WHERE e.EmpID IN (
SELECT EmpID FROM Sales
WHERE Amount > 300
) |
EVALUATE
SELECTCOLUMNS(
FILTER('Employees',
'Employees'[EmpID] IN
SELECTCOLUMNS(
FILTER('Sales',
'Sales'[Amount] > 300),
"EmpID", 'Sales'[EmpID]
)
),
"EmpName", 'Employees'[EmpName]
) |
COUNT(DISTINCT)
COUNT(DISTINCT) maps to the DAX
DISTINCTCOUNT function:
| |
| SQL Query | DAX Query |
SELECT e.Title,
COUNT(DISTINCT sa.EmpID)
FROM Employees e
INNER JOIN Sales sa
ON e.EmpID = sa.EmpID
GROUP BY e.Title |
EVALUATE
SUMMARIZECOLUMNS(
'Employees'[Title],
"COUNT", DISTINCTCOUNT('Sales'[EmpID])
) |