• Antriksh Sharma

SQL to DAX: Select/Project Columns

Updated: Aug 3

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:


30 views0 comments

Recent Posts

See All

Tabular Editor 3 Course

TE3 is the ultimate development tool for creating Tabular Model and recently I got an opportunity to make a full fledged course on it for Enterprise DNA. Here is a post about that and how you can secu