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.

SQL 2012 SP1 CU6 issue with SSIS DB in Availability Group

Recently we were installing SQL Server 2012 SP1 CU6 to fix the slow performance when you run the SSIS Server Maintenance Job to remove old data in SQL Server 2012, you can read about the issue here.  We ran into a serious issue where the installation failed and couldn’t start SQL Server services.

Let me first describe the server, it is a named instance of SQL Server 2012 SP1 Engine and SSIS with AlwaysOn Availability Group, primary instance in main data center and secondary replica in DR site. The AG comprises all databases and SSISDB.

SQL 2012 SP1 CU6 installation failed with the following error:

Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

The issue was related to configuring SSIS db  “SSISDB” with AlwaysON Availability Group. So what happened is that SQL Server service packs usually run in single-user mode, while an availability database must be a multi-user database. Therefore, during installing the CU6, all availability databases including SSISDB will be taken OFFLINE which become inaccessible and thus fail the patch.

To workaround this issue, here are the steps:

  1. Start SQL Server service with Trace Flag 902: 
    • >Net Start MSSQL$InstanceName /T902
  2. Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
  3. Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName \MSSQL
  4. Stop SQL Server services:  
    • >Net Stop MSSQL$InstanceName
  5. Start SQL server service from SQL Server configuration manager
  6. Add SSISDB back to Availability Group

That’s all and we are back in business. Click here from more information on how you can make use of SSIS and AlwaysOn Availability Groups.