BI

ByHariharan Rajendran

Date table in Power BI

Every BI Solutions will have a date dimension table data with other business data. Date dimension is one of the mandatory table as most of the analysis are based on date dimension table.

We can generate the date dimension table in all the scenarios. This post explains you that how we can generate the date dimension table in Power BI.

We have different ways to generate date dimension table. Here I explained two different ways.

Create a table by creating individual column manually

Use New table option and start to create the columns with below expressions.

Column Name Expressions
Date CALENDARAUTO()
DateAsInteger FORMAT ( [Date], “YYYYMMDD” )
Year YEAR ( [Date] )
Quarter “Q” & FORMAT ( [Date], “Q” )
YearQuarter FORMAT ( [Date], “YYYY” ) & “-Q”

& FORMAT ( [Date], “q” )

Monthnumber FORMAT ( [Date], “MM” )
YearMonthnumber FORMAT ( [Date], “YYYY/MM” )
YearMonthShort FORMAT ( [Date], “YYYY/mmm” )
MonthNameShort FORMAT ( [Date], “mmm” )
MonthNameLong FORMAT ( [Date], “mmmm” )
DayOfWeekNumber WEEKDAY ( [Date] )
DayOfWeek FORMAT ( [Date], “dddd” )
DayOfWeekShort FORMAT ( [Date], “dddd” )

Single Script to Generate Date Table

Add the below script in New table expression.

DimDate =

VAR fiscal_year_end_month = 3

RETURN

    ADDCOLUMNS (

        CALENDARAUTO ( fiscal_year_end_month ),

        “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),

        “Year”, YEAR ( [Date] ),

        “Quarter”, “Q” & FORMAT ( [Date], “Q” ),

         “YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q”

            & FORMAT ( [Date], “q” ),

        “Monthnumber”, FORMAT ( [Date], “MM” ),

        “YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),

        “YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),

        “MonthNameShort”, FORMAT ( [Date], “mmm” ),

        “MonthNameLong”, FORMAT ( [Date], “mmmm” ),

        “DayOfWeekNumber”, WEEKDAY ( [Date] ),

        “DayOfWeek”, FORMAT ( [Date], “dddd” ),

        “DayOfWeekShort”, FORMAT ( [Date], “dddd” )

    )

The above script will generate a table with all the columns. We can customise the table by modifying the expression.

ByHariharan Rajendran

Power BI Custom Visuals in Office Store

As we know Microsoft Power BI is one of the best business Intelligence Tool. It is offering a variety of services to the customers. Those services are Power BI as a service – Cloud, Power BI Desktop, Power BI Mobile App, Custom Visuals and etc.
There are plenty of custom visuals are available for us to directly in our reports and dashboards. Those custom visuals are available in below URL.

https://app.powerbi.com/visuals/

The visual library is already moved to office store. Any more new visuals and updates are happening in office store. The above URL will be available until May 31, 2017.

If you have used any of the custom visuals from current visuals gallery, still it will work. To get new updates you need to check in office store.

https://store.office.com/

Visit the above URL and select Power BI under product section.
You can see the custom visuals for Power BI, visuals are available under different categories.

To download and use, follow the below steps.
1. Select the visual
2. Click “Add”
3. You can see an option “Select to download <visual name>”, click.

The visual with .pbiviz extension will be download. You can use the visual in a report as usual with import option.

ByHariharan Rajendran

Sankey Chart Visual in Power BI VS R

Check out my previous post to see the streamgraph chart comparison.

StreamGraph Visual in Power BI VS R

In this post, I have taken Sankey chart custom visual. The power bi custom visual has less functionality compare than R visuals

Power BI Desktop

We have a Sankey chart custom visual available in Power BI. Download and import into Power BI Desktop.

https://store.office.com/powerbiaddininstallpage.aspx?rs=en-US&assetid=WA104380777

Here I used a dataset called Titanic. This is one of the datasets in R which I used for this post.

R Studio 

To get the sankey visual, we need to install the alluvial package.

Run the below script in R Studio or R console application.

install.packages(‘alluvial’)
tit <- as.data.frame(Titanic)
library(alluvial)
alluvial( tit[,1:4], freq=tit$Freq, border=NA,
                                hide = tit$Freq < quantile(tit$Freq, .50),
                                 col=ifelse( tit$Survived == “No”, “red”, “yellow”) )

Power BI Desktop R Studio
Import Sankey Chart visual Install alluvial and related packages
Less Functionality More Functionality
GUI Type R Script Type
Only 2 levels of visual by default for the simple dataset. To see multi-level visuals then need to prepare a dataset with multi-level of source and destinations Multi-Levels of visual – Just add the number of fields.
ByHariharan Rajendran

StreamGraph Visual in Power BI VS R

This post just shows the comparison of streamgraph in Power BI Custom Visual and R Script Visual.

Power BI Desktop

We have a streamgraph custom visual available in Power BI. Download and import into Power BI Desktop.

https://store.office.com/en-us/app.aspx?assetid=WA104380772&sourcecorrid=373810e3-72d4-4afd-96e7-6859505efb87&searchapppos=5&ui=en-US&rs=en-US&ad=US&appredirect=false

Here I used a very simple dataset to explore the visual.

Pass the above dataset values to chart.

R Studio  

To get the streamgraph visuals, we need to install the streamgraph package and other supporting packages .

Run the below script in R Studio or R console application.
install.packages(‘devtools’)
install.packages(‘Rcpp’)
install.packages(‘zoo’)
install.packages(‘DBI’)
install.packages(‘assertthat’)
install.packages(‘tibble’)
install.packages(‘yaml’)
devtools::install_github(“hrbrmstr/streamgraph”)
library(streamgraph)
# Create data:
year=rep(seq(2000,2005) , each=1)
name=c(letters[1:6])
value=c(1000,200,600,500,1250, 300)
data=data.frame(year, name, value)
streamgraph(data, key=”name”, value=”value”, date=”year”)

Power BI Desktop R Studio
Import Streamgraph visual Install Streamgraph and related packages
Visual values are keep

amending on the initial value

Identifies the maximum value and accommodate all the values within the maximum range
It is not interactive It is interactive
GUI Type R Script Type
Can’t export as HTML Can export as HTML
ByHariharan Rajendran

DAX Query CALENDARAUTO()

In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.

This above function will only work when you have a model with at least one column with date or datetime data type.

In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.

You can see the warning message above.

“CALENDARAUTO() function can not find a base column of DateTime type in the model”

In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.

Happy Learning. Share your comments.

ByHariharan Rajendran

SharePoint List to Power BI & Extending Power BI to SharePoint

This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.

I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.

Flow,

Pre-Requisites:

  1. SharePoint List URL
  2. SharePoint Credentials (On-premises or O365)
  3. Power BI Account
  4. Power BI Desktop

Follow the below steps,

Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.

Step 2: Get the SharePoint URL where the list is created.

https://xxxxx.sharepoint.com/Training

Step 3: Open Power BI Desktop and click Get Data.

Step 4: Choose SharePoint Online List, if you use O365. Click Connect

Step 5: Pass the O365 SharePoint URL and credentials.

Step 6: Choose the list and click Load.

Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.

Step 8: Save and publish the report to Power BI online services

Step 9: Login to app.powerbi.com with your credentials.

Step 10: Open your report from your workspace where you published.

Embed the Report in SharePoint

So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.

Follow the below steps.

Step 1: Open the report in app.powerbi.com

Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.

Step 3: It will ask you to publish.

Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.

Step 5: Go to SharePoint page and edit where you want to embed this report.

Step 6: Click Insert and choose the Embed Code

Step 7: Copy and paste the iframe

Step 8: Save the page.

Share your comments below.

Related Post:

Embed Power BI Report in Web page

ByHariharan Rajendran

Embed Power BI Report in Web page

This article explains that how can we show your Power BI report to everyone.

We have variety options in app.powerbi.com for the specific report.

The notable options are,

Publish to Web

Embed in SharePoint Online (preview)

Export to PowerPoint (preview)

Let us discuss “Publish to Web” Option

Open your Power BI report and click File option. You can see the options as like below.

Choose “Publish to Web” and click “Create embed Code”

Then click “Publish”, it will create a code for us.

We can share the link to anyone or else can embed the iframe in any web page or in SharePoint.

Check the report,

https://app.powerbi.com/view?r=eyJrIjoiN2UwNzRmYzYtNTAxYy00NTE1LTkxZTAtYzgyZTk1MzFhYWEyIiwidCI6Ijk2ODY3NjIxLTgxNDAtNGRjYS1iZTliLTMyMTkxMmU0NTY2NCIsImMiOjEwfQ%3D%3D

Sachin Tendulkar’s Cricket Data – Embedded Report

[field name=Iframe]

 

ByHariharan Rajendran

PowerBI Reports in SQL Server Reporting Services

Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.

This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.

This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.

As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.

Look and Feel

As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.

In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.

PowerBi

 

Approaches to publish PowerBI Reports to the portal,

Approach 1:

Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.

Bi1

 

BI2

Approach 2:

Save the report in local folder and upload manually to the portal.

Bi3