RANK indexing with condition

[JK-1]

Hi, have been looking at indexing again, and whether some sequencing conditions would be achievable.

For  (see snip below):
RAND = RAND()
RANK = RANKX(ALL(Table),Table[RAND])
RANK INC = IF(Table[isCurrent]="No",RANKX(ALL(Table),Table[RAND]))

Is it possible to in the RANK INC not have it skip the excluded numbers and keep the sequence running without number gaps.
Thought about && Table[isCurrent]="No" in RANK but can't get it right.

Or is RANKX not going to be the best option to fulfil?

Separately, I was then working on 3 digits to make up the number in RANK INC to be a full 3 digit sequence with leading zeroes.
Still learning switch but would switch enable length decision and prefix 00 (for 1), 0 (for 2).

Thanks,

[v-pnaroju-msft]

Thankyou, @SamsonTruong and @maruthisp, for your response.

Hi @JK-1,

We appreciate your inquiry posted on the Microsoft Fabric Community Forum.

It is possible to achieve gapless ranking for only the rows with "No" using the RANKX function, and to format these rankings as three-digit strings using the FORMAT function.

Kindly find attached a screenshot and the PBIX file, which may assist in resolving the issue:

If you find our response helpful, we would be grateful if you could mark it as the accepted solution and kindly provide kudos. This will help other members of the community who may have similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[maruthisp · Accepted solution]

Hi @JK ,

Could you please try below DAX expresion:
RANK_INC_Formatted =
VAR RankValue =
RANKX(
FILTER(ALL(Table), Table[isCurrent] = "No"),
Table[RAND],
,
ASC,
DENSE
)
RETURN
IF(
Table[isCurrent] = "No",
FORMAT(RankValue, "000")
)

FILTER(ALL(Table), Table[isCurrent] = "No") ==> ensures only those rows are considered.
DENSE ranking avoids gaps  ==> 1,2 ,3 ...
FORMAT function with "000" to add the number with leading zeros.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[JK-1]

That is excellent, thank you. Especially handy capturing the other part within it.

[SamsonTruong]

Hi @JK-1,

Please try the following DAX calculated column and let me know if this achieves your desired result. This also takes into account your 3 digit formatting:

RANK INC = 
IF(
    Table[isCurrent] = "No",
    FORMAT(
        RANKX(
            FILTER(ALL(Table), Table[isCurrent] = "No"),
            Table[RAND],
            ,
            ASC,
            DENSE
        ),
        "000"
    )
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session