Forum Discussion
gpalfi
5 years agoRegular Visitor
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...
- Anonymous5 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 )
Anonymous
5 years agoNot applicable
// 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
5 years agoRegular Visitor
Thanks alot for the help 🙂