Pass a segment value from Power BI to its data source Script R

Question

I have a report in Power BI that has Data Source = "Script R".

In the R Script, I have a connection to a database, to extract data of the format :

CustomerID    date_visit   date_order    ProductType   DeviceNumber
 1456778      2020-01-02   2020-03-01      Shoes           XX1
 1456778      2020-04-02   2020-04-11      Pants           XX2
 1456778      2020-05-12   2020-06-22      Shoes           XX2
 2345111      2020-01-22   2020-02-02      Shoes           YY2
 2345111      2020-03-03   2020-04-01      Pants           YY1

And I then I do some calculations on the dataframe, for example :

devicesPerClient <- fulldata %>% 
  group_by(CustomerID, ProductType) %>% 
  summarise(devices_count = n_distinct(DeviceNumber))

Gives the result :

CustomerID    ProductType    devices_count
1456778        Shoes            2
1456778        Pants            1
2345111        Shoes            1
2345111        Pants            1

This dataframe is then imported to Power BI and displayed like the following:

Visual Type : Segment

Field : ProductType

This allows to filter the whole report based on the product type.

Visual Type : Card

Field : Average of devices_count

The report eventually allows us to see the average of number of devices used per product type.

This is currently done on all the dataset, all dates included.

I want to evolve my Power BI report to add a filter by date range.

The date selected would then create a subset dataframe in R for date_order based on the date range selected in Power BI.

For example : From January to February 2020, how many devices were used by average for each product type ?

My problem here is for the filter by date, the calculations have to be redone in R everytime the filter changes the date range.

Is there a way to pass a segment value from Power BI to my R Code to recalculate at every value change ?

I don't know if my question is clear. I remain available. Thank you

I don't see why R needs to be involved at all. Load your database table into Power BI and then use a date table and measures to interact with it more dynamically. — Alexis Olson
– Alexis Olson, Commented Dec 17, 2021 at 19:41
Because the I'm doing operations such as : devices_count, quartile of devices count, etc... in R. So these calculations will depend on the date selected in PowerBI. @AlexisOlson — HelpASisterOut
– HelpASisterOut, Commented Dec 21, 2021 at 8:30
@HelpASisterOut - you can do all these operations in Power BI. R here gives you no value, you are overcomplicating the solution. — RADO
– RADO, Commented Dec 21, 2021 at 12:08
@RADO I am trying to lookup how to calculate quartiles in PowerBI, I didn't find an easy solution for my problem. I might need to rethink my whole project. — HelpASisterOut
– HelpASisterOut, Commented Dec 21, 2021 at 12:28

Mike Honey · Accepted Answer · 2021-12-21 10:17:08Z

There are two options for this - parameters or a dataset. I find the dataset approach more flexible, as you can easily pass multiple columns with minimal code changes.

The key change is to rebuild your query so it starts with regular Power Query steps that produce a single-row table with your date range values as columns, perhaps start date and end date.

Then you add an R Script step into that query. By default it will pass in the table from the prior step to R, as "dataset".

There's a fairly simple example of this in action here:

https://querypower.com/2017/03/11/r-execute-the-swiss-army-knife-of-power-query/

This has more complex topics, covering parameters and more:

https://www.thebiccountant.com/2017/08/25/tips-and-tricks-for-r-scripts-in-the-query-editor-in-power-bi/

Personally I would try to move the "get data from SQL" steps out of R and into Power Query - it's a great efficient tool for that and has many built-in options for date filtering that might give you a no-code solution for that requirement.

Then dataset received by R would resemble the first table in your example, and your remaining R script would just run the R-specific requirements e.g. quantiles.

smpa01 · Accepted Answer · 2021-12-27 13:42:06Z

Let's suppose in this given dataframe, you want to write a filter that returns a subset for date_visit>2020-02-15, it should return 2 rows only.

If you want to do the filtering with R but inside Power BI this is how you will do it

# 'dataset' holds the input data for this script
dataset$date_visit<-as.Date(dataset$date_visit)
dataset$date_order<-as.Date(dataset$date_order)
filter<-subset(dataset,date_visit>"2020-02-15")
filter

The full M is here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY8xCoAwEAT/ktpAbnNRnyHYJIQUFoKVFvp/PAhJFMTqmGKY2xgVseuHYVSdgoHRhrRBBSssMG/Hesr1nlTq/h0SYIFp2a/s4NNxmlqn18Crkx1YdkT06KA5yNHihPDtyARbgfOe8lsIsifd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, date_visit = _t, date_order = _t, ProductType = _t, DeviceNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"date_visit", type text}, {"date_order", type text}, {"ProductType", type text}, {"DeviceNumber", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)src<-data.frame(dataset)#(lf)dataset$date_visit<-as.Date(dataset$date_visit)#(lf)dataset$date_order<-as.Date(dataset$date_order)#(lf)filter<-subset(dataset,date_visit>""2020-02-15"")#(lf)filter",[dataset=#"Changed Type"]),
    filter = #"Run R script"{[Name="filter"]}[Value]
in
    filter

If you want to pass on a parameterized value (generated through some dynamic M query / Parameter created by you / hardcoded value) for date_visit you need to pass on like following

let
   val ="2020-02-15", /*this is the parameterized value either dynamically generated or harcoded to be passed on to R */
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY8xCoAwEAT/ktpAbnNRnyHYJIQUFoKVFvp/PAhJFMTqmGKY2xgVseuHYVSdgoHRhrRBBSssMG/Hesr1nlTq/h0SYIFp2a/s4NNxmlqn18Crkx1YdkT06KA5yNHihPDtyARbgfOe8lsIsifd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, date_visit = _t, date_order = _t, ProductType = _t, DeviceNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"date_visit", type text}, {"date_order", type text}, {"ProductType", type text}, {"DeviceNumber", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)src<-data.frame(dataset)#(lf)src$date_visit<-as.Date(src$date_visit)#(lf)src$date_order<-as.Date(src$date_order)#(lf)filter<-subset(src,date_visit>"""&val&""")#(lf)filter",[dataset=#"Changed Type"]),
    filter = #"Run R script"{[Name="filter"]}[Value]
in
    filter

It generates following

If you are dynamically filtering dataframes like this in PBI, one aspect to keep in mind that, dataType Date are extremely tricky between R and and PBI. It must be converted with as.Date first before any filtering can be applied on the R side.

But if you were filtering on ProductType="Shoes", you can could have simply done this

let
   val ="Shoes",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY8xCoAwEAT/ktpAbnNRnyHYJIQUFoKVFvp/PAhJFMTqmGKY2xgVseuHYVSdgoHRhrRBBSssMG/Hesr1nlTq/h0SYIFp2a/s4NNxmlqn18Crkx1YdkT06KA5yNHihPDtyARbgfOe8lsIsifd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, date_visit = _t, date_order = _t, ProductType = _t, DeviceNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"date_visit", type text}, {"date_order", type text}, {"ProductType", type text}, {"DeviceNumber", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)src<-dataset#(lf)filter<-subset(src, ProductType=="""&val&""")",[dataset=#"Changed Type"]),
    filter = #"Run R script"{[Name="filter"]}[Value]
in
    filter

Collectives™ on Stack Overflow

Pass a segment value from Power BI to its data source Script R

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related