SQL Top function and RANKX function for DAX Studio

Hey,

After holiday Im coming back with simple exercise. Purpose is to count orders on 2 aggregation levels — country and city and to assign ranking position and limit results to best 5.

We will use popular Superstore dataset.

We are about to count sales and aggregate results over country and city level.

SQL query looks like:

Our steps:

1. Create CTE in which we set aggragation levels: country and city

2. We count rows over country and city partitions and call result as NoOfTrades

3. We extract results. First we choose to display all (*) results.

4. We choose top 5 results

5. We label results with ranking position over NoOfTrades value

Below, DAX Studio version:

1. First we define measure in which we count rows in orders table

2. In summarize function we define grouping columns

3. We add results of 1 step calculation undef “Qty” alias

4. Additional column is ranking function which assings position by result value from step 3

5. All is embedded in FILTER function which limits ranking results to 1 to 5.

Hope you like it ;)

Regards

Related posts:

The study of statistics is hard, like tracking to the peak of Everest. Yes, no one can deny this fact. Yet, even about 60% of data science aspirant skip their plan to head into the AI and ML from…

Earlier this week a group of researchers at Tongji University’s medical school achieved a significant breakthrough in the treatment of lung disease, the fourth-leading cause of death in China. In a…