Forum Discussion
Create a Heat Map with a matrix with multiple variables (or comma separated values in one parameter)
Hey Anonymous. Let me know if the following will work.
Notes:
- The main piece of this is unpivoting the dataset into key-value pairs (see below screenshot).
The query in the red rectangle is the original dataset
The query in the blue rectangle is a reference query of the original and is unpivoted. This is the one that is used in the matrix.
The green rectangle contains the Attribute column (key) and the Value column
The purple rectangle contains the Attribute Sort column. This field is necessary to sort the Attributes across the top of the matrix. See here for more info on sorting a column by another column.
- I created a font color measure that makes the font color of the cell black if the count is less than or equal to the median total, and white if it is greater than the median total
- The cell background color for the values is just a regular gradient conditional format
You can download the .pbix here
If this is enough to get you going, please consider kudoing this reply and choosing it as the solution. Otherwise, let me know if you need additional tweaks.
- The main piece of this is unpivoting the dataset into key-value pairs (see below screenshot).
The heatmap screenshot is great. Any chance you could copy the data and headers into this formatter (left side) and then paste the JSON output here?
I copied the top ten rows of data and headings only due to the character limit here.
Thank you
Hey Anonymous. Let me know if the following will work.
Notes:
- The main piece of this is unpivoting the dataset into key-value pairs (see below screenshot).
The query in the red rectangle is the original dataset
The query in the blue rectangle is a reference query of the original and is unpivoted. This is the one that is used in the matrix.
The green rectangle contains the Attribute column (key) and the Value column
The purple rectangle contains the Attribute Sort column. This field is necessary to sort the Attributes across the top of the matrix. See here for more info on sorting a column by another column.
- I created a font color measure that makes the font color of the cell black if the count is less than or equal to the median total, and white if it is greater than the median total
- The cell background color for the values is just a regular gradient conditional format
You can download the .pbix here
If this is enough to get you going, please consider kudoing this reply and choosing it as the solution. Otherwise, let me know if you need additional tweaks.
This is great, thank you so much! This worked!
- The main piece of this is unpivoting the dataset into key-value pairs (see below screenshot).
[
{
"School": "School_J",
"Student Name ": "Student 1",
"Class_1": "1",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_1"
},
{
"School": "School_TP",
"Student Name ": "Student 10",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "1",
"Class_9": "",
"Class_10": "1",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_8, Class_10"
},
{
"School": "School_S",
"Student Name ": "Student 11",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "1",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_11"
},
{
"School": "School_S",
"Student Name ": "Student 12",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "1",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_12"
},
{
"School": "School_J",
"Student Name ": "Student 13",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "1",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_13"
},
{
"School": "School_J",
"Student Name ": "Student 14",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "1",
"Class_15": "",
"List of classes": "Class_14"
},
{
"School": "School_J",
"Student Name ": "Student 15",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "1",
"List of classes": "Class_15"
},
{
"School": "School_J",
"Student Name ": "Student 16",
"Class_1": "",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "1",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_5"
},
{
"School": "School_S",
"Student Name ": "Student 17",
"Class_1": "",
"Class_2": "",
"Class_3": "1",
"Class_4": "",
"Class_5": "",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "1",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_3, Class_10"
},
{
"School": "School_S",
"Student Name ": "Student 18",
"Class_1": "1",
"Class_2": "",
"Class_3": "",
"Class_4": "",
"Class_5": "1",
"Class_6": "",
"Class_7": "",
"Class_8": "",
"Class_9": "",
"Class_10": "",
"Class_11": "",
"Class_12": "",
"Class_13": "",
"Class_14": "",
"Class_15": "",
"List of classes": "Class_1, Class_5"
},
{
"School": ""
}
]