topic Re: How to Dynamically Track Issue Status in Power BI in DAX Commands and Tips

How to Dynamically Track Issue Status in Power BI

Thrasso00 — Mon, 24 Apr 2023 08:57:11 GMT

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,

Re: How to Dynamically Track Issue Status in Power BI

FreemanZ — Mon, 24 Apr 2023 09:40:31 GMT

hi Thrasso00

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

Re: How to Dynamically Track Issue Status in Power BI

Thrasso00 — Mon, 24 Apr 2023 12:48:40 GMT

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'.

Re: How to Dynamically Track Issue Status in Power BI

FreemanZ — Mon, 24 Apr 2023 13:32:28 GMT

hi Thrasso00

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

Re: How to Dynamically Track Issue Status in Power BI

Thrasso00 — Tue, 25 Apr 2023 06:10:40 GMT

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

Re: How to Dynamically Track Issue Status in Power BI

FreemanZ — Wed, 26 Apr 2023 07:28:51 GMT

hi Thrasso00

could you also add the expected column directly?

Re: How to Dynamically Track Issue Status in Power BI

Thrasso00 — Wed, 26 Apr 2023 09:49:14 GMT

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