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.

My Code works until I have members who cancelled their membership for a few months and then resumed after. I need to restart from the first month after skip. for example :

GROUPIDAGEDATECurrent RankDesired RANK
122035-44202206126
122035-44202205125
122035-44202204124
122035-44202203123
122035-44202202122
122035-44202201121
122035-442020122424
122035-442020112323
122035-442020102222
122035-442020092121
122035-442020082020
122035-442020071919
122035-442020061818
122035-442020051717
122035-442020041616
………………
122035-4420190111

This is what I have tried but doesn't work for dates skipping.

RKING= 
RANKX (
CALCULATETABLE (
               VALUES ('tbl'[Date] ),
               ALLEXCEPT ( 'tblW', 'tbl'[GP], 'tbl'[ID] ),
                         'tbl'[AGE] = 'tbl'[AGE],
                         'tbl'[date] >= start_date && 'tbl'[date] <= end_date // date slicer
                ),
[Date] ,
,ASC
)

 

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
    )

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached file.

    I hope this can provide some ideas on creating a solution to your problem.

     

     

    step one Previousmonthmembership: = 
VAR _currentdate =
    MAX ( Data[DATE] )
VAR _currentgroup =
    MAX ( Data[GROUP] )
VAR _currentid =
    MAX ( Data[ID] )
VAR _currentage =
    MAX ( Data[AGE] )
RETURN
    MAXX (
        FILTER (
            ALLSELECTED ( Data ),
            Data[GROUP] = _currentgroup
                && Data[ID] = _currentid
                && Data[AGE] = _currentage
                && Data[DATE] < _currentdate
        ),
        Data[DATE]
    )

     

    step two currentgroupindex: = 
VAR _currentdate =
    MAX ( Data[DATE] )
VAR _currentgroup =
    MAX ( Data[GROUP] )
VAR _currentid =
    MAX ( Data[ID] )
VAR _currentage =
    MAX ( Data[AGE] )
VAR _addindexcolumn =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALLSELECTED ( Data ),
            "@memberpreviousmonth", [step one Previousmonthmembership:]
        ),
        "@consecutivecondition",
            SWITCH (
                TRUE (),
                [@memberpreviousmonth] = BLANK (), 0,
                Data[DATE] - [@memberpreviousmonth] > 31, 1,
                0
            )
    )
VAR _addgroupingcolumn =
    ADDCOLUMNS (
        _addindexcolumn,
        "@groupindex",
            SUMX (
                FILTER ( _addindexcolumn, Data[DATE] <= _currentdate ),
                [@consecutivecondition]
            )
    )
VAR _currentgroupindex =
    MAXX ( FILTER ( _addgroupingcolumn, Data[DATE] = _currentdate ), [@groupindex] )
RETURN
    _currentgroupindex

     

    step three Ranking measure: = 
VAR _currentgroupindex = [step two currentgroupindex:]
RETURN
    IF (
        COUNTROWS ( Data ) <> BLANK (),
        RANKX (
            FILTER ( ALLSELECTED ( Data ), [step two currentgroupindex:] = _currentgroupindex ),
            CALCULATE ( MAX ( Data[DATE] ) ),
            ,
            ASC
        )
    )
  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    RKING=
    RANKX (

        Filter(

            'tblW',

            'tbl'[AGE] = EARLIER('tbl'[AGE]) &&

            'tbl'[ID] = EARLIER('tbl'[ID]) &&

            'tbl'[GROUP] = EARLIER('tbl'[GROUP])

        ),

        'tbl'[DATE],

        ,

        ASC

    )

      • 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
        )