I have been struggling to implement RANKX into my Reports. I have 3 tables
The date table is used for lots of other reports and goes up to the end of 2019
Our facts for this particular report only go up to March
We want to rank the Average Fact against the Group by Month. The report will have a slicer on Year
We also don’t want to rank against the full set of groups. Only a subset of them so I am applying a filter against group for three of the groups
The original Ranking DAX used
Rank = RANKX(CROSSJOIN(ALL(‘dim Date'[date].[Month]),ALL(‘dim'[Group])), [Avg Fact],,DESC)
RANKX – RANKX is an Iterator. It takes a table and an expression to evaluate. RANKX looks at each row in the table and running its evaluation which is to return the ranking for each row in the table argument. RANKX creates a row context because it’s an iterator
Row Context – Calculated at processing time instead of at run time. This is a calculated column rather than a measure because the data is set on each row. A good example of a Row context calculation is, Is this value greater than 100.If yes Set to True. If no set to False. This is applied on each row
CROSSJOIN – Cross join allows you to recreate a table from all Tables and Columns in the cross join
ALL – Returns all the rows in the table, ignoring any applied filters
So essentially You are ranking against ALL months in the date and All groups within the other dimension table. We are ranking the Avg Fact measure
Now this works to some extent if you add in the table with the date filter set but no filter on the groups you want. Looking at January for example
However Later on in the data set the null values are set as
AND when I apply the filter of only having certain groupings in the table the RANKX fails even further
Note that the RANKX is still ranking every single value, even though we have applied a filter for the Group
This is because of the ALL. The Cross Join takes ALL month Values and ALL Group Values, and dismisses the fact that we have applied a filter
You need ALL because if you just ranked against the 1 value that the row context is on, all ranking would be 1.
Rank = RANKX(CROSSJOIN(ALLSELECTED(‘dim Date'[date].[Month]),ALLSELECTED(‘dim'[Group])), [Avg Fact],,DESC)
ALLSELECTED is different to ALL because ALL calculates everything ignoring filters. ALLSelected Takes into account the filter on the visual. An important part of the solution is that we are slicing on Year and we are filtering for specific groups. As a consequence we need to use ALLSELECTED
Remember we are ranking the measure against each Group. What is the best Group this Month?
Next stop, ensuring we don’t rank against null values
NOT ISBLANK( ‘fact'[Avg Fact]),
We have wrapped up the original RANKX into An IF block
NOT ISBLANK (If our measure its not blank then assign the RANKX function
This is great, Its working in the table because we can see in February ‘Age’ is top ranked and ‘Digi’ is bottom ranked.
Changing the DAX Query issues from table to matrix
However , if the data is to be displayed in a matrix we may not want to rank in this way at all
As you can see, in January we rank against each group. But what we could require is to Rank the best month for a group.
So we have created a rank to rank each group in a Month. NOT to rank the best month for a group.
As yet I haven’t come up with a solution to this one so the mystery continues but once I have a solution I will certainly create a post about it.
If you have a solution please let me know