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 )

     

     

     

     

v-hashadapu's avatar
v-hashadapu
Community Support
8 months ago

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's avatar
lauriedata
Resolver I
8 months ago

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's avatar
    v-hashadapu
    Community Support
    8 months ago

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

    • lauriedata's avatar
      lauriedata
      Resolver I
      8 months ago

      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)
      • 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: