Association Rules 08-14-2016 15:27 PM - last edited 04-08-2018 01:51 AM

[Sharon]

Description

Association rules are ideal to quicly derive insights from large datasets.  The rules are autmatically detected and visualized. 

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.

[ironryan77]

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?

[ironryan77]

Also this is the error I'm getting:

[boefraty]

Hi ironryan77, 

Please send me a PBIX, I will see what can be done 

[ironryan77]

Thanks!

[boefraty]

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. 

[Anonymous]

@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?

[boefraty]

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. 

 

[karaoan]

Hi, I have installed r (default version and one that I used is 3.3.2 though) as per provided links and ran the script. When refreshing the PBIX I get the following error:

[boefraty]

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

[baferrie]

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?

[boefraty]

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)

[MAAbdullah47]

Please tell us how you draw this table.

[boefraty]

Which table? There is a parameter 'visualisationMethod' in the script 

[Anonymous]

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

[boefraty]

Hi @Anonymous, 

Could you please send me your data sample or even PBIX with data included? 

boefraty @ microsoft . com 

[boefraty]

Do you want to sort by second column? 

[Anonymous]

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

[Anonymous]

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.

[patoduck]

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

[boefraty]

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.