Forum Discussion

lauriedata's avatar
lauriedata
Resolver I
8 months ago
Solved

rank() function not working as expected (NOT USING RANKX)

Hello.  Whether or not I include "DENSE" in the formula, the result is identical.  The rank() function is giving me ties with skips in the numbering.  The rank is based on the total value of reward W...
Need Help
  • MarkLaf's avatar
    MarkLaf
    8 months ago

    Thanks for sharing the pbix as that makes it much easier to troubleshoot.

     

    The reason that the measure is not working in your pbix is because of the [Rank_TopN Reward Value] is not blank filter on the visual. This adds a value filter to the visual's SUMMARIZECOLUMNS, which does not interact well with the ADDCOLUMNS I had originally used. We can circumvent this issue if we use SUMMARIZECOLUMNS instead. This worked for me with the is not blank filter in your shared pbix.

     

     

    Rank_TopN Reward Value fixed = 
VAR _topN = SELECTEDVALUE('TopN'[Top N])
VAR _core = 
    SUMMARIZECOLUMNS( 
        v_flt_ci_cs_adjustment_rwd[Loyalty ID],
        ALLSELECTED( 
            v_flt_ci_cs_adjustment_rwd[Loyalty ID], 
            v_flt_ci_cs_adjustment_rwd[reward_issue_date] 
        ), 
        "@selectedSum", CALCULATE( SUM( v_flt_ci_cs_adjustment_rwd[sum_reward_value] ) )
    )
VAR _rank = RANK( DENSE, _core, ORDERBY( [@selectedSum], DESC ) )
RETURN
IF( _rank > 0 && _rank <= _topN, _rank )

     

     

     

     

MarkLaf's avatar
MarkLaf
Super User
8 months ago

Try using ALLSELECTED on your date column as your CALCULATE filter. Also, RANK rather than RANKX behaves more intuitively in my experience, so that may be worth trying.

 

Here is an example with some sample data I gleaned from your post.

 

v_flt_ci_cs_adjustment_rwd

Loyalty ID Reward Issue Date CS Adjusted Reward Value
38 8/1/2025 500
38 11/10/2025 240
38 11/11/2025 240
35 8/1/2025 501
35 9/29/2025 105
35 10/1/2025 125
22 8/1/2025 502
22 9/17/2025 225
281 8/1/2025 503
281 9/27/2025 220
19 8/1/2025 504
19 9/12/2025 215
45 8/1/2025 505
45 10/22/2025 205
40 8/1/2025 506
40 10/8/2025 145
40 10/9/2025 40
170 8/1/2025 507
170 9/30/2025 185

 

Note: this is your originally visible data + a row for each ID on 8/1/2025 to show the date filter is working.

 

Measure:

Selected Sum Rank = 
VAR _topN = SELECTEDVALUE('Top N'[Top N])
VAR _rank = 
    RANK( 
        ADDCOLUMNS( 
            ALL( v_flt_ci_cs_adjustment_rwd[Loyalty ID] ), 
            "@selectedSum", 
                CALCULATE( 
                    SUM( v_flt_ci_cs_adjustment_rwd[CS Adjusted Reward Value] ), 
                    ALLSELECTED( v_flt_ci_cs_adjustment_rwd[Reward Issue Date] ) 
                )
        ), 
        ORDERBY( [@selectedSum], DESC ) 
    )
RETURN
IF( NOT ISFILTERED( 'Top N'[Top N] ), _rank, IF( _rank <= _topN, _rank ) )

 

Visual + Slicers:

 

lauriedata's avatar
lauriedata
Resolver I
8 months ago

MarkLaf I see it works for you.  Thank you very much for  your time!! 

It's the bottom table that is the issue.  For me it gives me 1's for every row.  Even after I changed from DQ to Import mode.  I just can't understand why it would work for you and not me but I will keep trying.  I've included the formula in the attached using the actual field names. I'm struggling to upload a pbix.  Please let me know if you can access this:  Rewards Import Aonyous.pbix

 

  • MarkLaf's avatar
    MarkLaf
    Super User
    8 months ago

    Thanks for sharing the pbix as that makes it much easier to troubleshoot.

     

    The reason that the measure is not working in your pbix is because of the [Rank_TopN Reward Value] is not blank filter on the visual. This adds a value filter to the visual's SUMMARIZECOLUMNS, which does not interact well with the ADDCOLUMNS I had originally used. We can circumvent this issue if we use SUMMARIZECOLUMNS instead. This worked for me with the is not blank filter in your shared pbix.

     

     

    Rank_TopN Reward Value fixed = 
VAR _topN = SELECTEDVALUE('TopN'[Top N])
VAR _core = 
    SUMMARIZECOLUMNS( 
        v_flt_ci_cs_adjustment_rwd[Loyalty ID],
        ALLSELECTED( 
            v_flt_ci_cs_adjustment_rwd[Loyalty ID], 
            v_flt_ci_cs_adjustment_rwd[reward_issue_date] 
        ), 
        "@selectedSum", CALCULATE( SUM( v_flt_ci_cs_adjustment_rwd[sum_reward_value] ) )
    )
VAR _rank = RANK( DENSE, _core, ORDERBY( [@selectedSum], DESC ) )
RETURN
IF( _rank > 0 && _rank <= _topN, _rank )

     

     

     

     

  • lauriedata's avatar
    lauriedata
    Resolver I
    8 months ago

    MarkLaf  I CANNOT thank you enough for all the effort in helping me.  I noticed that your original formula works if I leaves the "rank is not blank" filter out (except that, of course, it also shows blanks).  And I see that the new formula works WITH the "rank is not blank" filter included.

    I will look up summarize columns and add columns, but if you have any advice on how I can wrap my head around this more clearly, please advise.  THANK YOU AGAIN!!!!!!   👏👏