How to rank categories?

[Anonymous]

HI, 

I have table with set order and known categories. What I want to get as result of calculated column rankColorGroup is something like this.   If you have some ideas, please share them. Thanks

[Anonymous]

Hi @Anonymous ,

if I have understood it currectly your wants to rank the color by using order as Categorgy.

To do so, please use the below DAX formula:

RankColorGroup =
IF (
    'Table'[Color] = "Red",
    RANKX ( FILTER ( 'Table', 'Table'[Color] = "Red" ), 'Table'[Order],, ASC ),
    IF (
        'Table'[Color] = "Blue",
        RANKX ( FILTER ( 'Table', 'Table'[Color] = "Blue" ), 'Table'[Order],, ASC ),
        IF (
            'Table'[Color] = "Orange",
            RANKX ( FILTER ( 'Table', 'Table'[Color] = "Orange" ), 'Table'[Order],, ASC )
        )
    )
)

If this post helps, then please consider Accepting it as the solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

[az38]

hi @Anonymous 

i couldn't find any logic in your rankColorGroup rule

can you describe more detailed how it should be defined?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Same color that form 1 or more rows in series get unique number, in ascending order.

[Anonymous]

[az38 · Accepted solution]

@Anonymous 

ok, let's rock

first, create two calculated columns:

_colMaxInGroup = 
VAR _nextColor = LOOKUPVALUE('Table'[color],'Table'[order],'Table'[order]+1)
RETURN
IF(_nextColor='Table'[color],0,1)

and

_colGroup = IF('Table'[_colMaxInGroup]=1,'Table'[order],
calculate(min('Table'[order]),FILTER(ALL('Table'),'Table'[order]>EARLIER('Table'[order]) && 'Table'[_colMaxInGroup]=1))
)

and cherry on the top

rankColourGroup = RANKX('Table','Table'[_colGroup],,,Dense)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Thanks, your way of solving this gave me ideas for other things too 🙂

[az38]

@Anonymous 

for your second question with CaseID always the same

_colMaxInGroup = 
VAR _nextColor = LOOKUPVALUE('Table'[color],'Table'[order],'Table'[order]+1,'Table'[CaseID],'Table'[CaseID])
RETURN
IF(_nextColor='Table'[color],0,1)

and

_colGroup = IF('Table'[_colMaxInGroup]=1,'Table'[order],calculate(min('Table'[order]),FILTER(ALL('Table'),'Table'[order]>EARLIER('Table'[order]) && 'Table'[_colMaxInGroup]=1 && 'Table'[CaseID]=EARLIER('Table'[CaseID]))))

and then

rankColourGroup = RANKX(FILTER('Table','Table'[CaseID]=EARLIER('Table'[CaseID])),'Table'[_colGroup],,,Dense)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn