DAX Rank Over Two Columns

Scenario: An interesting scenario came today from a client. Consider the following schema. You have a fact table InternetSales and two dimension tables (Customer and SalesTerritory).

You want to produce a report ranking each customer based on their sales for each territory. In addition, you want the rank to change as you filter the report, such as filter specific customers or specific territories, so you need a measure.

Solution: The DAX RANKX function takes a table as the first argument. However, in this case, the rank needs to be produced over fields from two tables. So, the CROSSJOIN function is used to produce the needed combination.

‘InternetSales'[CustomerRank] :=
IF (
ISBLANK([SalesAmount]) || NOT HASONEVALUE(Customer[FullName]), BLANK(),
RANKX(CROSSJOIN(ALLSELECTED(‘Customer'[FullName]), ALLSELECTED(‘SalesTerritory'[SalesTerritoryCountry])), [SalesAmount],,,DENSE)
)

The ALLSELECTED function is needed to ignore the context from the FullName and SalesTerritoryCountry from the respective columns on the report. The condition in the IF statement ignores calculating the rank for customers with no sales and for the report total.

If you need to ignore a specific table so it doesn’t affect ranking then you can use the CALCULATE function (the IF logic excluded for brevity):

‘InternetSales'[CustomerRank] :=  RANKX(CROSSJOIN(ALLSELECTED(‘Customer'[FullName]), ALLSELECTED(‘SalesTerritory'[SalesTerritoryCountry])), CALCULATE([SalesAmount], ALL(TableToIgnore)),,,DENSE)

Another way to solve this scenario is to produce the rank over the fact table but you need to add FullName and SalesTerritoryCountry to the fact table first and use the fact columns on the report (not the ones from the dimensions). Then, you can do:

‘InternetSales'[CustomerRank] :=  RANKX(ALLSELECTED(‘InternetSales’),CALCULATE([SalesAmount], ALLEXCEPT(‘InternetSales’, ‘InternetSales'[Player], ‘InternetSales'[SalesTerritoryCountry])),,,DENSE)