Forum Discussion
How do I display a data grid in PowerBI
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?
Something like this:
Notice that Patient C does not data for certain days and there is one Unk (Day 2 of Patient A).
= Table.FromRows({ { "A","A1", 1, 1, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "A","A2", 1, 2, "Unk", "" }, { "A","A3", 1, 4, "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "A","A4", 1, 6, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "A","A5", 1, 12, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "A","A6", 1, 24, "Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "A","A7", 1, 48, "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "B","B1", 2, 1, "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "B","B2", 2, 2, "Pos" ,"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "B","B3", 2, 4, "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "B","B4", 2, 6, "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "B","B5", 2, 12, "Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "B","B6", 2, 24, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "B","B7", 2, 48, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "B","B8", 2, 64, "Pos", "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "C","C1", 3, 1, "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "C","C2", 3, 2, "Pos" ,"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png" }, { "C","C3", 3, 4, "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" }, { "C","C4", 3, 6, "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png" } }, { "PatientID","PatientIDNumber", "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)))
13 Replies
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" })
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.
That seems to work...
So, this is really a kludge for me (because there is a 1/0 in the data grid), I honestly would rather have a red + and a green - for Positive and Negative
I had thought of trying a scatter plot with custom markers but I cannot seem to get anywhere with that
I don;t think there is any solution at this point, may be you want to look if something available in custom visuals.
