Rank not working

[mp390988]

Hi,

I am trying to rank clientId by the measure "2024 YTD" but i am getting all 1s.
So the rank is not working as expected.

The defintion of my rank is as follows:

RankClients = RANKX(
    ALL(TradesTbl[ClientID]),
    [RevenueYTDForPrevYear],,
    DESC,
    Dense
)

And the definiton of the measure I am trying to rank by is defined as follows:

RevenueYTDForPrevYear = IF(
    NOT ISBLANK(
        CALCULATE(
            [Revenue],
            DATESBETWEEN(
                'CalendarTbl'[Date],
                DATE(
                    YEAR(TODAY())-1,
                    1,
                    1
                ),
                DATE(
                    YEAR(EOMONTH(TODAY(),-1))-1,
                    MONTH(EOMONTH(TODAY(),-1)),
                    DAY(EOMONTH(TODAY(),-1))
                )   
            )
        )
    ),
    CALCULATE(
        [Revenue],
        DATESBETWEEN(
            'CalendarTbl'[Date],
            DATE(
                YEAR(TODAY())-1,
                1,
                1
            ),
            DATE(
                YEAR(EOMONTH(TODAY(),-1))-1,
                MONTH(EOMONTH(TODAY(),-1)),
                DAY(EOMONTH(TODAY(),-1))
            )   
        )
    ),
    0
)
 

For the purpose of the visual I renamed this measure to "2024 YTD".

Not sure why my ranking is returning all 1s.

Thank You

[v-nmadadi-msft]

Hi @mp390988 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @mp390988 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @mp390988 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @mp390988  ,
Thanks for reaching out to the Microsoft fabric community forum.

Please create a separate Table visual in your report and add only the 2024YTD and RankClients measures to it. This will help you determine whether the issue is with the measure logic itself or caused by unintended filters or interactions applied elsewhere in the report. By isolating the measures in a clean context, you can more easily pinpoint the root cause.

Additionally try modifying your DAX in such a way and check if the issue still persists

RankX_2 = 
VAR t = CALCULATETABLE(RankClients, REMOVEFILTERS(RankClients), VALUES(TradesTbl[ClientID]))
RETURN RANKX(t, [RevenueYTDForPrevYear] )

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[Ashish_Mathur]

Hi,

Does this measure work?

RankClients = RANKX(GENERATE(GENERATE(ALL(TradesTbl[ClientID]),ALL(TradesTbl[ClientName])),ALL(TradesTbl[DealerID]),ALL(TradesTbl[DelaerName]))[RevenueYTDForPrevYear],,DESC,Dense)

If this does not work, then share the download link of the PBI file.

[mp390988]

Hi @Greg_Deckler  - that does not make any difference

[Greg_Deckler]

@mp390988 Can you share some sample data as text? Hard to know what exactly is going wrong. You might check out the RANK function as well as it can sometimes be less finicky than RANKX. You could also try an alternative to RANKX found here: To *Bleep* with RANKX! - Microsoft Fabric Community

[Greg_Deckler]

@mp390988 Try:

RankClients = RANKX(
    ALL(TradesTbl[ClientID]),
    CALCULATE( [RevenueYTDForPrevYear] ),,
    DESC,
    Dense
)