Power BI – Dax – RANKX how to use Rank X with 2 values, Filters and Slicers in a Matrix

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

Rank =

IF(

    NOT ISBLANK( ‘fact'[Avg Fact]),

    RANKX(CROSSJOIN(ALLSELECTED(‘dim Date'[date].[Month]),ALLSELECTED(‘dim'[Group])),

    [Avg Fact],,DESC)

)

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s