topic Re: Keeping track of the last status in tickets in DAX Commands and Tips

Keeping track of the last status in tickets

Anonymous — Tue, 01 Aug 2023 13:31:20 GMT

Hi all

I have a fairly simple data model, a table "Issues", the "StatusChanges" which tracks all changes in status with a timestamp for each ticket, and finally a calculated table "IssueStatusHistory". The last table is meant to track the latest status for each ticket and month. That should let me go back in time to see how many tickets have had state.

The column Status in IssueStatusHistory is what I want to use for that. It is important to note that the issues can switch status more than once during a month, so that is why the "statusChanges" table also contains the column "last status in month".

The problem now comes from the fact that an issue might be set to done in one month, but is then set to "To Do" again in a subsequent month. Also, some issues do not have any changes for more than one month (From Jan to April in the example below), and I want to make sure I populate those values for as long as the issue is in that status. As illustrated in the screenshots, the issue filtered in the table was changed to "Done" in May 2022, but then set to "To Do" in June. How can I change my Status Formula below to make sure I will always get the most recent change as per the StatusChanges table?

Thanks for the help!

IssueStatusHistory:

StatusChanges:

Re: Keeping track of the last status in tickets

rubayatyasmin — Wed, 02 Aug 2023 06:10:18 GMT

Hi, Anonymous

how about using LSTNONBLANK dax func to return the latest status change value?

just to give you an idea: Might need to adjust the column names.

Latest Status =
CALCULATE(
LASTNONBLANK(StatusChanges[Status], StatusChanges[CREATED]),
FILTER(
StatusChanges,
StatusChanges[Issue_KEY] = EARLIER(Issues[Issue_KEY]) &&
StatusChanges[CREATED] = MAX(StatusChanges[CREATED])
)
)

refer:

https://learn.microsoft.com/en-us/dax/lastnonblank-function-dax

Re: Keeping track of the last status in tickets

Anonymous — Wed, 02 Aug 2023 09:14:37 GMT

Hi rubayatyasmin Thanks for the input, that might actually work, but somehow in only get blanks in return. I tried both LASTNONBLANK() and LASTNONBLANKVALUE(). Might be that something is wrong with the filtering?

Here's my last attempt:

Status = IF( [Created IDMonth]<[IDMonth], CALCULATE( LASTNONBLANK(StatusChanges[Last Status in Month],StatusChanges[Last Status in Month]), FILTER( StatusChanges, StatusChanges[ISSUE_KEY] = EARLIER(IssueStatusHistory[ISSUE_KEY])&& StatusChanges[CREATED]=MAX(StatusChanges[CREATED]))), "Not created yet")

Just to put some context I need to have the value in StatusChanges[Last Status in Month] put into the IssueStatusHistory[Status] column.

Re: Keeping track of the last status in tickets

Anonymous — Wed, 02 Aug 2023 10:00:07 GMT

Finally got it to work, here's the final DAX:

Status = IF( [Created IDMonth]<[IDMonth], CALCULATE( LASTNONBLANKVALUE(StatusChanges[CREATED],MAX(StatusChanges[Last Status in Month])), FILTER(StatusChanges, StatusChanges[ISSUE_KEY]=IssueStatusHistory[ISSUE_KEY]&& StatusChanges[IDMonth_changes]<=IssueStatusHistory[IDMonth])), "Not created yet")

The filtering based on IDMonth was still necessary, EARLIER() not really as each cell will run a table scan (as per my understanding). Finally, using LASTNONBLANKVALUE on Last Status in MOnth based on CREATED solved it. Thanks for the input rubayatyasmin

Re: Keeping track of the last status in tickets

rubayatyasmin — Wed, 02 Aug 2023 10:49:42 GMT

Good to know that the reply helped. Anonymous congratulations on solving your problem.