Forum Discussion
rank() function not working as expected (NOT USING RANKX)
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 )
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)
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.
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-...
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
Hi lauriedata , Thanks for the update. Please make sure your sample data is complete all the details including measures and everything.