Need help with creating a Power BI Custom Visual HTML with Plotly

[Anonymous]

Hi!

I have struggled some time now and I can't get my custom visual to work properly. What I want to do is to combine two dataset into one visual where the first dataset should be a line chart and the other should be a scatter plot chart. Both dataset has common column names and data types (avgweight, value and attribute) but they are unrelated to each other. I should be able to select different line chart from "select Type" filter, while I should be able to select types of plots from the Attribute filter (pigment, fat...). And I should also be able to select Location on scatter plots. But the two last filters should not affect the line chart.

Below you see all components that will be in the report, but my Power BI custom visual should show the line chart and scatter plots in the same visual. And here I though it should be easy. Continue below the picture

I started creating a custom visual from a template like this https://github.com/Microsoft/PowerBI-visuals/blob/master/RVisualTutorial/CreateRHTML.md. Then I went to plotly and found a code that I could spin on

library(plotly)
trace_0 <- rnorm(100, mean = 5)
trace_1 <- rnorm(100, mean = 0)
trace_2 <- rnorm(100, mean = -5)
x <- c(1:100)

data <- data.frame(x, trace_0, trace_1, trace_2)

p <- plot_ly(data, x = ~x) %>%
  add_trace(y = ~trace_0, name = 'trace 0',mode = 'lines') %>%
  add_trace(y = ~trace_1, name = 'trace 1', mode = 'lines+markers') %>%
  add_trace(y = ~trace_2, name = 'trace 2', mode = 'markers')

p

All things should be happening in the script.R file. Below I have tried many things. I am able to create static charts that works but I want to put in data from the report so it responds on the two datasets. right now I am stuck and unable to move forward

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################

# y <- c(Values$Value)
# x <- c(Values$AvgWeight)

# #x <- c(1:100)
# # x2 <- c(20, 40, 60, 80, 100)
# # y2 <- c(4,  2,  3,  5,  7)

# data <- data.frame(x, y)
# g=ggplot(Values, aes(x=Values$AvgWeight, y=Values$Value,colour = "Red")) + geom_jitter(size=4)

df<-data.frame(x=Values$AvgWeight,y= Values$Value, x2=Values$AvgWeight2,y2= Values$Value2)
g = plot_ly(df, x = ~x ) %>%
    add_trace(y = ~y, name = 'trace 0',mode = 'lines', type = "scatter") %>%
    add_trace(x = ~x2, y = ~y2, name = 'trace 1', mode = 'lines+markers',  type = "scatter") %>%

####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');
####################################################

 Hope any of you can help. I will very happy

Regards Geir

[stevedep]

You can take a look at this blog post. It describes how to add an interactive javascript Plotly Chart in Power BI. It's quite easy. 

Kind regards,

Steve.

[Anonymous]

You first call the ggplot, then later the plotly plot. This is why you are seeing seperate graphs. If you need tooltips youll have to use Plotly, if not then ggplot will suffice. 

g=ggplot(Values, aes(x=Values$AvgWeight, y=Values$Value,colour = "Red")) + geom_jitter(size=4)+geom_point(data=Othervaluestable,aes(x=Othervaluestable$AvgWeight, y=Values$Value))

Something along those lines. Basically with ggplot (and plotly) you can add additional visuals like so. Let me know if this is what you were thinking. If this solution helped, I would appreciate kudos and if it gives you the solution I would appreciate if you "accepted as solution".  

[Anonymous]

Hi and thanks for fast response. Perhaps I was not clear enough. Below you see what I am looking for 

Here you see a hardcoded version that works. I need to replace this code so it works with fields from Power BI when it uses the custom visual. It may be a challenge when I am using fields from two different tables, but perhaps not. I am sure there are solution for that. Hope you understand it better now

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################

# y <- c(Values$Value)
# x <- c(Values$AvgWeight)


trace_0 <- rnorm(100, mean = 5)

x <- c(1:100)
x2 <- c(1, 10, 20, 40, 60)
y2 <- c(4,  2,  3,  5,  7)


data <- data.frame(x, x2, trace_0,  y2)

g <- plot_ly(data, x = ~x) %>%
  add_trace(y = ~trace_0, name = 'trace 0',mode = 'lines', type = "scatter") %>%
  #add_trace(y = ~trace_1, name = 'trace 1', mode = 'lines+markers') %>%
  add_trace(x= ~x2, y = ~y2, name = 'trace 2', mode = 'markers', type = "scatter")



####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');
####################################################

[Anonymous · Accepted solution]

Have solved it

[Marco57]

Hello @Anonymous,

Any chances you would share how you did?

Thanks in advance.

[Anonymous]

@Anonymous 

This is not possible given the current functionality of the visual. It must be hardcoded. 

[Anonymous]

I should add that there is a custom visual for the javascript library d3. It might allow you to do this but it only supports 20,000 data points.