Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
Solved! Go to 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 )
// 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 )
Thanks alot for the help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |