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 )

     

     

     

     

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

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.