Obviously, T-SQL and DAX are two different query languages and they serve entirely different purposes. However, as someone from a database development background, when I learnt DAX initially, I started thinking SQL and tried to achieve the same thing from DAX. The reason for that thinking process is that it is always easy to learn something new when we compare the same thing with something we already know. In this blog post, I will cover some very basic DAX statements in comparison with T-SQL. Again, the idea is to start with T-SQL and try to obtain the same result set or the functionality from DAX.
For this post, I use Application.Cities and Application.StateProvinces tables in WideWorldImporters database, and these tables are imported into the tabular model. Check the database diagram below. As you can see, there is one too many relationships between the Cities and StateProvinces tables.
This is how the same tables and relationships look in Tabular model.
Let’s start with a simple select statement.
1. Select all the records from the Cities table
2. Select a few columns from the Cities table.
3. Select a few columns and order the result set by LatestRecordedPopulation in descending order.
4. Filter out the cities where LatestRecordedPopulation>1500000
5. Group cities by City name and apply sum aggregation to the LatestRecordedPopulation field.
The whole purpose of this blog post is not to teach you DAX, but to help you write a 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, the same query can be written in multiple ways so therefore note that DAX queries written in this post are not the only ways to achieve the same result set.
In my next post, I will cover a few other SQL statements in DAX.
Author:
Asanka Padmakumara
Consultant – Business Intelligence
