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.
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.
Sankey Chart Visual in Power BI VS R
Check out my previous post to see the streamgraph chart comparison.
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. |
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.
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 |
SSRS Security Roles
SQL Server Reporting Services has pre-defined roles for security.
Roles are,
- Browser
- Content Manager
- My Reports
- Publisher
- Report Builder
Each role has set of access which explored below. We can customise these roles or create a new role by connecting reporting services in SQL Server Management Studio.
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.
SSRS Mobile Report URL
This article explains you the possibilities of passing the parameters in mobile report URL.
When we run the mobile report in report manager, usually it will open a URL with folder and report name as like below,
https:////
We have many scenarios where we need to build the shared dataset with one or multiple parameters. To filter the data either we can add the selection list in mobile report and pass the selected item value to shared dataset or we can control through mobile report URL
Scenario 1
Single Parameter
We can pass the parameter to the above URL followed with “?”. The syntax will be like below,
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername>=value
Scenario 2
Multiple Parameters
If you have multiple parameters in your dataset then you need to add “&” symbol followed by your first parameter.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value&<datasetname>.@<parametername2>=value
Scenario 3
Single Parameter with Multiple values -Repeated
To pass multiple values to single parameter then we need to repeat the same parameter with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&><datasetname>.@<parametername1>=value2
Scenario 4
Multiple Parameter with Multiple values -Repeated
To pass multiple values to multiple parameters then we need to repeat the parameters with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&<datasetname>.@<parametername1>=value2&<datasetname>.@<parametername2>=value1&<datasetname>.@<parametername2>=value2
Scenario 5
Parameter with Multiple values with Comma separated
We can pass multiple values with just comma separated. It eliminates the scenario of repeating the parameter with different values.
To get this solution, need to set up the shared dataset to support multiple values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1,value2,value3<datasetname>.@<parametername2>=value1,value2,value3
Scenario 6
Stored Procedure with custom option
The reason for using the stored procedure is to pass “ALL” values instead of trying all values with comma separated.
Limitations,
- Can pass ALL to all the parameters
- Other than ALL, only single value is supported to other parameters
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=ALL<datasetname>.@<parametername2>=value1
SSRS 2016 Branding
Microsoft SQL Server 2016 Reporting Services has multiple new features. One of the features is Branding. It enables us to customise the Report Manager Portal with our preferred colours, logo and custom colours for mobile reports, etc.
You need two files to make your own branding.
1. colors.json
2. metadata.xml
The template for the above files are available in online. You can download here and start to customise it.
There is a possibility that you can get the below error.
Actually, this error is self-explanatory and the solution is very simple.
Need to zip the above 2 files and upload that in Report manager branding section.
The reason for Error:
If you are stored the files in a folder and zip the whole folder and try to upload in branding then you will get the above error message.
Solution:
Select those files and right click and choose to send to ->Compressed. Now you can upload the file.
