How to write your SQL query in DAX?

Obviously T-SQL and DAX are two different query languages and it serves entirely different purposes.However, as a person who came from database development background , when I learn DAX initially, I always started thinking SQL way and tried to achieve same thing from DAX. The reason for that thinking process is, it is always easy to learn something new when we compare same thing with something we already know. So in this blog post, I try to cover very basic DAX statements with comparison with T-SQL. Again, the idea is start with T-SQL and try to obtain same result set or the functionality from DAX.

For this post, I use Application.Cities, Application.StateProvinces tables in WideWorldImporters database, and same tables are imported in to tabular model.Check the database diagram bellow. As you can see, there is one to many relationship between Cities and StateProvinces tables.

image

This is how same tables and relationship looks in Tabular model.

image

Lets start with simple select statement.

1. Select all the records from Cities table


SQL DAX
SELECT * FROM Application.Cities EVALUATE Cities
image image

2. Select few columns from the City table


SQL DAX

SELECT CityName,
       LatestRecordedPopulation
FROM Application.Cities;

EVALUATE
ALL(Cities[CityName],Cities[LatestRecordedPopulation])

image image

3. Select few columns and order result set by LatestRecordedPopulation descending order.

SQL DAX

SELECT CityName,
        LatestRecordedPopulation
FROM Application.Cities
ORDER BY LatestRecordedPopulation DESC;

EVALUATE
ALL(Cities[CityName],Cities[LatestRecordedPopulation])
ORDER BY Cities[LatestRecordedPopulation] DESC

image image

4. Filter out cities where LatestRecordedPopulation>1500000

SQL DAX

SELECT CityName,
        LatestRecordedPopulation
FROM Application.Cities
WHERE LatestRecordedPopulation>1500000;

EVALUATE
     FILTER(
         ALL(Cities[CityName],Cities[LatestRecordedPopulation]),
         Cities[LatestRecordedPopulation]>1500000
         )

image image

5. Group cities by City  name and apply  sum aggregation to LatestRecordedPopulation field.


SQL DAX

SELECT CityName,
        SUM(LatestRecordedPopulation) AS LatestRecordedPopulation
FROM Application.Cities
GROUP BY CityName

EVALUATE
     SUMMARIZE
    (
     Cities,
     Cities[CityName],
     “LatestRecordedPopulation”,
     SUM(Cities[LatestRecordedPopulation])
     )

image image

Whole propose of the blog post is not to teach you DAX, but to help you write very simple select statement in DAX if you are new to DAX and have not written any DAX queries before. Additionally,  when it comes to DAX, same query can be written in multiple ways and therefore note that DAX queries written in this post are not the only way to achieve same result set. In next post I will cover few other SQL statement in DAX. Cheers !!!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s