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.
There's a better way. Learn about inactive relationships and USERELATIONSHIP() in measures. Then use a proper calendar table, link it (active) to the created date and (inactive) to the close date.
Then write down your business rules for your scenarios:
- created and closed in this interval
- created but not closed in this interval
- closed in this interval but created earlier
Once you decide how to handle each of these, write your measure accordingly.
Thank you. Do you have any particular resources in learning about inactive relationships and USERELATIONSHIP() in measures?
Actually measures in general I haven't explored yet.
As for business rules - I want to show counts (by week) of items created and closed each week in a given timeframe - so if they were opened outside of this timeframe, they do not show up in open counts, but their closure is included accordingly, and if they've been opened in the timeframe but not closed yet then do not show up in the "(blanks)" column (do not want a blanks column at all! 🙂 )
