Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DebbieE
Community Champion
Community Champion

RANKX Ranking NULL Values

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
 
2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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.

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

 

View solution in original post

DebbieE
Community Champion
Community Champion

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
 
 

 

 

View solution in original post

10 REPLIES 10
Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski

 

DebbieE
Community Champion
Community Champion

Its not worked

 

RankIssues5.JPGAnd 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
Community Champion
Community Champion

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.
Mariusz Repczynski

 

DebbieE
Community Champion
Community Champion

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

Mariusz
Community Champion
Community Champion

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.
Mariusz Repczynski



DebbieE
Community Champion
Community Champion

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

 

RankIssues6.JPGSo 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
Community Champion
Community Champion

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.
Mariusz Repczynski

 

DebbieE
Community Champion
Community Champion

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
Community Champion
Community Champion

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.

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

 

DebbieE
Community Champion
Community Champion

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
 
 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.