Help With Event In Progress Visualization

[russell80]

I'm trying to create a stacked column chart which shows for each day over the last 28 days (or a period defined by a slicer) how many records were created, closed or in progress each day. The records are held in a table which has a date column for "date created" and "date closed" (which is blank if in progress).

I've followed the guide on daxpatterns for events in progress to create a measure to count the number of records which were in progress each day (https://www.daxpatterns.com/events-in-progress).

My measure

# Event In Progress = 
VAR MinDate = MIN ( 'Date_table'[Date] )
VAR MaxDate = MAX ( 'Date_table'[Date] )
VAR Result =
CALCULATE (
COUNTROWS ( ft_records ),
ft_records[Created Date] <= MaxDate,
ft_records[Closed Date] > MinDate
|| ISBLANK ( ft_records[Closed Date] ),
REMOVEFILTERS ( 'Date_table' )
)
RETURN
Result

I created subsequent measures to count the created and closed records. The created measure just looked at the "Created Date" column and the closed measure counts the records with a closed state and uses the "last update" column to get the closed date.

Adding these 3 measures to the values of the stacked column chart visual and the date_table date to the axis, I get the visual I'm looking for (a stacked column for each day over the last 28 days showing the count of created, open & closed records for each day) but I'm not getting the interation I want from the visual. I want to be able to select on the chart the create/open/closed record count on a specific day and then filter a table with the records on it to show just those selected e.g. all records closed 14 days ago.

This has really got me stupmed and I'm not sure how to move forward from here, so any help would be welcome.

[millerz1]

I'm not able to get this solution to work.  Any suggestions or thoughts would be greatly appreciated.

Defects Open = 
    VAR EventsCreated =
        CALCULATETABLE(
            VALUES('R3 0 Hypercare List'[Id]),
            FILTER(
                ALL('R3 0 Hypercare List'),
                'R3 0 Hypercare List'[Created] <= MAX('Dates'[Date])
            )
        )
    VAR EventsPreviouslyClosed =
        CALCULATETABLE(
            VALUES('R3 0 Hypercare List'[Id]),
            FILTER(
                ALL('R3 0 Hypercare List'),
                'R3 0 Hypercare List'[Date Closed] < MIN('Dates'[Date])
            )
        )
    RETURN

    COUNTROWS(
        EXCEPT(
            EventsCreated,
            EventsPreviouslyClosed
        )
    )

[v-kelly-msft]

Hi @millerz1 ,

Would you pls create a new thread about your issue?We will follow it at the first time.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft]

Hi @russell80 ,

So your issue is solved ,right?

If so,could you pls mark the reply as answered to let more people find the solution?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[russell80]

Looks like we were posting at the same time 😁

I'll take a look at your post later tonight once I get a chance to read it 👍

[russell80]

This may not be the ideal solution but it does what I need and I'm pretty chuffed I managed to figure this out 👍

I re-read the article I posted in the first post and it talked about creating a table first to help do with the calculations, so that's what I tried. I created 3 tables using DAX, one for "Created" records, one for "Open" and one for "Closed" and a 4th table (record history) to Union them together. By adding additional columns to the tables to capture the record state and a value for summing in the visual, I got something that works. I had to duplicate the record table so I could create a relationship between it at the "record history" table, so when I clicked on the visual I could get the records associated with the selection (not sure if this has any impact).

You can download the pbix file here: Download 

[littlemojopuppy]

Glad you got it figured out, but that's not a great solution.  You're creating tables with duplicate info which wastes memory (everything in Power BI is done in RAM).  You may begin to see performance issues as the volume of data grows.  What I offered will do it in real time when used in visualizations and otherwise not impact RAM at all.

By the way...if you'd be interested I can offer recommendations for some very good courses on Udemy, etc. that will help you learn Power BI, DAX, etc.  This stuff is not easy to learn, and at times very painful 😫.

[russell80]

Sorry for my response coming across as a lecture, it really wasn't my intention, so for that please accept my sincere apologies. I also appreciate that you don't need to spend your time helping strangers on the internet, for that I'm also very grateful.

If I'm honest, I'm not sure I'll figure this out any time soon. It's only in the last few weeks I've started using Power BI and have been learning about data models, DAX, queries etc... and the learning curve has been very steep (the Guy In A Cube series on YouTube has been pretty helpful!). However, if I do find a solution to this and I remember this post, I'll make sure to reply so hopefully it's of help to others.

Thanks again for all your help!

[littlemojopuppy · Accepted solution]

Howdy!

Thank you for the apology.  🙂

I found this article that might interest you.  But I took a simpler approach to the problem.  Here's the code:

Events Open in Period = 
    VAR EventsCreated =
        CALCULATETABLE(
            VALUES(ft_records[Record_ID]),
            FILTER(
                ALL(ft_records),
                ft_records[Start Date] <= MAX(Date_table[Date])
            )
        )
    VAR EventsPreviouslyClosed =
        CALCULATETABLE(
            VALUES(ft_records[Record_ID]),
            FILTER(
                ALL(ft_records),
                ft_records[Finish Date] < MIN(Date_table[Date])
            )
        )
    RETURN

    COUNTROWS(
        EXCEPT(
            EventsCreated,
            EventsPreviouslyClosed
        )
    )

What we're doing here is creating a couple of table variables.  The first one is all events opened on or before the last date in a month.  The second is all events closed prior to the month in question.  I'm working with the assumption that an event will be open - even if only for an instant - if it happened to be closed on the same day.  The EXCEPT() function returns rows from one table that are not found in the other, and then we just count the rows.  Here's what the result looks like.

I spot checked this in Excel by manually counting records for June and July and it appears accurate.  But you should probably check a little more just to be sure.

You can download the PBIX here.

[littlemojopuppy]

I've put some thought into how to reply to this...

In your original request you stated "This has really got me stupmed and I'm not sure how to move forward from here, so any help would be welcome."  I am help.

In subsequent replies you stated:
"Thanks for taking the time to look at this, I've downloaded your pbix file and had a look at what you've done but it's not quite doing what I want." and "In your snippet I want to be able to select the point on the line chart for 45 in progress events and the record table on the left to filter and show just the 45. Similarly do the same for closed and created records. In your snippet it shows all the records and doesn't filter any further."  I asked for further clarification because you didn't provide a whole lot of context to work with so I was giving it my best guess.  I asked a couple of clarifying questions because what you're asking for doesn't make sense...see my last response.  And the most complete definition of context came in what came across as a lecture.  So not cool.

You need to realize that people like myself to respond in forums such as this are volunteering their time and effort for your help.  I'm sure you'll get this figured out.  Good luck!

[russell80]

Here's the correct link: Download 

[littlemojopuppy]

Would it be an accurate statement that what you're looking for is the state of all events that existed in a given time period?  Is that what you're after?

Need some clarification as well...your stacked column has three states: created, in progress and closed.

• Created: an event generated during a time period.
• In progress: defined as finish date is blank.  May have been created in the current or a prior period.
• Closed: defined as finish date is not blank.  May have been created in the current or a prior period.

Your counting seems very strange to me...Created is straight forward.  But In Progress and Closed will double count events created this month because an event will either be closed in the current period or carry forward into the next one.  Are you sure this is what you're looking for???

[russell80]

---

@littlemojopuppy wrote:

Would it be an accurate statement that what you're looking for is the state of all events that existed in a given time period?  Is that what you're after?

---

Yes, that's right. Think of it like an IT ticketing system, where each record is a ticket which is created and remains open until it is closed. I want to show for any time period, how many tickets were opened and what tickets theses are, how many were closed and what tickets these are and how many were opened but not closed and what tickets these are. For example, I would like to look back at every day for the last month and for each day be able to see which tickets were created that day, which were closed and which were created some time in the past but are not closed or were closed after that day. I would like to be able to see this on a chart and be able to select a particular day and then select a particular state (created, open, closed) and see the associated records e.g. I want to be able to answer questions like: show me all records which were open on 1st Dec or show me all records which were closed on 4th Dec or show me all records which were created on 29th Nov.

So far we've been able to show all the records (created, open, closed) for a particular time period but we haven't been able to filter that further to show just the created/open/closed by using a visual to filter for them.

[russell80]

In your snippet I want to be able to select the point on the line chart for 45 in progress events and the record table on the left to filter and show just the 45. Similarly do the same for closed and created records. In your snippet it shows all the records and doesn't filter any further.

[russell80]

OK, I've created a new page in the pbix file "Example". I've taken the date from the record table when filtered for October and added the extra info in a new csv to show what I'm trying to do.

Now in the stacked column visual, if I click the Closed part of the column, the record table filters and shows just the records which were closed during the period (October). Likewise, if I click on In Progress or created, the same thing happens to the reocrd table and the records are filtered to show just those which were created in the period or were still open by the end of the period.

New PBIX file Download 

[littlemojopuppy]

This seems to be the same file from earlier...

[russell80]

Thanks for taking the time to look at this, I've downloaded your pbix file and had a look at what you've done but it's not quite doing what I want.

I've added a table visual to the report page you created which lists all the records. What I want to happen is when I select either of the columns or the line, the records associated with that value are filtered and shown on the table. What happens at the moment is I'm not sure but it looks like the records are filtered only for the month selected on the chart and not the measure. Is there a way I can get the records in the table to filter e.g. when I select the closed events column in November, the table shows all the records which were closed in November and just those records?

PBIX Report with record table: Download 

[littlemojopuppy]

Hi -

Going to need you to explain what you mean by "what I want to happen is when I select either of the columns or the line, the records associated with that value are filtered and show" because that's what it's doing.  Screen snip is filtered for Oct 2020 and it's showing everything that was created, closed or in progress during October 2020.

[littlemojopuppy]

Ok...so I played with the PBIX you provided.  A couple things...

First, you need two relationships between the date table and ft_records, one on StartDate (primary I assume) and one on FinishDate.

Second...your measures seem unnecessarily complicated.  I created the following

Events Created = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        USERELATIONSHIP(Date_table[Date], ft_records[Start Date])
    )

Events Closed = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        NOT(ISBLANK(ft_records[Finish Date])),
        USERELATIONSHIP(Date_table[Date], ft_records[Finish Date])
    )

Events in Progress = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        ISBLANK(ft_records[Finish Date])
    )

Events Created Running Total = 
    CALCULATE(
        [Events Created],
        FILTER(
            ALL(Date_table),
            Date_table[Date] <= MAX(Date_table[Date])
        )
    )

Events Closed Running Total = 
    CALCULATE(
        [Events Closed],
        FILTER(
            ALL(Date_table),
            Date_table[Date] <= MAX(Date_table[Date])
        )
    )

Events in Progress Alternate = [Events Created Running Total] - [Events Closed Running Total]

The last one is clearly because I'm a huge fan of keeping it very simple.

This is a wall of numbers showing month by month each of the measures above...

Here's a combo clustered column/line showing events created vs closed as columns and those in progress (alternate/easy calculation) as the line.

I'd argue with you that your outstanding as an element in a stacked column hides the true meaning of what you're trying to project...events created, closed and outstanding.  Having it separately on the line calls out "here's what's left to be done".

You can download my revised PBIX here.

[littlemojopuppy]

Provide a sample PBIX showing what you've got so far?

[russell80]

I've created an example pbix which shows how far I've managed to get

https://drive.google.com/file/d/1A9j298wXmtPOKMoBipfsoPb_lyt770F3/view?usp=sharing