Condense Line Graph Values

[Anonymous]

My data table (DataTable) has a series of rows. Each one represents an issue and each issue contains a DateCreated, IssueType, and IssueCreater column. The DateCreateded column is a full time stamp that includes the time the issue was created. I've extracted the time at which the issue was created into the column TimeOfDay.

My goal is try and see if there is a certain time of day where there are more inssues being created.

Currently when I try to make a line graph, I get an insanely long graph that has a column for each row (300,000+). Normally, I would set some interval for the displayed x-axis and the program would "zoom out" and display the all the data within the a range.

Any help is appreciated!

[mQc]

HI @Anonymous

I am having the same issue. did you ever find a solution to your problem? 

I want to see the entire time frame at once, just 'zoomed out' so that all data points are included in one view and i do not have to scroll to the right.  A time slicer would not work for what I need. 

Would love to hear if you were able to find a solution

[Anonymous]

@mQc, unfortunately I haven't. After posting, the business owner didn't see the value in that visual so it was scrapped from our report.

I'll try to revisit it today and see if I'm able to find anything out since last posting.

[mQc]

@Anonymous okay thanks! 

i was able to get some help from a colleague who recommended i convert the data type for that column to data.  power Bi then automatically grouped the x axis labels by month and displayed the graph as one more compact visual. in case this comes up for you in the future! 

i am definitely a novice so sorry I dont have a better vocabulary to explain exactly how the dimensions changed but that worked for me!

[Anonymous]

@mQc, I might have figured something out.

For reference in my instructions, our data table will be called DataTable with the columns DataDate and ItemName.

1. First we have to create a time table, which I did using the below Power Query which I'll call TimeTable:
   

   let
       Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,1,0)),
       convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"DayTime"}, null, ExtraValues.Error),
       createTimeKey = Table.AddColumn(convertToTable, "TimeKey", each Time.ToText([DayTime], "HHmmss")),
       hourIndex = Table.AddColumn(createTimeKey, "HourIndex", each Time.Hour([DayTime])),
       minuteIndex = Table.AddColumn(hourIndex, "MinuteIndex", each Time.Minute([DayTime])),
       setDataType = Table.TransformColumnTypes(minuteIndex,{{"DayTime", type time}, {"TimeKey", type text}, {"HourIndex", Int64.Type}, {"MinuteIndex", Int64.Type}})
   in
       setDataType

2. Next I'll join the 'DataTable'[DataDate] to the 'TimeTable'[DayTime] column of our time table. If you're starting with a datetime data type instead of a time type column in your dataset like I did, follow the below to create a time type column
   
   1. In the Query Editor, convert the 'DataTable'[DateTime] column to Text
   2. Split the column by delimiter, using the leftmost space character
   3. Convert the new time column to the time datatype (We'll call this new column DataTime.Time)
   4. Because I was tracking seconds in my time, but not in my time table, I then created a custom column in DAX to remove the seconds:

      DataTime.CustomTime = FORMAT('DataTable'[DataTime.Time],"hh:mm:00 AM/PM")

   5. The relationship will now be between 'DataTable'[DataTime.CustomTime] and 'TimeTable'[DayTime].
3. Now, I created buckets in the 'TimeTable' to group our values by 15 minute intervals (though this can be whatever you want)
4. Right-click on the 'TimeTable'[DayTime] and select New Group
5. Here you can create bins that will automatically group your data in increments
6. Once complete, use your new binned column as the axis in your bar chart or line graph

 

[v-ljerr-msft]

Hi @Anonymous,

If I understand you correctly, you should be able to use the DateCreated column as a Slicer on the report to display the the data within a specific date range.

Regards