Forum Discussion
Need Help with RANK function
Good afternoon!
I need help adjusting my RANK measurement.
Currently it repeats the value 15 for all other values that repeat with the same value, this is making it difficult for me to make the top 20.
Any ideas?
My meaure =
Point of attention: I am ranking by values from the month of February.
Hi yforti13,
Thank you for reaching out to Microsoft Fabric Community.
To exclude values that are zero and only assign ranks to values greater than zero, here is an updated measure that will rank only values greater than 0, and show no rank (blank) for zero values:
m_RankingTabelaRec1820 =
VAR CurrentValue =
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1),
d_Calendario[Date] <= DATE(2025, 2, 29)
)
RETURN
IF (
CurrentValue > 0,
RANK(
DENSE,
FILTER(
ALLSELECTED(d_DemaisConvenios[Convênio]),
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1)
&& d_Calendario[Date] <= DATE(2025, 2, 29)
) > 0
),
ORDERBY(
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1)
&& d_Calendario[Date] <= DATE(2025, 2, 29)
),
DESC
)
)
)If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
6 Replies
yforti13 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.the problem is that my ranking measure is applying the fifteenth place equally to all zero values, I would like it to only apply the ranking to values that are above zero and not include any ranking for any zero value.
Hi yforti13,
Thank you for reaching out to Microsoft Fabric Community.
To exclude values that are zero and only assign ranks to values greater than zero, here is an updated measure that will rank only values greater than 0, and show no rank (blank) for zero values:
m_RankingTabelaRec1820 =
VAR CurrentValue =
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1),
d_Calendario[Date] <= DATE(2025, 2, 29)
)
RETURN
IF (
CurrentValue > 0,
RANK(
DENSE,
FILTER(
ALLSELECTED(d_DemaisConvenios[Convênio]),
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1)
&& d_Calendario[Date] <= DATE(2025, 2, 29)
) > 0
),
ORDERBY(
CALCULATE(
[m_Recuperacao],
d_Calendario[Date] >= DATE(2025, 2, 1)
&& d_Calendario[Date] <= DATE(2025, 2, 29)
),
DESC
)
)
)If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
