Forum Discussion
Show ticket progress status
Hi PBI community!
I am trying to show service ticket status progress or change over time on a PBI report.
I have a normalized table that looks like this:
| ComID | UpdatedOutcomeDate | Outcome |
| 1 | 01/01/2017 | A |
| 1 | 03/01/2017 | B |
| 2 | 01/05/2018 | C |
| 3 | 04/05/2018 | A |
| 3 | 06/05/2018 | C |
So every new status will be added in a new row.
In order to show the most recent status/outcome, I created two calculated columns:
| ComID | UpdatedOutcomeDate | Outcome | MaxDate | IsLatest |
| 1 | 01/01/2017 | A | 03/01/2017 | Older |
| 1 | 03/01/2017 | B | 03/01/2017 | Latest |
| 2 | 01/05/2018 | C | 01/05/2018 | Latest |
| 3 | 04/05/2018 | A | 06/05/2018 | Older |
| 3 | 06/05/2018 | C | 06/05/2018 | Latest |
using these DAX expressions:
MaxDate = CALCULATE(MAX(Com_OutcomeTbl[OutcomeDate]),FILTER(Com_OutcomeTbl,Com_OutcomeTbl[ComID]=EARLIER(Com_OutcomeTbl[ComID])))
IsLatest = IF(Com_OutcomeTbl[OutcomeDate]=Com_OutcomeTbl[MaxDate],"Latest","Older")
What I'd like to do next is to present how the outcome changes over time. I was thinking about presenting it with the Sankey visual but essentially I want it to look like this:
| Count ComID | First Status | Count ComID | Second Status |
| 5 | A | 3 | B |
| 2 | C | ||
| 2 | B | 2 | B |
| 7 | C | 1 | B |
| 6 | C |
My main challenge is how to reorganize the data to fit this use.
Thanks for the help!
5 Replies
Hi kazael
Could you expain more about your expected output? I cannot fully understand the third table. How do the First Status and Second Status and Count ComID come from ?
Regards,
Cherie
Sorry, you're right. It wasn't very clear.
I'm looking for a way to pivot the first table based on the date, so it will look like that:
ComID UpdatedOutcomeDate1 Outcome UpdatedOutcomeDate2 Outcome 1 01/01/2017 A 03/01/2017 B 2 01/05/2018 C 3 04/05/2018 A 06/05/2018 C In addition, I'd like to add columns dynamically in case there are other outcomes and dates added to the same ComID.
Is it possible with DAX? Would it be better to push it to the DB and write it with SQL?
Thank you!
Hi kazael
It seems you may try to add measures for the table as below:
UpdatedOutcomeDate2 = IF ( MAX ( Com_OutcomeTbl[OutcomeDate] ) = CALCULATE ( MAX ( Com_OutcomeTbl[OutcomeDate] ), ALLEXCEPT ( Com_OutcomeTbl, Com_OutcomeTbl[ComID] ) ), MAX ( Com_OutcomeTbl[OutcomeDate] ) )Outcome2 =
CALCULATE (
MAX ( Com_OutcomeTbl[Outcome] ),
ALLEXCEPT ( Com_OutcomeTbl, Com_OutcomeTbl[ComID] )
)Add a filter measure as below and drag it to visual level filter.
Filter = IF ( MAX ( Com_OutcomeTbl[OutcomeDate] ) = CALCULATE ( MIN ( Com_OutcomeTbl[OutcomeDate] ), ALLEXCEPT ( Com_OutcomeTbl, Com_OutcomeTbl[ComID] ) ), 1 )Regards,
Cherie
