Help with Evolution Graph

[gpalfi]

Hi All,

I'm still learning Power Bi/DAX, so I first tried to search for a solution to the problem I'm facing, however I cannot seem to find the right approach. Hence I seek your support.

What I want to achieve is that I can see by day on a column chart how the tickets evolve through the different states.

My model has the following tables that I can see that are needed for this.

• Date/time table
• Table with tickets wich have all the details (one row per ticket) -2000 rows
• Changelog (many-each row is a change) -30000 rows

The relationships are as following:

Date <one-both-many> created - Ticket table - Id <one-both-many>Id changelog

The following sample data is in them:

Ticket table

_Id	Created	Updated	Closed	Status
CS-101	01-08-20	08-08-20		In progress
CS-102	04-08-20	20-08-20		Done
CS-103	20-08-20			Not started
CS-104	12-08-20	27-08-20		Not started
CS-105	14-08-20			Not started

Changelog

_Id	Field	change date	from	to
CS-101	Status	08-08-20	Not started	In progress
CS-102	Status	06-08-20	Not started	In progress
CS-102	Status	15-08-20	In progress	Validation
CS-102	Status	20-08-20	Validation	Done
CS-104	Status	21-08-20	Not started	In progress
CS-104	Status	27-08-20	In progress	Not started

The ticket opens directly in "not started", as there was no change this is not shown in the changelog.

The states can go in order, or the can go back to the prevous state. 

What I want to achieve is the following:

[Anonymous · Accepted solution]

// This task will be much easier if
// you store all your states in
// the Changelog table. That means you
// also have to include a record for any
// ticket that just opened in the "Not Started"
// state (transition from "Not Started" to
// "Not Started." If you don't do this, the DAX will be
// much harder to write, thus slower. The Date table
// that you'll use to visualize the movement of
// tickets through time should NOT be connected 
// to any of the tables (must be DISCONNECTED).
// You can have another Date table that will join
// to Tickets[Created]/[Updated] to let you filter by
// tickets created/updated within a given period of time
// but this dimension will NOT be suitable for
// visualizing movement through time. You can
// name this Date table something like
// 'Ticket Creation/Update Dates'. The other Dates
// table will be called, say, 'Evolution Dates'.
//
// So, if the above is in place, you can
// write a measure that will show you the
// number of tickets on each day (in a given
// status). The day on which we'll calculate
// the number will be the last day of the selected
// period of time from 'Evoution Dates' (as this
// setup makes sense in this case). If you slice
// by status (and 'Evolution Status' must be
// a separate dimension NOT connected to any tables),
// you'll get the number of tickets in this status
// on the last day of the selected period.
// So, Changelog must be a hidden fact table.
// Only Tickets will join to it on Tickets[TicketID].
// No other tables can join to it. If you want
// to be able to slice by status in the Tickets
// table, then use a different dimension, for instance,
// 'Current Ticket Status' and connect it to Tickets.

// By the way, if it can happen that a ticket
// goes through different stages on the same
// day, then you have to be able to say which
// status is the last one on that day. Hence,
// you'll need another column that will tell
// you the temporal seqence of the statuses. For
// the sake of argument let's assume that this
// can't happen and for any one ticket in Changelog
// there can't be 2 rows with the same date.

[# Tickets] =
// To get the number of tickets on the last
// day of the selected period in 'Evolution Dates'
// we have to, for each ticket, find the latest
// record before or on this day and check
// if its status is in the statuses selected
// in 'Evolution Status'. If it's true, then
// count the ticket in, else don't.
var __lastDay =
    MAX( 'Evolution Dates'[Date] )
var __selectedStatuses =
    VALUES( 'Evolution Status'[Status] )
var __result =
    SUMX(
        Tickets,
        // Check if the latest record in Changelog
        // (such that its date <= last date of the
        // evolution period) has a status that is
        // present in the 'Evolution Status' table.
        // If so, count it in.
        var __latestStatus =
            SELECTCOLUMNS(
                // If the assumptions above are
                // satisfied, this will return
                // just one row and __latestStatus
                // will be turned automatically
                // into a scalar. If it happens
                // that this returns more than 1 row,
                // then you've got violations
                // of the assumptions and have to
                // code accordingly to accommodate
                // the changed conditions of the
                // problem.
                TOPN(1,
                    CALCULATETABLE(
                        Changelog,
                        Changelog[Change Date] <= __lastDay
                    ),
                    Changelog[Change Date],
                    DESC
                ),
                "@Status", Changelog[ToStatus]
            )
        return
            1 * ( __latestStatus in __selectedStatuses )
    )
return
    if( __result, __result )

[gpalfi]

Thanks alot for the help 🙂