cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DBirdmanAR Frequent Visitor
Frequent 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

Accepted Solutions
DBirdmanAR Frequent Visitor
Frequent Visitor

Re: How do I display a data grid in PowerBI

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

Steve_Wheeler Established Member
Established Member

Re: How do I display a data grid in PowerBI

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
Super User IV
Super User IV

Re: How do I display a data grid in PowerBI

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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





DBirdmanAR Frequent Visitor
Frequent Visitor

Re: How do I display a data grid in PowerBI

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

Super User IV
Super User IV

Re: How do I display a data grid in PowerBI

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/






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





DBirdmanAR Frequent Visitor
Frequent Visitor

Re: How do I display a data grid in PowerBI

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.

Super User IV
Super User IV

Re: How do I display a data grid in PowerBI

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Vvelarde Super Contributor
Super Contributor

Re: How do I display a data grid in PowerBI

@DBirdmanAR

 

Hi, please follow the steps in this video.

 

 

Let me know if helps you.

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User IV
Super User IV

Re: How do I display a data grid in PowerBI

Very well done @Vvelarde and well explained. I tried but I think you provided the exact solution. Thank you!






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
DBirdmanAR Frequent Visitor
Frequent Visitor

Re: How do I display a data grid in PowerBI

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"
})
DBirdmanAR Frequent Visitor
Frequent Visitor

Re: How do I display a data grid in PowerBI

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors