Forum Discussion

Mateusz00's avatar
Mateusz00
Frequent Visitor
2 years ago
Solved

Index / Rank

Hello, I have three columns (ID, Time and Status). I would like to have some calculated column which shows me for each ID index / rank until the status is "Closed" (based on time column). ID      ...
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Mateusz00 

    try below code 

    RANKX(
    FILTER(
        'Table','Table'[id]=EARLIER('Table'[id]) &&
        'Table'[time]<=EARLIER('Table'[time])&&
        'Table'[status]="closed"
    ),
    'Table'[time],,ASC,Dense
)

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, Mateusz00 

Based on the information you have provided, Here are my answers to your questions.

1. Create a table of your presentation.

 

2. Create a calculated column, enter the appropriate DAX code and filter out the Index with Status of Closed.

Column =

    VAR currentID = 'Table'[ID]

    VAR currentStatus = 'Table'[Status]

RETURN

IF(currentStatus = "Closed",

    RANKX(

        FILTER('Table', 'Table'[ID] = currentID && 'Table'[Time] <= EARLIER('Table'[Time]) && 'Table'[Status] = "Closed"),

        'Table'[Time],

        ,

        ASC,

        DENSE

    ),

    BLANK()

)

 

 

 

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mateusz00's avatar
Mateusz00
Frequent Visitor
2 years ago

Hello, thank you for your response but how can we complete each rows? (in this formula we have blanks and I must have data exactly like in my Index column from the sample).

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Mateusz00 

    try below code 

    RANKX(
    FILTER(
        'Table','Table'[id]=EARLIER('Table'[id]) &&
        'Table'[time]<=EARLIER('Table'[time])&&
        'Table'[status]="closed"
    ),
    'Table'[time],,ASC,Dense
)