Forum Discussion
Show ticket progress status
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