Forum Discussion
Reshape data
Hello folks,
I'm struggling with some data I need to "shape". Basically I have a table with a single row for each support ticket that is raised. The row has a "Date Created" field and a "Date Resolved" field. To allow me to use the data more easily I'd like to be able to have a duplicate row for each day that the ticket remains open.
The original table is:
Ticket Number Date Created Date Resolved Agent Name
135118 01/03/2017 05/03/2017 Michael
135119 09/03/2017 10/03/2017 Stuart
The new table should be:
Ticket Number Date Created Date Resolved Agent Name
135118 01/03/2017 05/03/2017 Michael
135118 01/03/2017 05/03/2017 Michael
135118 01/03/2017 05/03/2017 Michael
135118 01/03/2017 05/03/2017 Michael
135118 01/03/2017 05/03/2017 Michael
135119 09/03/2017 10/03/2017 Stuart
135119 09/03/2017 10/03/2017 Stuart
Has anyone got any ideas on the best way to achieve this?
Thanks!
Bailey
Hi Anonymous,
You could try creating a New Table in DAX and use the following code
Table Expanded = FILTER( CROSSJOIN('Table',CALENDARAUTO()), [Date]>='Table'[Date Created] && [Date] <='Table'[Date Resolved] )
2 Replies
Hi Anonymous,
You could try creating a New Table in DAX and use the following code
Table Expanded = FILTER( CROSSJOIN('Table',CALENDARAUTO()), [Date]>='Table'[Date Created] && [Date] <='Table'[Date Resolved] )Thanks Phil! Perfect! It almost looks too simple but it works perfectly. Thanks again for taking the time to provide me with an answer.