topic Re: Help with counters by day and by status on weekly chart in DAX Commands and Tips

Help with counters by day and by status on weekly chart

justuser123 — Wed, 25 Mar 2020 22:35:10 GMT

Hi everyone!

I have a little issue and hope to get a good advice.

For a simplicity, let say I have a model with the three tables.

Incident table “Incidents” with a unique Incident ID (UID) field.
Status change table “Statuses” with the fields: Incident ID (UID); Start Date (date and time), End Date (date and time), Status (numeric).
Standard calendar Date table.

Incidents table is linked as 1:* to Statuses table. Date table is standalone and not linked to any table.

The task is to build a clustered column chart with week days on X axis (Sunday to Saturday) and display the numbers (values) of incidents by status and day. Since this task is repeatable for multiple statuses, I just need an assistance in calculating this numbers for one incident status.

Let say it is a number of open incidents per day. Incident is open if its status is less than 9 on the specific day. Therefore, I need to show numbers of open incidents on Sunday, Monday, Tuesday, etc.

I also have a slicer by weeks based on the Date table. User should be able to select any week in current or past year and see the number of open incidents per day on the selected week.

One more. Statuses table might have multiple records with same incident and status, i.e. incident can be closed, reopened, closed again etc. Sometimes multiple activities can happen on the same day, i.e. start and end dates could be equal for multiple statuses of the single incident but the time is different. Therefore, the max status number must be selected according to the date and time.

Example of Statuses table:

Incident ID Start Date End Date Status

ABC 03/23/2020 08:20:12 am 03/23/2020 08:25:24 am 1

ABC 03/23/2020 08:25:24 am 03/23/2020 10:21:44 am 3

ABC 03/23/2020 10:21:44 am 03/23/2020 11:25:24 am 9

ABC 03/23/2020 11:25:24 am 03/23/2020 05:00:01 pm 4 Last record on 03/23

ABC 03/24/2020 09:10:32 am 12/31/2078 12:00:00 am 10 Incident closed

It means that on Monday 03/24 Incident ABC should be counted as open because of the last status 4.

However, on Tuesday 03/24 it should not be counted as an open. Of course, on any day before 03/23 incident should not be counted at all.

If a using time makes task more complicated then we can skip it and just pull the max status number for a day regardless of time. I can tolerate a slight discrepancy if ABC will be counted as closed (status 9) on 03/23 because of not so many cases like this one but it’s better to do it right.

Thank you very much in advance 😊

Re: Help with counters by day and by status on weekly chart

Greg_Deckler — Thu, 26 Mar 2020 02:16:24 GMT

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Re: Help with counters by day and by status on weekly chart

justuser123 — Thu, 26 Mar 2020 20:47:21 GMT

Greg_DecklerThank you very much. This link gave me some ideas but did not resolve the problem. Besides, my client wants to count incidents by the latest status for the day. Therefore, I'm still working on it. If I could mark your post as a partial solution I would've done it 🙂

Re: Help with counters by day and by status on weekly chart

justuser123 — Sun, 29 Mar 2020 15:27:33 GMT

I still need an assistance in this project so I tried to simplify the input data.

The new table MaxStatusPerDay was added into the model.

Table has three columns: Incident ID, Date, MaxStatus and linked to Incidents table as *:1. It looks something like this:

Incident ID Date MaxStatus

123 03/19/2020 4

123 03/20/2020 9

123 03/22/2020 3

123 03/26/2020 9

345 03/22/2020 2

345 03/25/2020 9

345 03/26/2020 5

345 03/27/2020 9

The goal is still same: count number of open incidents per day and display it on weekly chart. Weekly chart is based on standalone Date table.

Incident is open when its status is less than 9. Therefore, on 03/19/2020 incident 123 is open, 345 is not counted (did not exist), number of open = 1, and so on:

Date Number of Open

03/19/2020 1

03/20/2020 0

03/21/2020 0

03/22/2020 2

03/23/2020 2

03/24/2020 2

03/25/2020 1 345 closed, 123 open

03/26/2020 1 345 open, 123 closed

03/27/2020 0

Any date after 03/27/2020 or before 03/19/2020 the number of open incidents is 0.

Any advice will be greatly appreciated.

Thank you.