top of page

SQL to DAX: Select/Project Columns

Updated: Aug 3, 2021

In this blog let's understand the difference between selecting columns in SQL vs DAX, i am using a fairly simple Product table which contains a limited column but enough for supporting the examples:

To compare the results I am going to SQL Server Management Studio and DAX Studio


Selecting a Table


The most basic query that you can write in DAX and SQL is just simply selecting the whole table:


SQL:


DAX:


Bonus: If you are into DAX optimizations then you would be aware about xmSQL ( Pseudo SQL code that is executed against the storage engine to extract data caches, this internally is in the form of binary but has been converted into human readable format by DAX Studio )


You can see that the simple selection of a table in DAX internally executes an xmSQL query that requests for all of the columns from the Products table.


The row number is used internally by DAX engines to optimized the query.


Selecting a single or multiple columns


To select multiple or single column you would write a following query in SQL:

DAX equivalent:

DAX Storage Engine queries:


Selecting unique rows


To select unique rows for the column specified in SQL you would write:

Now there are way too many ways of getting unique rows in DAX, so let's cover them all with the difference.


ALL

Query Plan: Notice there is no DISTINCT in the xmSQL, the reason is DISTINCT/GROUP BY is implicit in xmSQL query


DISTINCT


This time I have wrapped the SELECTCOLUMNS inside DISTINCT

Query Plan:

SUMMARIZE

Query Plan:

SUMMARIZECOLUMNS

Query Plans:


592 views1 comment

1 Comment


Hi Antriksh, Based on this example you wrote do you think DAX can convert these TSQL code? /*

The query grabs the first time for each ID (the first SELECT, before the UNION ALL)

and then recurses, finding the next row for each ID which is at least 15 minutes appart.

*/

-- the delta reset when rowcount reach 15 min - not a windows of 15 min

WITH times AS (

SELECT e.ID, MIN(e.DTTM) AS DTTM

FROM example e

GROUP BY e.ID

UNION ALL

SELECT r.ID, r.DTTM

FROM (

SELECT e.ID, e.DTTM,

ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.DTTM) AS rn

FROM example e

INNER JOIN times t

ON e.ID = t.ID AND e.DTTM >= DATEADD(mi, 15, t.DTTM)

) r


Like
bottom of page