Basic SELECT Query with SUM and WHERE Clause
Input SQL:
SELECT SUM(SalesAmount) FROM Sales WHERE Year = 2023;Expected DAX Output:
CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2023)Explanation: In DAX, CALCULATE() is used to apply filters to an aggregation like SUM(). The SQL WHERE clause is converted into the filter argument of CALCULATE().
GROUP BY Query with SUM
Input SQL:
SELECT City, SUM(SalesAmount) FROM Sales GROUP BY City;Expected DAX Output:
SUMMARIZE(Sales, Sales[City], "Total Sales", SUM(Sales[SalesAmount]))Explanation: In DAX, SUMMARIZE() is used for grouping data. It groups the data by the City column and returns a summary of Total Sales for each city using the SUM() function.
Join Query (SQL INNER JOIN Equivalent)
Input SQL:
SELECT Customers.CustomerName, Sales.SalesAmount
FROM Customers
INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID;Expected DAX Output:
CALCULATE(SUM(Sales[SalesAmount]), RELATED(Customers[CustomerName]))Explanation: In DAX, relationships between tables are managed using the RELATED() function. The SQL INNER JOIN is implied by the relationships in the model, so DAX doesn’t explicitly require a JOIN.
DISTINCT Query
Input SQL:
SELECT DISTINCT ProductCategory FROM Products;Expected DAX Output:
VALUES(Products[ProductCategory])Explanation: The DAX function VALUES() returns distinct values from a column, which is equivalent to SQL’s SELECT DISTINCT operation.
COUNT with Multiple Conditions (AND in WHERE clause)
Input SQL:
SELECT COUNT(*) FROM Sales WHERE Year = 2023 AND Region = 'North';Expected DAX Output:
CALCULATE(COUNTROWS(Sales), Sales[Year] = 2023, Sales[Region] = "North")Explanation: In DAX, multiple conditions can be passed into CALCULATE(). COUNTROWS() is used to count the rows that satisfy the conditions (equivalent to SQL’s COUNT(*)). The SQL WHERE clause with AND is translated to multiple filter conditions inside CALCULATE().
SQL to DAX Conversion Table: Key Differences and Equivalents
Here is a list that outlines the key differences between SQL and DAX, highlighting how to convert common SQL queries and operations into their DAX equivalents.
| SQL Concept | SQL Syntax | DAX Equivalent | Explanation |
|---|---|---|---|
| Basic SELECT | SELECT column FROM table | CALCULATE(SUM(table[column])) | DAX uses CALCULATE to evaluate expressions and apply filters. |
| SUM Function | SELECT SUM(column) FROM table | SUM(table[column]) | DAX uses SUM() the same way as SQL, for aggregating values. |
| COUNT Function | SELECT COUNT(column) FROM table | COUNT(table[column]) or COUNTROWS(table) | COUNT() or COUNTROWS() are used in DAX to count values or rows. |
| DISTINCT | SELECT DISTINCT column FROM table | VALUES(table[column]) | DAX uses VALUES() to return distinct values. |
| GROUP BY | SELECT column, SUM(column2) FROM table GROUP BY column | SUMMARIZE(table, table[column], "Total", SUM(table[column2])) | DAX uses SUMMARIZE() to perform groupings. |
| WHERE Clause (Filter) | SELECT column FROM table WHERE condition | CALCULATE(SUM(table[column]), table[condition]) | DAX uses CALCULATE() to apply filters like the WHERE clause. |
| Multiple WHERE Conditions | SELECT column FROM table WHERE condition1 AND condition2 | CALCULATE(SUM(table[column]), table[condition1], table[condition2]) | Multiple conditions in WHERE are added as filters in CALCULATE(). |
| JOINs (INNER JOIN) | SELECT a.column, b.column FROM a INNER JOIN b ON a.key = b.key | CALCULATE(SUM(a[column]), RELATED(b[column])) | DAX uses RELATED() to fetch related data, implying a join. |
| LEFT JOIN | SELECT a.column, b.column FROM a LEFT JOIN b ON a.key = b.key | LOOKUPVALUE(b[column], b[key], a[key]) | DAX uses LOOKUPVALUE() to replicate a LEFT JOIN. |
| COUNT with Filters | SELECT COUNT(*) FROM table WHERE condition | CALCULATE(COUNTROWS(table), table[condition]) | COUNTROWS() in DAX counts rows while applying filters. |
| AVERAGE Function | SELECT AVG(column) FROM table | AVERAGE(table[column]) | DAX uses AVERAGE() for calculating the average. |
| MAX Function | SELECT MAX(column) FROM table | MAX(table[column]) | MAX() in DAX finds the maximum value from a column. |
| MIN Function | SELECT MIN(column) FROM table | MIN(table[column]) | MIN() in DAX finds the minimum value from a column. |
| LIMIT | SELECT column FROM table LIMIT n | N/A (handled by visual filtering in Power BI) | DAX doesn’t have a LIMIT function. Limit rows using filters in visuals. |
| ORDER BY | SELECT column FROM table ORDER BY column ASC/DESC | N/A (handled by sorting in visuals) | DAX doesn’t explicitly support ORDER BY. Sorting is managed by visuals. |
| CASE Statement | SELECT CASE WHEN condition THEN result ELSE result2 END FROM table | SWITCH(TRUE(), condition, result, condition2, result2, ...) | DAX uses SWITCH() for CASE logic. IF() can also be used for simple conditions. |
| IS NULL / IS NOT NULL | SELECT column FROM table WHERE column IS NULL | ISBLANK(table[column]) | DAX uses ISBLANK() to check for null values. |
| NOT NULL | SELECT column FROM table WHERE column IS NOT NULL | NOT(ISBLANK(table[column])) | DAX uses NOT(ISBLANK()) to check for non-null values. |
| COALESCE / IFNULL | SELECT COALESCE(column1, column2) FROM table | COALESCE(table[column1], table[column2]) | DAX also uses COALESCE() to return the first non-null value. |
| IF Statement | SELECT IF(condition, true_value, false_value) | IF(condition, true_value, false_value) | IF() functions are similar in both SQL and DAX. |
| UNION | SELECT column FROM table1 UNION SELECT column FROM table2 | UNION(table1, table2) | DAX uses the UNION() function to combine rows from two tables. |
| EXCEPT | SELECT column FROM table1 EXCEPT SELECT column FROM table2 | EXCEPT(table1, table2) | EXCEPT() in DAX returns rows present in the first table but not in the second. |
| INTERSECT | SELECT column FROM table1 INTERSECT SELECT column FROM table2 | INTERSECT(table1, table2) | INTERSECT() in DAX returns rows common to both tables. |
| DATE Functions | SELECT DATE(column) | DATE(year, month, day) | DAX uses DATE() to handle date columns similarly to SQL. |
| DATEADD Function | SELECT DATEADD(column, interval) | DATEADD(table[column], interval, "MONTH"/"YEAR"/"DAY") | Both SQL and DAX use DATEADD() to manipulate dates. |
| DATEPART Function | SELECT EXTRACT(YEAR FROM column) | YEAR(table[column]) | DAX uses YEAR(), MONTH(), and DAY() to extract date parts. |
| BETWEEN Operator | SELECT column FROM table WHERE column BETWEEN value1 AND value2 | CALCULATE(SUM(table[column]), table[column] >= value1, table[column] <= value2) | DAX uses CALCULATE() with multiple conditions for the BETWEEN operator. |
| IN Operator | SELECT column FROM table WHERE column IN (value1, value2) | CALCULATE(SUM(table[column]), table[column] IN {value1, value2}) | DAX uses IN {} for similar functionality to SQL’s IN operator. |