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 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!
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
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 |
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
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |