RANKX Ranking NULL Values

[DebbieE]

I have the following Tables

dim Date, Dim Group, Fact 

DAX Expression

Rank = RANKX(CROSSJOIN(ALL('dim Date'[date].[Month]),ALL('dim Group'[Group])),

[Avg Fact)],,DESC,Dense)

Note that I cross Join Month and Group in the RANKX because they are both in the query

But it Ranks NUll values, For example, if I add the data, This is what I want

                     Jan      Feb       Mar       Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec

Group 1       90%     80%      70%      60%

                     1          2          3            4       

Group 2       60%     80%      100%    90%

                    4           3             1           2

Group 3       70%     70%      80%      85%

                     3           3           2            1  

But this is what I get (Or an example of it)

                     Jan      Feb       Mar       Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec

Group 1       90%     80%      70%      60%

                     2          3          4            5        1            1         1        1           1           1          1          1

Group 2       60%     80%      100%    90%

                    5           4             2           3       1            1         1        1           1           1          1          1

Group 3       70%     70%      80%      85%

                     4           4           3            2         1            1         1        1           1           1          1          1

How do I change the DAX to add a filter to the Fact to not rank NULL Values?

Thanks in Advance

[Mariusz · Accepted solution]

HI @DebbieE 

ALLSELECTED() will preserve the filters coming from outside the visuals so the filters for year and group will still be applied.

If you are using date dimension in your model, then you should have a Month Column and use it and Month Sort Column in SUMMARIZE ( ) 

Please see my Measure and outcome below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @DebbieE 

Try something like this.

Rank = 
VAR  _tbl = SUMMARIZE( ALLSELECTED( yourFactTable ), 'dim Date'[date].[Month] , 'dim Group'[Group] )
RETURN RANKX( _tbl, [Avg Fact],, DESC, Dense )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DebbieE]

Its not worked

And its not ranking the Percentages Correctly.

There are 2 other things Im doing that may be affecting it

1. There is a Slicer in the report on year 2019

2. There is visual filter  selecting 3 groups, so some groups are missing.

Im begining to worry that I cant do this in DAX 

[Mariusz]

Hi @DebbieE 

Can you create and share a data sample?

Many Thanks

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DebbieE]

Unfortunately not, The way this form works , I cant share data from our One Drive 

[Mariusz]

HI @DebbieE 

Try the below.

Rank = 
IF(
    NOT ISBLANK( [Avg Fact] ),
    VAR  _tbl = CALCULATETABLE( VALUES( 'dim Date'[date].[Month] ), ALLSELECTED() )
    RETURN RANKX( _tbl, [Avg Fact],, DESC, Dense )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DebbieE]

Its got rid of the rank against null which is great but all the ranks are 1

So what I want is for Plan Jan = 1, Feb = 3 , March = 2

Again, Ive got a filter on Group so it only choses those 3 groups and a Slicer on Year

[Mariusz]

Hi @DebbieE 

Try the below, or if your original measure worked fine, just add IF( NOT ISBLANK( [Avg Fact] ), Your Measure ) 

Measure = 
Rank = 
IF(
    NOT ISBLANK( [Avg Fact] ),
    VAR  _tbl = CALCULATETABLE( 
        SUMMARIZE( 'dim Date', 'dim Date'[MonthColumn], 'dim Date'[MonthSortColumn] ), 
        ALLSELECTED() 
    )
    RETURN RANKX( _tbl, [Avg Fact],, DESC, Dense )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DebbieE]

Im unsure where the month sort column has come from ?

Im using Month from the date hierarchy

Ive tried the following but it just sets everything as 1

IF(

NOT ISBLANK('fact'[Fact] ),

VAR _tbl = CALCULATETABLE(

SUMMARIZE( 'dim Date', 'dim Date'[date].[Month]),

ALLSELECTED()

)

RETURN RANKX( _tbl,[Fact],, DESC, Dense )

)

Also I dont think my original RANKX was working correctly because I have a slicer on Year and a Filter that filters out just 3 groups to use out of all the groups in the dimension for this table. E.g. In the matrix I have Group1 Group2 and Group3 but there are actually 8 groups

[Mariusz · Accepted solution]

HI @DebbieE 

ALLSELECTED() will preserve the filters coming from outside the visuals so the filters for year and group will still be applied.

If you are using date dimension in your model, then you should have a Month Column and use it and Month Sort Column in SUMMARIZE ( ) 

Please see my Measure and outcome below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DebbieE · Accepted solution]

Ive managed to rejig my script using the above info thankyou 

Rank =

IF(

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

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

[Avg Overall Satisfaction (Flash Report)],,DESC)

)

ALLSELECTED takes my filter into consideration because Im only selecting certain groups AND NOT ISBLANK only ranks none blank Values