Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using power bi slicers to filter data on Power BI Custom Visuals using R Plotly

Hi!

I have manage to create a line chart and scatter plot rhtml visual. The line chart and scatter plots are from two different tables and they both are unrelated to other tables in my data model. I can manage to use PBI slicers as well, but it must be part of the same table that is gonna to be filtered. That works fine. It filters the location data without the need of sending in the location column to R. It just works.  But I rather want to use my data model and relations between the tables like picking location name from the location table when there exist a relation between the scatter plot table and location table. I have tried this and I am not getting any errors but it is not filtering on the selected items in my location slicer. Ok I think perhaps I need to send in location as well to get the data filtered in the R engine. I didn't think this would be a demand, or perhaps I have misunderstood a bit here.

 

I have been searching the net looking for a guidance but I have not succeeded so far. I was watching this youtube video "https://www.youtube.com/watch?v=Ax-jgwnolNI"  from BI Elite. Here they created a R scatter 3d plot with a slicer created from the WhatIf parameter section in power bi desktop. The slicer here works but it is not the same case as mine. I start wondering if R Plotly is able to filter on Power bI slicers at all.

 

When reading the R documentation there are ways of filtering the data. there is a filter function and probably other contructions in R that can solve my case. I hope some of you have done this or know how to solve my case. I send you my R script code and hope you can help me. Below you see the report and it is not responding on location Slicer (show all plots)

 

regards Geir

 

 

# input data from POWER BI Custom Visual    
    dataset = cbind(x1, y1, x2, y2, y2legends, locations)
    namesDS = names(dataset)

    pal <- c("red", "blue", "green")

    data <- data.frame(x1, y1, x2, y2, y2legends, locations)
 
    l <- list(
    font = list(
        family = "sans-serif",
        size = 12,
        color = "#000"),
    bgcolor = "#E2E2E2",
    bordercolor = "#FFFFFF",
    borderwidth = 2)

    fig <- plot_ly(data, 
           x = ~x1, 
           y = ~y1, 
           name = 'Normal kurve', 
           type = 'scatter', 
           mode = 'lines')
    fig <- fig %>% add_trace( x = ~x2, 
            y = ~y2, 
            name = ~y2legends, 
            mode = 'markers', 
            color = ~y2legends, 
            colors = pal, 
            text = ~y2legends,
            hovertemplate = paste(
                   "<b>%{text}</b>%<br><br>",
                   "%{xaxis.title.text}: %{x}<br>",
                   "%{yaxis.title.text}: %{y:.2f}<br>",
                   "<extra></extra>"
            )
        )
    fig <- fig %>% layout(legend = l)

p = ggplotly(fig);
internalSaveWidget(p, 'out.html');

 

 

 

Need Help

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Could it be a problem with the data model, would you mind sharing your .pbix file?

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      https://drive.google.com/file/d/1zu8XSnaTtVL27dag5SGr0M8wiJJ4Ws5w/view?usp=sharing 

       

      Hi again!

       

      Here I have put together a power bi desktop file. In this file you will see the use of my custom visual and also a R visual that I have experienced with. The R script shows that I am able to filter on the slicers on the scatter plot dataset, but when I try to send in two dataset into my custom visual, it works when I use fields from the scatter plot dataset only, and not when I use related fields from other tables.

       

      So it turns down to how can I send in two dataset (x1,y1, x2,y2) both with their filter selections into my Power BI custom visual. dataset 1 which includes x1,y1 and line chart slicer (a disconnected filter) and dataset 2 which includes x2,y2, project, location and perhaps other slicers.

       

      In my R code it is therefore interesting what I can do this this code

       

          dataset = cbind(x1, y1, x2, y2, y2legends,Location, Project)
          namesDS = names(dataset)
          pal <- c("red", "blue", "green")
       
          data <- data.frame(x1, y1, x2, y2, y2legends, Location, Project)
        
      # Can I adjust this code to separate so I have two datasets or frames before I send in the data variables into 
       
       fig <- plot_ly(data, x = ~x1, y = ~y1, name = 'Normal kurve', type = 'scatter', mode = 'lines')
          fig <- fig %>% add_trace( x = ~x2, y = ~y2, name = ~y2legends, mode = 'markers', color = ~y2legends, colors = pal, text = ~Location,
                  hovertemplate = paste(
              "<b>%{text}</b><br><br>",
              "Average weight: %{x}<br>",
              "%{yaxis.title.text}: %{y:.2f}<br>",
              "<extra></extra>"
              )
              )