Forum Discussion
Heatmap ideas?
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
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
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?
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
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])))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