Regresion lineal multiple R studio Power Bi
Hola, ejecuté un script de r para calcular una regresión lineal multiple. Pude obtener el intercepto y slope y traspasarlos a una tabla utilizando library broom tidy(model). library(broom) model <- lm(Energía_electrica~ Grados + Transacciones,dataset) model <- tidy(model) esta es la base de datos Mes Energía_electrica Grados Transacciones Tienda ene-22 93.471 337 58.567 CD feb-22 82.053 319 51.095 CD mar-22 79.777 295 55.652 CD abr-22 67.907 204 49.633 CD may-22 71.634 185 49.520 CD jun-22 68.864 164 49.280 CD jul-22 69.559 159 50.992 CD ago-22 67.204 161 49.913 CD sept-22 63.007 172 43.913 CD oct-22 65.902 204 48.704 CD nov-22 66.469 256 46.081 CD dic-22 73.164 326 50.385 CD ene-22 132.321 354 66.405 hc feb-22 108.787 313 61.665 hc mar-22 114.572 304 65.195 hc abr-22 94.501 207 56.435 hc may-22 98.740 170 55.612 hc jun-22 87.222 151 52.630 hc jul-22 81.592 123 54.133 hc ago-22 74.998 142 50.118 hc sept-22 67.039 188 43.383 hc oct-22 77.155 212 49.618 hc nov-22 108.387 260 48.931 hc yo necesito calcular la regresión para cada tienda y que la tabla con slope e intercepto me haga una distinción de cual es para cada tienda o que con los filtros los números se actualicen (debo tener slope e intercepto en una tabla porque luego necesito ocupar esos numeros para formulas). Se puede hacer algo como un ciclo for o alguna distinción en esta nueva tabla para filtrar por tienda?
Creating a Custom Gauge Visual
I'm trying to create a custom visual that shows the distribution of data through a visualisation similar to a boxplot. I then want to have a point on that plot that moves along the boxplot depending on the value of a slicer. But the problem is my slicer moves the boxplot and the scale aswell. I created my visual with R and I've included the code below: Dar <- ggplot(data = dataset, aes(x = 0, y = Data)) + scale_color_viridis_d( option = "mako", name = "Level:", direction = -1, begin = .15, end = .9, labels = function(x) paste0(as.numeric(x)*100, "%") ) + ggdist::stat_interval() + coord_flip() + stat_summary(geom = "point", fun = "median", color = "red", size = 6, pch = 17, aes(x = -0.05, y = Data) ) Dar And this is what my visualisation looks like: Any help would be greatly appreciated!
Personal Gateway for R script
Hi there, I have used an R script in my BI Dashboard, published it and set an auto refresh with a personal gateway. The personal gateway is on and ready to use, the auto refresh works with no error but the column that refrences the R code does not show the correct data. The column is meant to show Trues and falses but it only shows False. When I refresh in desktop it works perfectly and the column displays the correct info. I think I am missing something in the personal gateway or in the actual R code but I have read everything I can find on this and just can't seem to get it right. I am new to using R in BI so the whole process has been trial and error but it is just odd that it works in desktop but not service. The R code is used as a step in one of my tables in query editor, this is how it starts off: new_dataset <- dataset new_data <- data.frame(new_dataset) HS <- new_data$"US_HTS_Code__c" COO <- new_data$"Country_of_Origin2__c" new_data$ADDFlag1 <- Any guidance will be greatly appreciated, thanks so much!Scheduled refresh of SQL source but with R script transformstions
Hello. I am exploring using PBIRS as the server to host Power BI reports, which will extract/source data from a SQL Server Database, and which will subsequently use R scripts to transform the data in various ways. Per the link below, as far as I understand R scripts themselves are not a source which can have PBIRS scheduled refreshes applied to them. As such, I'm wondering the following: 1.) Is there a way to work around this so that I can in fact schedule refreshes of a single R script which will hold the code for the entire ETL, 2.) If SQL Server Database is the source and transformations are applied using R scripts, does scheduled refresh work in this scenario? https://docs.microsoft.com/en-us/power-bi/report-server/data-sources#list-of-supported-authentication-methods-for-model-refresh
R Script in direct query
I am using R Script in Direct Query to retrieve data from SQL server using SP by passing logged-in user id. In Power Query Editor window, the script runs fine and retrieves data. But on applying changes to the report, it is throwing below error: Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'. Sample code: let RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"), output = Record.FieldValues(RScript{[Name="output"]}[Value]{0}){0}, Source = Sql.Database("SQLServerName", "DBName", [Query="EXEC SP_Name '"&output&"'"]) in SourceR-Script Visual Runtime Error in Power BI Server
Hi Everyone, I wrote a R-Script that works/runs perfectly in Power BI Desktop but doesn't work when published to the Power BI Service. I'm not able to pinpoint the error, is it the pipe operator (%<%)? Any help would be highly apperciated, I've been struggling with this issue for some time now. Script Runtime Error: Evaulation Error: Level sets of factors are different R-Script: # 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(ProjectName, Commodity, WBS_code, Months, Running_Total_V2, Total_Scope_V2, RTPercentCompleteV2, Project_Description) # dataset <- unique(dataset) # Paste or type your script code here: library(dplyr) library(ggplot2) library(RColorBrewer) proposal_project <- unique(dataset[["Project_Description"]]) # there are duplicates due to WBS df <- dataset %>% select(ProjectName, Commodity, Months, RTPercentCompleteV2) %>% distinct() # for projects with multiple entries for the same percent complete, keep the highest value df <- df %>% group_by(ProjectName, Commodity, RTPercentCompleteV2) %>% slice(which.max(Months)) %>% arrange(Months) # group by and interpolate pctcomplete <- seq(0.1, 1, by=0.05) df <- df %>% group_by(ProjectName, Commodity) %>% group_modify(~ {approx(.x$RTPercentCompleteV2, .x$Months, xout=pctcomplete) %>% data.frame()}) %>% ungroup() prop_df <- df %>% filter(ProjectName == proposal_project) # split out proposal data after transformations df2df <- df %>% filter(ProjectName != proposal_project) data_wo_proposal <- df2df %>% group_by(Commodity,x) %>% summarise_at(vars(y),list(mean_duration = mean)) # plot mindiff <- min(c(0)) maxdiff <- max(c(max(data_wo_proposal$mean_duration, na.rm=TRUE), max(prop_df$y, na.rm=TRUE))) ticks <- round(seq(mindiff, maxdiff, by=2)) tick_y <- seq(0,100, by=5) g <- ggplot(data=data_wo_proposal, aes(x=mean_duration, y=x, colour=Commodity)) + geom_line(linetype=2) + geom_point() final_plot <- g + geom_line(data = prop_df, aes(x=y,y=x,colour=Commodity)) + geom_point(data=prop_df,aes(x=y,y=x,colour=Commodity)) final_plot_label <- final_plot + labs(title='Average Duration of Reference Projects vs Proposal Project', subtitle=paste('Proposal Project:',prop_df$ProjectName[1], '(solid line in figure below)'), x= 'Duration (Months)',y='Percent Complete') + theme_bw() + theme(legend.position='bottom', legend.title=element_blank()) + scale_colour_brewer(palette='Set1') + scale_x_continuous(breaks=ticks)+scale_y_continuous(labels = scales::percent_format(accuracy = 1),breaks=seq(0,1, by=.05)) final_plot_label Thank you, Val
R Custom Power BI Visualization Variable Input Handling
I am trying to create a custom viz (scatter plot) with the ability to provide different options for colour and shape of the points using ggplot2. My visualisation works fine when I specify both the Shape and Colour parameter, however, I want to be able to only supply one or the other down the road, not always both. This is what my parameter options look like: While my R script.r file for ingesting data is: This is the error I get when I don't include Colour or Shape or both. I feel that the ability to handle not supplying an argument should be handled within the script which I have tried to do. However, I am now stuck.Routine Update for R & Supported R Packages in the Power BI Service
Request support for a new R package If you would like to request support of an R package not found in that list, please submit your request. The current process for updating R & R packages is not working. Many of the supported packages in the Power BI Service are out of date. It appears that this is a issue for many users, and would probably be widely supported, but the 'support' metric that MS is using is dilluted across the many packages in use. Regular updates to these supported packages should not require community support for MS to do it.
