Forum Discussion

NilR's avatar
NilR
Icon for Post Patron rankPost Patron
4 years ago
Solved

DAX Issue- Rankx by multiple Categories

I have 4 Categories (GP, ID, Age, Date). I would would like to create calculated column and group by GP, ID, and Age and Rank/ count by Date to see how many months each member has in past 24 month. ...
help needed
  • vapid128's avatar
    vapid128
    4 years ago

    OH OH sorry,

    I didnt get what your mean.

     

     

    rank =
    RANKX(
        FILTER(
            'Table',
            [GROUP]=EARLIER('Table'[GROUP]) &&
            [ID]=EARLIER('Table'[ID]) &&
            [AGE]=EARLIER('Table'[AGE])
        ),
        [DATE],
        ,
        ASC
    )
     
     
    Month>1 =
    var _NextMonth =
    LOOKUPVALUE(
        'Table'[DATE],
        [GROUP],[GROUP],
        [ID],[ID],
        [AGE],[AGE],
        [rank],[rank] +1
    )

    return (_NextMonth-[DATE])>1
     
     
    sessionID =
    RANKX(
        FILTER(
            'Table',
            [GROUP]=EARLIER('Table'[GROUP]) &&
            [ID]=EARLIER('Table'[ID]) &&
            [AGE]=EARLIER('Table'[AGE]) &&
            [Month>1]
        ),
        [DATE],
        ,
        ASC
    )
     
     
    ID_in_Session =
    RANKX(
        FILTER(
            'Table',
            [GROUP]=EARLIER('Table'[GROUP]) &&
            [ID]=EARLIER('Table'[ID]) &&
            [AGE]=EARLIER('Table'[AGE]) &&
            [sessionID]=EARLIER('Table'[sessionID])
        ),
        [DATE],
        ,
        ASC
    )
NilR's avatar
NilR
Icon for Post Patron rankPost Patron
4 years ago

vapid128 Thank you! still doesn't reset the date.

vapid128's avatar
vapid128
Icon for Solution Specialist rankSolution Specialist
4 years ago

OH OH sorry,

I didnt get what your mean.

 

 

rank =
RANKX(
    FILTER(
        'Table',
        [GROUP]=EARLIER('Table'[GROUP]) &&
        [ID]=EARLIER('Table'[ID]) &&
        [AGE]=EARLIER('Table'[AGE])
    ),
    [DATE],
    ,
    ASC
)
 
 
Month>1 =
var _NextMonth =
LOOKUPVALUE(
    'Table'[DATE],
    [GROUP],[GROUP],
    [ID],[ID],
    [AGE],[AGE],
    [rank],[rank] +1
)

return (_NextMonth-[DATE])>1
 
 
sessionID =
RANKX(
    FILTER(
        'Table',
        [GROUP]=EARLIER('Table'[GROUP]) &&
        [ID]=EARLIER('Table'[ID]) &&
        [AGE]=EARLIER('Table'[AGE]) &&
        [Month>1]
    ),
    [DATE],
    ,
    ASC
)
 
 
ID_in_Session =
RANKX(
    FILTER(
        'Table',
        [GROUP]=EARLIER('Table'[GROUP]) &&
        [ID]=EARLIER('Table'[ID]) &&
        [AGE]=EARLIER('Table'[AGE]) &&
        [sessionID]=EARLIER('Table'[sessionID])
    ),
    [DATE],
    ,
    ASC
)