Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
DBirdmanAR
Regular Visitor

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):

 

  TestGrid.png

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?

2 ACCEPTED SOLUTIONS

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"
})

View solution in original post

Anonymous
Not applicable

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))) 

DataGridInR.PNG

View solution in original post

13 REPLIES 13
Vvelarde
Community Champion
Community Champion

@DBirdmanAR

 

Hi, please follow the steps in this video.

 

 

Let me know if helps you.

 




Lima - Peru

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:

FinalViz.png

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"
})

Hi All,

 

@Vvelarde's solution works for a binary system (1's and blanks).

 

My data is constantly being added so some of the PatientIDs/StudyDay do not have data, others have Positive, Negative, and Unknown.  When I use @Vvelarde's method, you cannot hide the values of the text by changing the color because the text can only have one color.

 

I am not sure where to go at this point...

@DBirdmanAR

 

Yeah, is worked when have a Binary Scenario (accord to your original question and Data.)

 

Please post sample data with all the possible escenarios and will try to find a solution.

 

Regards

 

 




Lima - Peru

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"
})
Anonymous
Not applicable

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))) 

DataGridInR.PNG

Please share bi file for understnd how you have done it

 

AB
parry2k
Super User
Super User

On you aggregation field, change it to MAX instead of SUM, click drop down arrow, and in conditional formatting, do somethign like this:

 

format1.PNG

 

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.

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.

 

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.

Nothing there (I had checked previously).  I suppose I could try to do it with R but that seems like a lot of extra work.

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.