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

[lauriedata]

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 WITHIN THE DATE RANGE, NOT FOR A SINGLE DATE.    The ranking ORDER is correct, but I would expect the numbers to be 1,1,3,3,5 or if I use dense, to be 1,2,3,4,5 etc.  Neither is happening.

Here are the formulas:

Rank_TopN Reward Value =
var _TopN = selectedvalue('TopN'[Top N]) /* user chooses rank level to view */
Return
if(HASONEFILTER(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),
switch(true(),rank(all(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),orderby([CS Adjust RW],desc))<=_TopN,
rank(all(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),orderby(v_flt_ci_cs_adjustment_rwd[CS Adjust RW],desc))))

NOTE: CS Adjust RW = calculate (sum(v_flt_ci_cs_adjustment_rwd[sum_reward_value]), allexcept(v_flt_ci_cs_adjustment_rwd, v_flt_ci_cs_adjustment_rwd[Loyalty ID])).

==========================

If I do the same formula for another field/table for points, it works out fine, so I cannot figure out what I am doing wrong. 

Here is the points result and formula that all seem to work as expected.

Rank_TopN Points = 

var _TopN = selectedvalue('TopN'[Top N]) /* user chooses rank level to view */

Return

if(HASONEFILTER(v_flt_ci_cs_adjustment_points[Loyalty ID]),

switch(true(),rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([CS Adjust Points],desc))<=_TopN,

rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([CS Adjust Points],desc))))

NOTE:  CS Adjust Points = 

        CALCULATE(

SUM(v_flt_ci_cs_adjustment_points[points_earned]),

ALLEXCEPT(

v_flt_ci_cs_adjustment_points,

v_flt_ci_cs_adjustment_points[Loyalty ID]

)

)

[v-hashadapu]

Hi @lauriedata , Thank you for reaching out to the Microsoft Community Forum.

The ranking breaks because the rewards table is still producing multiple visible rows per Loyalty ID (one per date) and the RANK function always ranks each row of the table it is given. Even though your measure [CS Adjust RW] correctly returns the total reward per Loyalty ID, the visual’s row context forces RANK to evaluate that same total repeated across several rows, which leads to tied values but skipped rank numbers. That is why DENSE has no effect. The function is not ranking a clean, one-row-per-ID table. Your points table works only because its structure naturally gives one row per ID during ranking.

You must force RANK to operate on a distinct list of Loyalty IDs, not the visual rows. Example:
Rank_TopN Reward Value =
VAR _TopN = SELECTEDVALUE('TopN'[Top N])
VAR _Rank =
RANKX(
ALL(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),
CALCULATE([CS Adjust RW]),
,
DESC,
DENSE
)
RETURN
IF(_Rank <= _TopN, _Rank)

[lauriedata]

Hi.  Thank you, I tried that, but the formula with rankx  with dense gave the exact same result as my rank.  1,1,4,4, etc.  When I refreshed the data, it also threw a cyclic error.  I'll keep working on this.  Thanks again.

[v-hashadapu]

Hi @lauriedata , Thank you for reaching out to the Microsoft Community Forum. 

Please try this:
Rank_TopN Reward Value =
VAR _TopN = SELECTEDVALUE('TopN'[Top N])
VAR RankTable =
ADDCOLUMNS(
VALUES(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),
"TotalRW", CALCULATE([CS Adjust RW])
) VAR _Rank = RANKX(RankTable, [TotalRW], , DESC, DENSE)
RETURN IF(_Rank <= _TopN, _Rank)

If you still see 1,1,4,4 or get a cyclic error, that strongly indicates a dependency loop, make sure [CS Adjust RW] does not reference any ranking measures or any measure that in turn references Rank_TopN. Also check for hidden decimal differences (use ROUND([CS Adjust RW],2) in the TotalRW column while testing).

If that still doesn’t work, please provide me with a short sample data (preferably not as images) including your measures and all the relevant details and I will check it to see how I can help you.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[lauriedata]

I'm getting "The value for 'TotalRW' cannot be determined. Either the column doesn't exist, or there is no current row for this column."  I'll have to send sample data tomorrow.  Thanks

[v-hashadapu]

Hi @lauriedata , Thanks for the update. Please make sure your sample data is complete all the details including measures and everything.

[lauriedata]

This formula below is close.  I'm sorry I haven't been able to anonymize the data but I discovered that the issue is the ranking is not considering the dates in the date slicer.  It is ranking based on all dates in the data though the visual displays according to the date slicer. 

The desired result is that the ranking formula be based on the dates chosen in the slicer across all loyaltyids. 

It is correctly ignoring the date IN THE VISUAL (I show the dates in the visual but need the rank to be based on all dates in the slicer, not each date in the visual, and not all dates in the data)

Rank_TopN Reward Value =

VAR _TopN = SELECTEDVALUE('TopN'[Top N])

VAR _Rank =

RANKX(

ALL(v_flt_ci_cs_adjustment_rwd[Loyalty ID]),

 calculate (sum(v_flt_ci_cs_adjustment_rwd[sum_reward_value]), ALLEXCEPT(v_flt_ci_cs_adjustment_rwd, v_flt_ci_cs_adjustment_rwd[Loyalty ID])),

,

DESC,Dense

)

RETURN

IF(_Rank <= _TopN, _Rank)

[lauriedata]

@v-hashadapu Thank you for your efforts.  Please see the link in the response to Mark.  I thought I was possibly created conflicts in my measures and the rank formula conditions, so I tried to do everything in the rank formula itself to no avail.  It's the bottom table that is an issue.

[MarkLaf]

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]

@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 · Accepted solution]

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]

@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!!!!!!   👏👏

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.  Show the expected result there.