Forum Discussion

Jeanxyz's avatar
Jeanxyz
Icon for Power Participant rankPower Participant
2 years ago
Solved

DAX rank in column

I have a table with multiple Issue_Keys, each issue_Key has multipel status records. I would like ot rank the status records based on [Created] field per Issue_Key. [Ranking expected] is what I expec...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Jeanxyz , Use below DAX

     

    DAX
    Ranking =
    VAR CurrentIssueKey = 'Fact_Status Log'[ISSUE_KEY]
    VAR CurrentCreated = 'Fact_Status Log'[CREATED]
    RETURN
    CALCULATE(
    COUNTROWS(
    FILTER(
    'Fact_Status Log',
    'Fact_Status Log'[ISSUE_KEY] = CurrentIssueKey &&
    'Fact_Status Log'[CREATED] <= CurrentCreated
    )
    ),
    ALLEXCEPT('Fact_Status Log', 'Fact_Status Log'[ISSUE_KEY])
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Jeanxyz , Use below DAX

 

DAX
Ranking =
VAR CurrentIssueKey = 'Fact_Status Log'[ISSUE_KEY]
VAR CurrentCreated = 'Fact_Status Log'[CREATED]
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'Fact_Status Log',
'Fact_Status Log'[ISSUE_KEY] = CurrentIssueKey &&
'Fact_Status Log'[CREATED] <= CurrentCreated
)
),
ALLEXCEPT('Fact_Status Log', 'Fact_Status Log'[ISSUE_KEY])
)

  • Jeanxyz's avatar
    Jeanxyz
    Icon for Power Participant rankPower Participant
    2 years ago

    Thanks a lot, bhanu_gautam . It works like a charm. 

     

    I slightly changed the measure, using all() instead of allexcept(), it also works.

    Ranking =
    VAR CurrentIssueKey = 'Fact_Status Log'[ISSUE_KEY]
    VAR CurrentCreated = 'Fact_Status Log'[CREATED]
    RETURN
    CALCULATE(
    COUNTROWS(
    FILTER(all('Fact_Status Log'),
    'Fact_Status Log'[ISSUE_KEY] = CurrentIssueKey &&
    'Fact_Status Log'[CREATED] <= CurrentCreated
    )
    ))