Forum Discussion
Convert List Table into usable data
Yes, exactly what you've said.
To confirm it is going to work before you go to too much effort, you could just duplicate the main fact table and call one 'open' and one 'closed', join on the date table and build the visuals making sure you use the date from the date table. You may need to add a date only column for the resolve date so you have an appropriate type to join on (date to date).
One thing I noticed from the screenshots, you may want to add a YearWeek column as an integer, e.g. 202119 this will give you something to use as a 'Sort by' column for your 'Year Week' text column (although may not be necessary given the way you've formatted it).
Making this change to have two fact tables joined by the date table is what I'm referring to as "appropriate".
Let me know if you want the code for a PQ version of a date table.
Hi PantherusNZ,
Do you have a date dimension in your model?
Are you able to share a screenshot of your model showing the relationships?
My gut feeling is, splitting the open and closed data with an appropriate data model will make the reporting easier but really need a little more detail.
Regards,
Kim
Hi,
I created a Date dimension previously using instructions I found to do so in DAX, set it as the date table and built the hierarchy. I had it working reasonably well although the data wasn't quite right (a single item was opened back in 2018 and just closed a couple of weeks ago, but in the graph produced it showed 1 item in 2018 for BOTH opening and closing, even though nothing closed in 2018. The hierarchy I had produced was very messy and it wouldn't let me edit it so I deleted and rebuilt a simpler one, but now it doesn't work at all so I've definitely broken something...
So you think if I create two separate tables, one with the appropriate opened items, and one with the appropriate closed items, relate them both to the date table, and then bring them together in the visualisation? What is the "appropriate data model"?
Yes, exactly what you've said.
To confirm it is going to work before you go to too much effort, you could just duplicate the main fact table and call one 'open' and one 'closed', join on the date table and build the visuals making sure you use the date from the date table. You may need to add a date only column for the resolve date so you have an appropriate type to join on (date to date).
One thing I noticed from the screenshots, you may want to add a YearWeek column as an integer, e.g. 202119 this will give you something to use as a 'Sort by' column for your 'Year Week' text column (although may not be necessary given the way you've formatted it).
Making this change to have two fact tables joined by the date table is what I'm referring to as "appropriate".
Let me know if you want the code for a PQ version of a date table.
THANK YOU!! Splitting the data into separate tables, linking the two for Date relationships and building from there has worked perfectly. Thank you!!