The syntax for RANKX is as follows:
Table: The first parameter table needs to be a DAX table.
This can be a physical table, or it can be the output of a DAX function that returns a table. Like any of the X functions (SUMX, MINX, MAXX etc), the table that gets passed here controls the number of loops, or iterations performed by the RANKX function. If we pass our sample 10-row dataset to the function, the code inside the RANKX function will loop 10 times (once for each row in the table). The smallest number that could be generated is a 1, while the largest value that could be returned is a 10. If we used a function to summarise the physical table, say to the distinct values of the Category column, the RANKX function will logically perform three loops.
Expression: Think of this parameter as a “Sort by” instruction for the RANKX function.
This needs to be a single number – but can be the result of a complex calculation, although more often than not, it will likely be just the SUM over a column for a single or series of rows group together. You can write DAX calculation here that returns a single value.
Value: This optional parameter is a peculiar one.
The quick answer is that if you are just trying to perform some basic ranking, you probably don’t need this. It’s possible to add code here and not get an error, BUT you may also subtly change your ranking order in a way that will be difficult to detect. I will try to explain this in more detail at the end of the article, so until you actually know you need this parameter, I recommend you leave it blank.
Order: The order parameter allows you to swap the ranking between the default that the highest expression gets a ranking of 1, with the next highest being 2, to the lowest expression being a 1, with the next highest returning a rank of 2. The only two values that can be passed to this parameter are ASC or DESC.
Ties: This parameter has nothing to do with breaking ties.
If you happen to have multiple rows in the table that have the same value for expression, this parameter will not break ties, or return different ranking values. Once the RANKX function has sorted the table by the expression and established the order – this function lets you decide if you would like to inject gaps into the numeric ranking sequence in the event of a tie. The sample data-set I use for this article deliberate has some ties so you can see what the RANKX function will return. The only values that can be passed for this parameter are DENSE, which means no gaps in the numeric ranking sequence, and SKIP, which will add gaps.