How to Dynamically Track Issue Status in Power BI

[Thrasso00]

Hi everyone,

I'm looking for help on how to add a custom "Status" column to an issue tracking table in Power BI. The "Status" column should track the current status of each issue based on its timeline of "IssueActions", with possible values of "Actif" or "Closed". I'm trying to use DAX but my knowledge is limited. Can anyone provide guidance on how to achieve this? Any help would be greatly appreciated.

Zone	Issue	IssueAction	DateOfIssueAction	DesiredColumn(Status)
1	10	Reopened	23-04-23	Actif
1	10	Closed	20-02-23	Closed
1	10	Edited	15-01-23	Actif
1	10	Open	14-01-23	Actif
1	11	Open	19-02-23	Actif
2	12	Edited	25-05-22	Actif
2	12	Edited	19-04-22	Actif
2	12	Reopened	17-04-22	Actif
2	12	Closed	15-04-22	Closed
2	12	Edited	14-03-22	Actif
2	12	Open	13-03-22	Actif
2	13	Closed	14-04-22	Closed
2	13	Open	18-02-22	Actif
3	14	Closed	17-03-22	Closed
3	14	Open	13-01-22	Actif
3	15	Edited	15-05-21	Actif
3	15	Edited	24-03-21	Actif
3	15	Open	22-01-21	Actif

Best regards,

[FreemanZ]

hi @Thrasso00

Not sure i fully got you. try to add a calculated column like:
Column =
IF(
[IssueAction]="Closed",
"Closed",
"Actif"
)

[Thrasso00]

HI @FreemanZ thank you for answering,

What I want to do is group by issue number and assign a status of 'Active' or 'Closed' based on the chronological order of the IssueActions.

If we analyze issue number 12, you will see that it is 'Active' when the IssueAction remains 'Open' until an IssueAction = Closed is performed. From that moment on, it will be in the 'Closed' status until there is an IssueAction = Reopen. At that point, it would change back to 'Active'.

[FreemanZ]

hi @Thrasso00 

do you have a different request from the original post? could you post your request as a data table?

[Thrasso00]

Hi @FreemanZ,
I'm not sure I understood your question.
I created a post initially which has not been very successful in terms of responses.
I have broken the problem down into easier parts. Maybe someone can help me. So, somehow it's a different problem.
Here is the table:

Zone	Issue	IssueAction	DateOfIssueAction
1	10	Reopened	23-04-23
1	10	Closed	20-02-23
1	10	Edited	15-01-23
1	10	Open	14-01-23
1	11	Open	19-02-23
2	12	Edited	25-05-22
2	12	Edited	19-04-22
2	12	Reopened	17-04-22
2	12	Closed	15-04-22
2	12	Edited	14-03-22
2	12	Open	13-03-22
2	13	Closed	14-04-22
2	13	Open	18-02-22
3	14	Closed	17-03-22
3	14	Open	13-01-22
3	15	Edited	15-05-21
3	15	Edited	24-03-21
3	15	Open	22-01-21

[FreemanZ]

hi @Thrasso00 

could you also add the expected column directly?

[Thrasso00]

Hi @FreemanZ,

Zone	Issue	IssueAction	DateOfIssueAction	DesiredColumn(Status)
1	10	Reopened	23-04-23	Actif
1	10	Closed	20-02-23	Closed
1	10	Edited	15-01-23	Actif
1	10	Open	14-01-23	Actif
1	11	Open	19-02-23	Actif
2	12	Edited	25-05-22	Actif
2	12	Edited	19-04-22	Actif
2	12	Reopened	17-04-22	Actif
2	12	Closed	15-04-22	Closed
2	12	Edited	14-03-22	Actif
2	12	Open	13-03-22	Actif
2	13	Closed	14-04-22	Closed
2	13	Open	18-02-22	Actif
3	14	Closed	17-03-22	Closed
3	14	Open	13-01-22	Actif
3	15	Edited	15-05-21	Actif
3	15	Edited	24-03-21	Actif
3	15	Open	22-01-21	Actif