Power BI – Create Custom Interactive Visuals with R

Prerequisites

Install node.js: Download and Install the latest version of node.js from  the node.js website

Install R : Download and install the latest version of R from here.
Once R is installed I also recommend you to install R Studio the integrated development environment for R. 

Enable R scripting in Power BI: In Power BI desktop, go to File > Options and settings > Options>R Scripting: Then set up your R home directories and R IDEs.

Set up Node js

Open a CMD command and type:

npm install -g powerbi-visuals-tool

To check if the Powerbi-visuals-tools has been installed successfully installed type:

pbiviz 

Let’s build our first R Custom Visual with HTML output

Now using the windows “cd” command navigate to the specific folder where you want to store your custom PBI visuals and type:

pbiviz new sampleRHTMLVisual -t rhtml

You should now see a similar folder named sampleRHTMLVisual created in your specified folder

If you open the file Script.r you’ll see the below R script generated.
You can find this template from the Microsoft github PowerBI-visuals

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
g = qplot(`Petal.Length`, data = iris, fill = `Species`, main = Sys.time());
####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');
####################################################

You can change this script later to create your own visuals. I shall write soon another article about how to create nicer and more advanced visuals in R.

Generate the PowerBI visual file:

Now we need to create the pbi file, still in the CMD command using “cd” navigate to the folder where the script.r is stored and type:

pbiviz package

Once the command has been executed you should see the PBI file in the “dist” folder:

Import the custom visual into PowerBI

Now in PowerBI in the visualizations pane we need to click on the three dots > Import from file and select the PBI file located in the “dist” folder.
Then even though the R script references a sample dataset and not the Power BI data we need to add a measure to the Values area to trigger the visual to appear.

Note that the interactions with this visual are limited as this is an HTML
visual. The main limitation is that you cannot interact with the other visuals present in your PowerBi page.
However we still get few interactions such as:

  • tooltip
  • zoom in/out
  • auto scale
  • display/hide categories
  • compare data on hover…

If you are already proficient in R you can easily create other visuals you just need to edit the script.r file, run the pbiviz package command again and import the generated PBI file.

I’ll be shortly writing another post on how to create more advanced visuals and how to use data directly from PowerBI instead of a preloaded sample dataset.

Also, if you’re fluent in JavaScript and Json you can create even more advanced custom visuals that can fully interact with your other visuals just like the built-in powerbi visuals.

DAX – TREATAS function – Book-to-Bill Ratio use case

Treatas is a powerful DAX function that applies the result of a table expression as filters to columns from an unrelated table.

In other words, treatas applies the filter context of a specific table to another unrelated table (no relationship in the model).

Book to Bill ratio

A book-to-bill ratio is the ratio of orders received vs orders shipped and billed for a specific period, a month in our case.

The book-to-bill ratio reveals how quickly a business fulfils the demand for its products.

How do we do this in DAX?

There’s couple of ways to achieve it:

  • Bidirectional filter + changing the filter context: bad practice will literally kill the performance of your model
  • Intersect + Lookup: was ok before the treatas function but turns to be much slower than treatas
  • Role playing dimension + userelationship function: by far the fastest but you ned to use role playing dimensions
  • Treatas: if like me you cannot use role playing dimensions for whatever reasons this is the best option

OK here is my model:

  • Fact Order
  • Order date dimension
  • Ship date dimension

There isn’t any relationship between my two dimension and the granularity level of my fact table is the date (day).

Power BI Model

Power BI – Orders Received and Orders Shipped

We’ve received 2,457 orders between Jan 2019 and March 2019 and those 2,457 orders have been delivered between Jan 2019 and May 2018.

Power BI – Book to Bill ration for Jan 2019

And here I filtered out only the orders received in January so among those orders only 369 have been shipped the same month which gives the following ratio: Ratio = Orders Received/Orders Shipped = 0.358

OK now remember there’s no relationship between my two date dimensions so how do we propagate the filter context of the Order Date to the Ship Date? This where Treatas comes in.

Power BI – Book to Bill ratio using Treatas function

Propagate the filter context to unrelated table with Treatas

Orders Shipped:
Only the orders shipped the same month as the received order are counted. This is done by propagating the filter context of the Order Date to the Ship Date.

Orders SHipped (treatas) = 
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])                 
)


Now we can compute the ratio applying the same logic.
Ratio (Book to Bill) = Total Orders Received / Total Orders Shipped

book to bill ratio (treatas) = 

DIVIDE(
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])
                    
),calculate(sum('Fact Orders'[nb Orders]))
)

In my example, my model contains two unrelated Date tables.
When I apply a filter to Order Date[Year Month] “Jan2019” the same filter, “Jan2019” is applied to Shipped Date[Year Month] hence the two tables must use similar column types such as Date, Product, Location, Customer…


You can read more about treatas in the microsoft docs

DAX – Exclude data based on Slicer selection

How works a slicer in Power BI?

A slicer is just like a filter, it is used to narrow a portion of data shown in a visual.
For example if we want to narrow my dataset to only specific regions we just need to tick the region we want to see and all the unticked regions will not show up in our visual.

So a normal slicer includes the selected items and excludes the unselected items. But what if instead of including the selected items we want to exclude them.

How to exclude data from a Slicer selection?

In other words, we want to use a slicer to exclude some data and display all the others.

Step 1:
First, we need to create a disconnected dimension which will be a copy of the dimension used as a slicer, “DimSalesTerritory” in my case.

Step 2:
We then need to create a measure “isExclude” that will be used to find out which rows should be excluded if any.

isExclude = 
IF(
    MAX(DimSalesTerritory[SalesTerritoryRegion]) IN 
    ALLSELECTED('Disconected DimSalesTerritory'[SalesTerritoryRegion]) 
    &amp;&amp; COUNTROWS(ALLSELECTED('Disconected DimSalesTerritory')) <> 
    COUNTROWS(ALL('Disconected DimSalesTerritory')),
    1,
    0
)

The first condition checks which rows should be excluded and the second condition is only used to prevent the measure to exclude everything when nothing is selected.

Step 3:
Now we can create a new measure “Sales Amount (Disconnected)” which will sum up the SalesAmount for all the regions that are not excluded.

Sales Amount (Disconected) = 
VAR FilterRegions = 
    FILTER (
        FactInternetSales,
        [isExclude] =0
    )
RETURN
    CALCULATE (
        sum(FactInternetSales[SalesAmount]), 
        FilterRegions 
    )

Step 4:
All right, you should be all set up now now.
As we can see on the visuals below when we select Australia from the slicer all the other regions appart from Australia are displayed.

Step 4 bis:
If instead of creating a new measure you want the “Exclude Slicer” to work on your visual for any measures you just need to add the “isExclude” measure into the visual filter pane.

Visual level filters for slicers

Since the latest Power BI release (June 2019) we are now able to filter a slicer based on a measure!
And I think we’ve all been waiting for this feature for quite a while.

One of the top reason we needed this feature was to have the ability to sync slicers. (Well this was already achievable with the bidirectionnel filter which is a really bad practice…)
So as we can see on the example below the customer Alma has bought only three products but the whole list of products is still showing up in the slicer Product.

So how can sync the slicers and make sure only the sold products apear in the slicer?
This is pretty simple we only need to have a measure and not a column and drag this measure into the visual filter area of the slicer.

Once we have dragged the measure into the visual filter pane the slicer will only show the sold products.

And if we want to filter a slicer based on more than one measure we can simply create a measure that will check the not null condition on multiple measures and returns 1 if at least one the measure is not null. And then drag this filterMeasure into the visual filter area to sync our slicers.

filterMeasure= 
if(not(ISBLANK([Order Qty])) || not(ISBLANK([Sales Amount])) ,1,0)