DAX RANKX function – Part II

In a previous post, I explained how to use RANKX function to calculate revenue ranking for each provider in each department. In this post I’m going to explain how to rank at different levels.

I’m using my previous example which is a list of providers in different hospital clinical departments, I want to rank department and rank providers in each department by revenue (two ranking levels). Here is my sample table I started with creating a table in Excel “Table1” and add it to Power Pivot model.


I created following measures to rank provider by revenue:



The Rank measure will rank the [TotalRevenue] for the current Row against the [TotalRevenue] for all other rows in the Revenue column. The ALL(Table1[Provider]) is the list of values that the rank function use to compare the current row against. Since I used ALL function to ignore Provider filter context, I will get the following:


Now let’s add the department to pivot table and see how Rank measure works:


As you can see the Rank by provider still working within each department by the rank by department is not, it uses the department total and compare it against list of provider values. I want to rank each department against all departments, and rank providers within each department. In order to do that in Power Pivot, I want to create an IsDepartmentLevel measure that will return TRUE if the current selection in the PivotTable has a valid correspondent department definition, FALSE otherwise. If it is TRUE, Rank department, otherwise Rank providers. Let’s see how to write the IsDepartmentLevel measure:




                                                 CALCULATE(COUNTROWS(Table1) ,






The IsDepartmentLevel measure checks that the number of rows that are active in Table1 equal to the number of rows that would be active by removing from the filter context any column except Department.

As you can see in the following Pivot table, I added the IsDepartmentLevel measure to Pivot table, the IsDepartmentLevel measure is FALSE for every provider level but TRUE for department.


Now we are able to identify the department level which we need in order to evaluate the Rank measure and Rank department vs. provider.

Let’s re-write the Rank measure and see how to use the IsDepartmentLevel measure and RANKX function to rank departments and providers by revenue.

Rank:= IF(


                    RANKX(Filter(All(Table1[Department]), [TotalRevenue]),[TotalRevenue],,,Dense),

                    RANKX(Filter(All(Table1[Provider]), [TotalRevenue]),[TotalRevenue],,,Dense)


I used IF function to evaluate IsDepartmentLevel measure, if it is TRUE, it will calculate the revenue ranking for each department, otherwise it will calculate the revenue ranking for each provider.

The following Pivot table shows the ranking for each department.


When I expand the departments, it shows the ranking for each provider in each department and maintained the ranking for each department.


In this post, you have seen how to use RANKX function and calculate at different levels (department and provider) using one measure.

Project “ChâteauKebob”

Authors: Ayad Shammout & Denny Lee


It may sound like a rather odd name for an End-to-End Auditing Compliance project – and the roots admittedly enough are based on the authors’ prediliction toward great food in the city of Montréal – but there actually is an analogous association!

Château means manor house or palace and kebob refers to meat that is cooked over or next to flames; large or small cuts of meat, or even ground meat, it may be served on plates, or in sandwiches (mouth watering yet).  Château Kebob means house of kebob with different meat.

So why did we call our project “ChâteauKebob”? In this project Denny Lee and I used a château of different technologies in one framework or house with Kebob of mixed of data small or big data, structured and unstructured, and served on plates or in sandwiches, … sorry I meant served with multiple BI tools for reporting and analysis.

The purpose of this project is to provide a set of tools and jumpstart with scripts to implement the project involving HDInsight (Hadoop), SQL Server 2012, SQL Server Analysis Services 2012, Excel 2013 PowerPivot and Power View. You can review an overview of the project at slideshare .

The SDK is available at GitHub to download the entire project.

Healthcare Compliance with Big Data and BI

Healthcare Compliance with Big Data and BI

Over the past few years Denny Lee  (Technical Principal Program Manager within Microsoft’s SQL Business Intelligence Group) and I are always working on a very exciting SQL Server projects, earlier this month we presented “Big Data, BI, and Compliance in Healthcare” at PASS BA Conference in Chicago, IL.

Few years ago, we implemented “Centralized Audit Framework” to manage and view the audits of entire SQL Server environment that will parse, load, and report all of audit logs.

Expanding on the “Reaching Compliance: SQL Server 2008 Compliance Guide” to more easily handle larger volumes of structured and unstructured data and to gain richer and deeper insight using the latest analytics. To achieve this, we are building a Big Data-to-BI project involving HDInsight (Hadoop on Windows or Azure), SQL Server 2012, SQL Server Analysis Service 2012 Tabular instance, Integration Services, PowerPivot, and Power View.

The purpose of this SDK is to provide a set of tools and jumpstart with scripts to implement the Auditing project involving HDInsight, SQL Server 2012, PowerPivot and Power View.

Implementation Overview

The basic implementation of the Auditing and Reporting solution is shown in the figure below.


Figure 2

The general flow of data in this solution is that Audits are created on any number of SQL Servers (2008 and 2012) in the environment and are set to log to the file system. The audit logs will be stored directly to a central network file share. A scheduled SQL Server Agent Job runs an SSIS package that reads the audit log files, combines them into large file sizes (250MB to 1GB file size) and uploads them to HDInsight Blob Storage which is the storage source for HDInsight on Azure or Windows.

Once audit logs stored in HDInsight Blob storage, we use Hive which is a data warehouse framework for Hadoop that facilitates easy data summarization, ad-hoc queries, and the analysis of large datasets.

Create a BI Semantic Data Model tabular to bring relevant Hadoop data into SQL Server Analysis Services Tabular by using HiveQL via SQL Server Linked Server connection to Hadoop Hive. Analysis Services can then serve up the data for ad-hoc analysis and reporting.

Reports are created with Excel 2013 using Power View that interacts with views of data from data models based on SSAS tabular model, or using Data Explorer to import audit data from Hive external table in HDInsight to allow compliance auditors and server administrators to assess the server compliance and trends in server compliance.

This information would then be fed back to the appropriate security, administrator and application development teams to enact policies to approve levels of compliance.

As the system evolves, teams may load additional application audit logs into Hadoop which could help tie these SQL Server specific activities to application and business activities.

The SDK will be available soon at GitHub to download the entire project. Stay tuned!

DAX RANKX function scenarios

RANKX is a DAX function introduced in SQL Server PowerPivot 2012. It returns the ranking of a number in a list of numbers for each row in the table argument. For more information about RANKX, please click here .

Let’s see it in action, here is my scenario

I have a list of hospital clinical departments (to simplify the example, I’m using 2 departments) and would like to rank the providers in each department by revenue. I started with creating a table in Excel “Table1” and add it to PowerPivot model.


I used the following measures to create a simple Rank function:




The RankAsc and RankDesc measures will rank the [TotalRevenue] for the current Row against the [TotalRevenue] for all the other rows in the Revenue column. The ALL(Table1[Provider]) is the list of values that the rank function use to compare the current row against. The difference between the two rank measures is the Order of rank value (low to high or high to low). One thing to notice here is that I use an existing measure TotalRevenue.

Using the above RANK measures get the following ranking:


Now, when I filter on Medicine department, I get the following


The descending rank works ok but the ascending rank starts with 2, why is that?

Let me explain, I was ranking the set of providers in ALL(Table1[Provider]) which includes everyone, not just the folks in the one department.

When I filter on Medicine, the folks who are not in the Medicine department are still in the set that is being ranked. The use of ALL() means that I am ranking against an unfiltered set of all the providers, not just the providers in the selected department.

To fix this issue, I’ve modified the measure for RankAsc, here is the formula instead:


The part I added is highlighted in red, and it says to only consider the providers who have non-zero revenue.

Now, when I view the pivot table and filter on Medicine, as you can see it is ranking correctly,


So far so good, but what if I have a provider with no revenue (NULL value) in the Medicine department. I replaced the provider “Gail” revenue with NULL value.


Refreshing the pivot table,


As you can see above, that Gail is Rank = 1 in ascending order, that is because the RANK =1 position is going to the items that are tied with no data.  In other words, if there are any items in the column for whom the measure is BLANK(), they would all be tied for position =1 when you rank in ascending order.

So to fix this issue, I modified the DAX formula to:

RankAsc=IF(NOT(ISBLANK([TotalRevenue])),RANKX(FILTER(All(Table1[Provider]),[TotalRevenue]),[TotalRevenue],,1,Dense), BLANK())

RankDesc=IF(NOT(ISBLANK([TotalRevenue])),RANKX(FILTER(All(Table1[Provider]),[TotalRevenue]),[TotalRevenue],,,Dense), BLANK())

The above formula will check the TotalRevenue measure for a provider, if it is not blank then rank, otherwise ignore it.

I got the required result which doesn’t show a rank for a NULL value.


This just a simple example but you can do much more with the Rankx function.