Hi All,
I want to display a simple data grid in PowerBI and format the cells red for TestResults=Pos and green for TestResults=Neg. Ultimately the data would look something like this (from MS Excel):
My data looks like this (just create a Blank Query and paste this in)
= Table.FromRows({{"A", 1, "Pos"} , {"A", 2, "Pos"},{"A", 4, "Neg"} , {"A", 6, "Pos"},{"A", 12, "Pos"} , {"A", 24, "Pos"},{"A", 48, "Neg"} , {"A", 64, "Pos"},{"B", 1, "Neg"} , {"B", 2, "Pos"},{"B", 4, "Neg"} , {"B", 6, "Neg"},{"B", 12, "Pos"} , {"B", 24, "Pos"},{"B", 48, "Pos"} , {"B", 64, "Pos"}}, {"PatientID ", "StudyDay", "TestResults"})The Table visualization appears to me to sum/count my data in columns based on items in the Values.
I can get a partial solution if I create a "helper" column (TestNum ) that is 1 for Pos and 0 for Neg, then I use the Matrix visualization with the rows=PatientID, columns=StudyDay, and Values=TestNum (aggregation =sum) but I cannot conditionally format the cells to be Red/Green
= Table.FromRows({{"A", 1, "Pos",1} , {"A", 2, "Pos",1},{"A", 4, "Neg",0} , {"A", 6, "Pos",1},{"A", 12, "Pos",1} , {"A", 24, "Pos",1},{"A", 48, "Neg",0} , {"A", 64, "Pos",1},{"B", 1, "Neg",0} , {"B", 2, "Pos",1},{"B", 4, "Neg",0} , {"B", 6, "Neg",0},{"B", 12, "Pos",1} , {"B", 24, "Pos",1},{"B", 48, "Pos",1} , {"B", 64, "Pos",1}}, {"PatientID ", "StudyDay", "TestResults","TestNum"})I am pretty flexible in how the visualization ultimatly ends up but how would I accomplish this?
Though it has Known Limitations (see https://powerbi.microsoft.com/en-us/documentation/powerbi-service-r-visuals/#known-limitations), an R visual can do it. See below for sample R script (12 lines without comments and blanks) and output.
library(ggplot2) # Needed for ggplot
library(ggthemes) # has a clean theme for ggplot2
library(tidyr) # provide 'complete' function for empty heatmap cells
#Convert the (integer) StudyDay to a factor to avoid gaps in the X axis
dataset$StudyDay=with(dataset,factor(StudyDay))
#Fill missing data values, to avoid blank cells in the heatmap
dataset <- dataset %>% complete(PatientID, StudyDay)
# Avoid having to reference dataset$ each time in function calls
attach(dataset)
#Plot the dataset with TestResults to set the fill colour
ggplot(dataset, aes(x=StudyDay, y=PatientID, fill=TestResults)) +
#geom_tile to present as grid per https://www.rstudio.com/wp-content/uploads/2015/12/ggplot2-cheatsheet-2.0.pdf
geom_tile(color="black", size=0.1) +
#Set the fill colours for TestResults
scale_fill_manual(values = c("Pos" = "green", "Neg" = "red", "Unk" = "blue")) +
#Set square tiles
coord_equal() +
#Apply tidy Tufte theme with no axis ticks
theme_tufte(base_family="arial", ticks=FALSE) +
#Order by reverse PatientID
scale_y_discrete(limits = rev(levels(PatientID))) 
Very well done @Vvelarde and well explained. I tried but I think you provided the exact solution. Thank you!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi All,
I'll try @Vvelarde's solution but in the meantime I was able to use the Enhanced Scatterplot (https://powerbi.microsoft.com/en-us/documentation/powerbi-service-tutorial-enhancedscatter/) and was able to get something close to what I wanted.
I'd like the icons bigger
Visualization:
Data is:
= Table.FromRows({
{
"A1",
1,
1,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"A2",
1,
2,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"A3", 1,
4,
"Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
},
{
"A4",
1,
6,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"A5",
1,
12,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"A6",
1,
24,
"Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"A7",
1,
48,
"Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
},
{
"A8",
1,
64,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"B1",
2,
1,
"Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
},
{
"B2",
2,
2,
"Pos" ,"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"B3",
2,
4,
"Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
},
{
"B4",
2,
6,
"Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
},
{
"B5",
2,
12,
"Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"B6",
2,
24,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"B7",
2,
48,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
},
{
"B8",
2,
64,
"Pos",
"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
}
}, {
"PatientID ",
"YHelper",
"StudyDay",
"TestResults",
"Image"
})
Though it has Known Limitations (see https://powerbi.microsoft.com/en-us/documentation/powerbi-service-r-visuals/#known-limitations), an R visual can do it. See below for sample R script (12 lines without comments and blanks) and output.
library(ggplot2) # Needed for ggplot
library(ggthemes) # has a clean theme for ggplot2
library(tidyr) # provide 'complete' function for empty heatmap cells
#Convert the (integer) StudyDay to a factor to avoid gaps in the X axis
dataset$StudyDay=with(dataset,factor(StudyDay))
#Fill missing data values, to avoid blank cells in the heatmap
dataset <- dataset %>% complete(PatientID, StudyDay)
# Avoid having to reference dataset$ each time in function calls
attach(dataset)
#Plot the dataset with TestResults to set the fill colour
ggplot(dataset, aes(x=StudyDay, y=PatientID, fill=TestResults)) +
#geom_tile to present as grid per https://www.rstudio.com/wp-content/uploads/2015/12/ggplot2-cheatsheet-2.0.pdf
geom_tile(color="black", size=0.1) +
#Set the fill colours for TestResults
scale_fill_manual(values = c("Pos" = "green", "Neg" = "red", "Unk" = "blue")) +
#Set square tiles
coord_equal() +
#Apply tidy Tufte theme with no axis ticks
theme_tufte(base_family="arial", ticks=FALSE) +
#Order by reverse PatientID
scale_y_discrete(limits = rev(levels(PatientID))) 
On you aggregation field, change it to MAX instead of SUM, click drop down arrow, and in conditional formatting, do somethign like this:
Hope it will work.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I don;t think there is any solution at this point, may be you want to look if something available in custom visuals.
https://app.powerbi.com/visuals/
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Good luck mate!
I shared with you what I know. If you find a solution, please share so that other can get benefit from it.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
