Sharon's avatar
Sharon
Microsoft Employee
10 years ago

Association Rules

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.

 

Advanced Analytics Visualizations

24 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        patoduck
        Helper III
        9 years ago

        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

         

         

  • baferrie's avatar
    baferrie
    New Member
    9 years ago

    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?

  • karaoan's avatar
    karaoan
    Kudo Kingpin
    9 years ago

    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's avatar
      boefraty
      Microsoft Employee
      9 years ago

      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

  • ironryan77's avatar
    ironryan77
    Kudo Commander
    9 years ago

    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?