Heatmap ideas?

[redhughes]

Hi,

Because of how my operations colleagues add appointment data to our CRM, the resulting tables are quite difficult to work with in terms of reporting. Basically we have one line per client and three columns per appointment. A client can have up to 5 appointments in a day so we end up with 105 columns (example below). What we would like to achieve is a daily heatmap that shows us when we are busiest during the day (so that we can match this to our staff work patterns), so proably a graph for Monday showing time on x-axis and required staff in y-axis. Any ideas how to deal with kind of data? Thank you in advance for all the suggestions!

Client	Monday1-start	Monday1-duration	Monday1-staff	Monday2-start	Monday2-duration	Monday2-staff	Monday3-start	Monday3-duration	Monday3-staff
John Smith	09:00	00:30:00	1	13:00	01:00:00	2	19:00	00:45:00	1

[mahoneypat · Accepted solution]

I think your example data had a copy/paste error; the duration values are not consistently in the expected place.  I tried to correct it but not sure if correct.  In any case, please put the M code below into a blank query to see the corrections, but more importantly, one way to transform this dataset for easier analysis/visualizaton.  Once all the right types of values are consistently in each column, you can easily make a Matrix visual look like a heatmap with conditional formatting.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIyMDLQNTDTNTAEcsDY0MrAFMZG40OQAS4Ki3KsakkwFqzUwMoAxDWmngsMcKjHqZhOKFYHGD2Jeamj0YPNIQMeO7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, #"Date effecti" = _t, #"Mon1-carer" = _t, #"Mon1-start" = _t, #"Mon1-durat" = _t, #"Mon2-carer" = _t, #"Mon2-start" = _t, #"Mon2-durat" = _t, #"Mon3-carer" = _t, #"Mon3-start" = _t, #"Mon3-durat" = _t, #"Mon4-carer" = _t, #"Mon4-start" = _t, #"Mon4-durat" = _t, #"Mon5-carer" = _t, #"Mon5-start" = _t, #"Mon5-durat" = _t, #"Tue1-carer" = _t, #"Tue1-start" = _t, #"Tue1-durati" = _t, #"Tue2-carer" = _t, #"Tue2-start" = _t, #"Tue2-durati" = _t, #"Tue3-carer" = _t, #"Tue3-start" = _t, #"Tue3-durati" = _t, #"Tue4-carer" = _t, #"Tue4-start" = _t, #"Tue4-durati" = _t, #"Tue5-carer" = _t, #"Tue5-start" = _t, #"Tue5-durati" = _t, #"Wed1-carer" = _t, #"Wed1-start" = _t, #"Wed1-durat" = _t, #"Wed2-carer" = _t, #"Wed2-start" = _t, #"Wed2-durat" = _t, #"Wed3-carer" = _t, #"Wed3-start" = _t, #"Wed3-durat" = _t, #"Wed4-carer" = _t, #"Wed4-start" = _t, #"Wed4-durat" = _t, #"Wed5-carer" = _t, #"Wed5-start" = _t, #"Wed5-durat" = _t, #"Thu1-carer" = _t, #"Thu1-start" = _t, #"Thu1-durati" = _t, #"Thu2-carer" = _t, #"Thu2-start" = _t, #"Thu2-durati" = _t, #"Thu3-carer" = _t, #"Thu3-start" = _t, #"Thu3-durati" = _t, #"Thu4-carer" = _t, #"Thu4-start" = _t, #"Thu4-durati" = _t, #"Thu5-carer" = _t, #"Thu5-start" = _t, #"Thu5-durati" = _t, #"Fri1-carer" = _t, #"Fri1-start" = _t, #"Fri1-duratio" = _t, #"Fri2-carer" = _t, #"Fri2-start" = _t, #"Fri2-duratio" = _t, #"Fri3-carer" = _t, #"Fri3-start" = _t, #"Fri3-duratio" = _t, #"Fri4-carer" = _t, #"Fri4-start" = _t, #"Fri4-duratio" = _t, #"Fri5-carer" = _t, #"Fri5-start" = _t, #"Fri5-duratio" = _t, #"Sat1-carer" = _t, #"Sat1-start" = _t, #"Sat1-durati" = _t, #"Sat2-carer" = _t, #"Sat2-start" = _t, #"Sat2-durati" = _t, #"Sat3-carer" = _t, #"Sat3-start" = _t, #"Sat3-durati" = _t, #"Sat4-carer" = _t, #"Sat4-start" = _t, #"Sat4-durati" = _t, #"Sat5-carer" = _t, #"Sat5-start" = _t, #"Sat5-durati" = _t, #"Sun1-carer" = _t, #"Sun1-start" = _t, #"Sun1-durati" = _t, #"Sun2-carer" = _t, #"Sun2-start" = _t, #"Sun2-durati" = _t, #"Sun3-carer" = _t, #"Sun3-start" = _t, #"Sun3-durati" = _t, #"Sun4-carer" = _t, #"Sun4-start" = _t, #"Sun4-durati" = _t, #"Sun5-carer" = _t, #"Sun5-start" = _t, #"Sun5-duration" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Client", "Date effecti"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Attribute.1", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute.1.1", "Attribute.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1.1", "Day"}, {"Attribute.1.2", "Appointment"}}),
#"Extracted First Characters" = Table.TransformColumns(#"Renamed Columns", {{"Attribute.2", each Text.Start(_, 5), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted First Characters","carer","staff",Replacer.ReplaceText,{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"start", type time}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"start", null}})
in
#"Replaced Errors"

You can use your columns as rows/column in the Matrix, and calculate with the duration or staff columns, and use conditional formatting to get the heatmap look.  Please see this link for more details on how to do that.

https://www.youtube.com/watch?v=aCq9Nz8K_Cs

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[redhughes]

@mahoneypatand what if the source of data was an XML field with a URL like this one?

https://www.globiflow.com/podiofeed.php?c=4444&a=666666&f=4444

Thank you in advance!

[mahoneypat]

I don't see any data at that link.  In any case, if it is structured like your example data, the approach would be the same.

Regards,

Pat

[redhughes]

@mahoneypat  - again thanks for your solution, it's perfect. just one more question. If I end up with 1 line per appointment, and I have their start and end times, how can I then use it to create a heatmap? i.e. how will PowerBI know that there's X appointments happening on Mon at 1pm. I thought I could create columns for each time (e.g. 10am) and have calculations there to say if (10am is) higher or equal to start time AND (10am) is less than end time. However, that's a lot of columns... Is there another solution?

[mahoneypat]

You shouldn't need to make extra columns.  Typically, I do this by have a disconnected table with the time (or date) values that will go in the columns.  Then you can right a measure that counts the # of events with start date/time < max date/time (from the disconnected table) and end date/time >= min date/time (from disconnected table).  You can first store those max and min of the disconnected table as variables and then use them in the Filter().

Regards,

Pat

[redhughes]

@mahoneypat  - thanks again for your advice, greatly appreciated. I'm learning a lot while researching stuff like 'disconnected tables'. However, I'm still struggling...

I've created a disconnected table with 7am-11:30 pm times, and the measure below. However, I'm unable to link the weekdays from the main source of data into this. Do I have to create sets of times for each day of the week and then somehow use those in additional filters?

Even if that succeeds, I have another issue: the main data has client info (e.g. branch, area) that I'd love to use as filters. However, as the tables are not connected, I can't currently use them. Is there a way around this?

Apologies for all the noob questions, but it seems I've stumbled upon my biggest Power BI challenge yet... Again, thank you for all your advice! Really appreciate it.

time-heatmap =

CALCULATE (

sum('item'[staff]),

FILTER (

ALL ( 'item' ),

'item'[start] <= max('Time'[time])

&& 'item'[end] > min('Time'[time])

)

)

[mahoneypat]

Glab you are making progress and moving out of Noobtown.  So that you can use the other columns on the Item table as slicers, etc., you just need to not remove those filters in your measure.  By having ALL('item') you are getting rid of any filters on that table.  You can just remove the filters on start and end time as follows

time-heatmap =

CALCULATE (

sum('item'[staff]),

FILTER (

ALL ( 'item'[start], 'item'[end] ),

'item'[start] <= max('Time'[time])

&& 'item'[end] > min('Time'[time])

)

)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[redhughes]

@mahoneypat - I think I'm pretty much there. thank you for all your guidance! one last thing is whether you think it's possible to summarise somehow the below table (they show the measure we created together earlier per "shift" and per day) so that we only have the lowest value for each shift (AM and PM) for each day:

[mahoneypat]

Not totally sure what you mean.  Can you explain/show what your new visual would look like?  You want to know the earliest appointment in the AM and in the PM for each day?  Or each client?

Regards,

Pat

[redhughes]

@mahoneypat - no, I would like to know the lowest value for the measure we've created per day, per shift. For the example, the large detailed table is at the bottom, and the summarised table I'd like to get would like like this:

Day/Shift	AM	PM
Mon	-11	-7
Tue	-8	-6
Wed	-8	-5
Thu	-9	-5
Fri	-11	-7
Sat	-10	-6
Sun	-9	-4

[mahoneypat]

Please try this expression

Min Value = MINX(VALUES('time'[Time]), [time-heatmap])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[redhughes]

@mahoneypatthank you! will have a go later this week!

[RobbeVL]

Hi,

Ideally you transform your data so you have a Single Date/Time Column.

I only dont see any date specified?  Is there a way to trace back the month/year/day ? Is this a weekly extract you receive?...

If your dataset grows, you might also reach some column limitations. 

If you get me some more info, id be happy to help you create an initial transformation.

Robbe

[redhughes]

@RobbeVLhere's a link to an example file:

https://www.dropbox.com/s/5fp2tp2k2rzcebd/Visit%20schedules%20-%20example.xlsx?dl=0

[redhughes]

Basically every client has one active weekly appointment schedule. Therefore there's no need for dates - every week their visits will be the same. Also, because of that the number of columns should stay the same. I will post an example data extract later today. Thank you so much for your help!!