08-14-2016 15:27 PM - last edited 04-08-2018 01:51 AM
Description
Prerequisites (The sample .pbix files will not work without these prerequites completed)
1. Install R Engine
Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately installR on your local computer. You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository.
2. Install the required R packages.
Download the R script attached to this message and run it to install all required packages on your local machine.
Required R packages:
arules, arulesViz, grDevices, gridExtra, grid, methods
Tested on:
CRAN 3.3.1, MRO 3.3.0, , powerbi.com
Legal Disclaimers:
Terms of Service and Third Party Programs.
Great job on this visual! But I can't figure out how you created your dataset? I'm looking at the sample PBIX file and it looks like the selected fields are total counts. So in my PBIX file I counted the total values by creating a measure:
MPPL2 = CALCULATE(COUNT(IRC_Metric_RSA_Refresh[PPL2]), FILTER(IRC_Metric_RSA_Refresh, [PPL2]="AUTO"))
But when I add this measure to the visual in my own PBIX file, it doesn't allow Basic filtering and it doesn't display in my PBIX. How can I mimic your behavior in my PBIX file?
Also this is the error I'm getting:
Hi @ironryan77,
I am really sorry for late reply 😞
Apparently some changes/ updates in "arules" package caused the sub-function "cleanRedundant" to work diffrently without backward compatability. We will need to solve it both for R visual and R custom visual. I will update and ping you after update.
@boefraty I know this is a very old thread but I am getting the same error with this visual. Is there a way to fix it?
Hi @Anonymous and @Anonymous and @surething and @ironryan77 and @HowardCC
I am really sorry for such a a late reply. The error in R-script is inside cleanRedundant function
The is.subset functionality changed with latest releases of arules package.
Quick and dirty fix is to comment out some lines in the function (attached solution)
Which means you still may get some redundant rules in output, will investigate in the future.
The problem is with "ISLR" package it is required by R connector (not R visual), so you have two workarounds:
1) Don't do refresh
2) call command
install.packages("ISLR")
from any R console
Hello, I downloaded the association rules custom viz and succesfully have it working with the data. I was wondering though if there was any way we could sort the rules based on the RHS? I tried adding a filter, but what that did was exclude the data from ever getting to the association rules algorithm. Is there a way to sort the association rules without excluding data?
Side branch solution for this feature:
https://github.com/boefraty74/PowerBI-visuals-assorules-1/tree/assorulesWithTextFilters
I added two text parameters, comma separated strings to be searched inside RHS string rule (using OR operator)
Please tell us how you draw this table.
Which table? There is a parameter 'visualisationMethod' in the script
Hi,
@boefraty I am trying to make it to works with my transactions data but it does not gives me any visualization for it.
Any idea how to make it works?
Mohammad PATEL
Hi @Anonymous,
Could you please send me your data sample or even PBIX with data included?
boefraty @ microsoft . com
Do you want to sort by second column?
Hi
I wonder if you can advice that what Im trying to acheive is possible.
I wanted to transform my dataset to transactions using a simple R script below
library(arules)
dd = as(dataset, "transactions")
df = as.data.frame(as(dd, "matrix"))
However I got an error message beolow. Can you please help what this error actually mean and how to fix it?
DataSource.Error: ADO.NET: R script error.
Loading required package: Matrix
Attaching package: 'arules'
The following objects are masked from 'package:base':
abbreviate, write
Error: could not find function "as"
Execution halted
Details:
DataSourceKind=R
DataSourcePath=R
Message=R script error.
Loading required package: Matrix
Attaching package: 'arules'
The following objects are masked from 'package:base':
abbreviate, write
Error: could not find function "as"
Execution halted
ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException
Answered by own question!
library(arules)
library(methods)
data(dataset)
dd1=as(dataset,"transactions")
trans=dd1
AdultData=as.data.frame(as(dd1, "matrix"))
AdultData=cbind(id=1:nrow(AdultData),AdultData)
replace data(Adult) with data(dataset) and R does the rest!.. didnt have eyes for details there.
Hi,
I have a table with 2 Columns ID and Product.
I want to Edit the Script in R, but I can't get it to work
How should it be the read.transactions statement?
> txn <- read.transactions(dataset, rm.duplicates= TRUE,format='basket',sep='','',cols =1)
But I get this error...
Error in readLines(file, encoding = encoding) : 'con' is not a connection
The data with 2 columns: "TransactionID" and "Product" is indeed very natural. Attached is the PBIX with R code suited to such input.
The relevant data transformation is in lines
nnn = names(dataset)
transData <- as(split(dataset[,nnn[2]], dataset[,nnn[1]]), "transactions")
The data in PBIX is synthetic.