cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PantherusNZ
Frequent Visitor

Convert List Table into usable data

I have a data import which lists the items created and closed in a given timeframe, which I want to display as a clustered graph in PowerBI, showing the items created and closed in separate columns in a cluster, separated by week.  My trouble is that, within that timeframe, not all items created have been closed, and not all closed items were created in that timeframe - this means that my attempts at displaying that data thus far have consistently displayed warped data with a (blanks) column invariably showing up, even with the box unticked to be displaying them.

 

This is what the data itself looks like:

PantherusNZ_0-1621297597121.png

 

I've been working to transform the data for week separation (I have another topic open about being unable to get a hierachy working correctly) and have got very close by creating a custom column called "Year Week" which produces a string result showing the year and weeknum value for the date (e.g. 2021 - 5) with which I was very close to getting exactly the graph I wanted, except for the (blanks) colum sticking out like a sore thumb.  I believe this is due to the "Shared Axis" as the key issue as I do not have a proper colum reference that is suitable for both the created and closed data - if I used created then it excludes closed ones, and vice versa.  I tried using a Date table's reference but that didn't correctly wrap the data and every single column was the total number of each column for every entry.

 

PantherusNZ_1-1621298305713.png

 

To that end, I think I need to somehow convert my data so that I have a proper "parent" list of the relevant weeks to properly wrap the values for created and closed items and then produce the graph from there.

 

Is that the correct approach?

Is there some other way to better produce this visual, for the data I have?  I'm happy to adjust the data to make it work better in Power BI if necessary, I just don't want to spend a lot of time on a change that won't work (e.g. one theory was I'd need to have the created items and closed items in separate tables, but a small attempt at this approach seemed to be even worse)

 

Thank you

1 ACCEPTED SOLUTION
KNP
Responsive Resident
Responsive Resident

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.

View solution in original post

7 REPLIES 7
KNP
Responsive Resident
Responsive Resident

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

PantherusNZ
Frequent Visitor

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...

PantherusNZ_0-1621396940057.pngPantherusNZ_1-1621396958287.pngPantherusNZ_2-1621397016131.pngPantherusNZ_3-1621397041473.png

 

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"?

KNP
Responsive Resident
Responsive Resident

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.

View solution in original post

PantherusNZ
Frequent Visitor

THANK YOU!!  Splitting the data into separate tables, linking the two for Date relationships and building from there has worked perfectly.  Thank you!!

PantherusNZ_0-1621486509048.png

 

Ashish_Mathur
Super User III
Super User III

Hi,

I'd like to help.  Share a smaller dataset and show the expected result in a Table format.  Once my answers tally with what you show in the expcted result, we can always switch to our desired visual.  Also, since you want to show your visual by weeks, please also share a 2 column Caledar table with Date in the first column and week numbers in the second column.  The Dates in the Calendar column should cover the span of dates in the 2 date columns appearing in your dataset. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User III
Super User III

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! 🙂 )

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors