Forum Discussion
Dynamically place text in R Script visual
Because Power BI is designed as a tool to be used interactively, it has many limitations on static data labels. To get around this issue, I'm using ggplot in an R Script visual to create a bubble chart where I can label bubble values with multiple data dimensions.
The R Script visual passes fields as an R dataframe named dataset. So far, so good. I want to place annotation text on the right side of the plot. To do this, I need to know the extent of the x and y axes and use that information to set the x and y values in the geom_text() or annotate() functions. To do that, I need to know the minimum and maximum values of the x and y data points.
Assuming my fields are named x_data and y_data, I have included the following in my R script:
xmin <- min(dataset$x_data)
xmax <- max(dataset$x_data)
ymin <- min(dataset$y_data)
ymax <- max(dataset$y_data)
I then use the variables in to position my text:
g <- dataset %>%
ggplot(aes(x = x_data, y = y_data)) +
geom_point(aes(size = size_data)) +
annotate(geom='label', label='Test text', size = 4, x=xmax, y=ymax, hjust="inward", vjust="inward")
g
The code runs without error, but the text label does not appear. If I hard code the values of xmax and ymax in the annotate() command, it does. This makes it appear that the variable values are not visible to the rest of the script, yet there is no error.
Does anyone know if calculated R variables can work the way I'm intending? And if they can't, is there any other way to accomplish what I'm trying to do?
After a bit of additional analysis, I found the issue. On my Power BI page, I use a number parameter slider which feeds a topN function to limit the number of data points plotted. Since I can't pass the parameter directly as a scalar constant, I set the size of the unwanted points to null. This causes the min() and max() functions to return NA.
My solution was to filter the dataset data frame before calculating min and max for my axes.Here's the code, FWIW:
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:# dataset <- data.frame(Vendor, Market Share, Growth, Revenue YoY, Top Vendor Revenue)# dataset <- unique(dataset)# Paste or type your script code here:# Load required librarieslibrary(tidyverse)library(scales)library(ggrepel)# Filter and format datasetdataset <- dataset %>%filter(!grepl('^Other ', .$Vendor )) %>% # Remove 'Other Vendors' from outputfilter(.$`Top Vendor Revenue` > 1) # Workaround due to fact PBI does not allow variables to be passed to R# Compute axis limites from datasetxmin <- 0xmax <- max(max(dataset$`Market Share`), 0.25)ymin <- min(dataset$`Growth`)ymax <- max(dataset$`Growth`)
3 Replies
Anonymous , Make sure your calculation are done within canvas(Visual), you are not doing page on power bi page
amitchandak I don't know what your reply means. I am using Power BI Desktop. I am using the built-in R script visualization. All my code is entered in the R script editor that is part of the R script visualization. The ggplot bubble chart I have created works and displays properly. The issue is that I am trying to use a calculated variable to place a text annotation on the chart canvas, but it doesn't appear to be recognized. If I run the same code in RStudio, it does work.
Does that help?
After a bit of additional analysis, I found the issue. On my Power BI page, I use a number parameter slider which feeds a topN function to limit the number of data points plotted. Since I can't pass the parameter directly as a scalar constant, I set the size of the unwanted points to null. This causes the min() and max() functions to return NA.
My solution was to filter the dataset data frame before calculating min and max for my axes.Here's the code, FWIW:
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:# dataset <- data.frame(Vendor, Market Share, Growth, Revenue YoY, Top Vendor Revenue)# dataset <- unique(dataset)# Paste or type your script code here:# Load required librarieslibrary(tidyverse)library(scales)library(ggrepel)# Filter and format datasetdataset <- dataset %>%filter(!grepl('^Other ', .$Vendor )) %>% # Remove 'Other Vendors' from outputfilter(.$`Top Vendor Revenue` > 1) # Workaround due to fact PBI does not allow variables to be passed to R# Compute axis limites from datasetxmin <- 0xmax <- max(max(dataset$`Market Share`), 0.25)ymin <- min(dataset$`Growth`)ymax <- max(dataset$`Growth`)
