Show ticket progress status

[kazael]

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!

[v-cherch-msft]

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

[kazael]

Hi @v-cherch-msft

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!

[v-cherch-msft]

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

[kazael]

Thank you @v-cherch-msft for this awesome solution!

How should I modify it in order to add more columns in the future based on the date?

So if my original table looked 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
1	01/01/2018	G
3	01/07/2018	D
3	01/10/2018	F

and I'd like to show it like this:

ComID	UpdatedOutcomeDate1	Outcome	UpdatedOutcomeDate2	Outcome	UpdatedOutcomeDate3	Outcome	UpdatedOutcomeDate4	Outcome
1	01/01/2017	A	03/01/2017	B	01/01/2018	G
2	01/05/2018	C
3	04/05/2018	A	06/05/2018	C	01/07/2018	D	01/10/2018	F

[v-cherch-msft]

Hi @kazael

It seems there is no better way to do that with DAX. Using M language in query editor is a more complex way to do that.

Regards,

Cherie