Forum Discussion
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 Time Status Index
1 01/01/2023 Other 1
1 02/01/2023 Closed 1
1 03/01/2023 Other 2
1 04/01/2023 Other 2
1 05/01/2023 Other 2
1 06/01/2023 Closed 2
1 07/01/2023 Other 3
2 06/01/2023 Other 1
2 07/01/2023 Closed 1
2 08/01/2023 Other 2
2 09/01/2023 Other 2
2 10/01/2023 Closed 2
4 Replies
Topic got to the spam folder earlier. Can somebody helps me?
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.
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).
