Forum Discussion

gpalfi's avatar
gpalfi
Regular Visitor
5 years ago
Solved

Help with Evolution Graph

    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 suppo...
  • Anonymous's avatar
    Anonymous
    5 years ago
    // 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 )