BLOGS

ByHariharan Rajendran

Monitor Power BI Services Health

It always mandatory to monitor the services that we engaged with. It helps us to understand the status of the services and some additional information.

This is very much suitable for cloud based services like Azure because those services are managed and maintained by Microsoft Azure team.

To monitor the services, you should have admin access. Let us take a scenario with office 365.

Follow the below steps to monitor the Power BI Services,

  1. Connect O365 admin center
  2. Expand Health and choose service Health. It will list out all the services. If the services are working good then you can see a green tick mark near each service name.
  3. Identify the services that are showing any advisory.
  4. Check the advisory and understand the status details.

ByHariharan Rajendran

Multi-factor Authentication for Power BI – O365

It is easy to enable the multi-factor authentication for a user. To enable, administrator permission is required. Once we enable the multi-factor authentication to the user then the user should pass through the authentication wherever that user wants to access.

For example, if you are Office 365 admin and you are enabling multi-factor authentication for a user. That user should go through the second authentication to login to email, power bi or whatever services that are connected with O365.

Follow the below steps to enable Multi-factor Authentication,

  1. Go to office 365 Admin Portal
  2. Expand users -> Active users
  3. Select the user and click multifactor authentication in more settings
  4. Select the user and click enable
  5. Go to service settings and choose the appropriate options.
  6. Connect app.powerbi.com, sign-out and sign-in again. We can choose the method of authentication, either by phone call or sms.

ByHariharan Rajendran

Access Report Manager outside of Network

Nowadays, most of the organisations are using Azure Virtual Machine to host SQL Server Reporting services. By default, we can access the reporting service manager with-in same network. It won’t be possible to access the reporting services outside of network without setting up below configuration.

This is not only for SSRS in Azure VM. The same setup is applicable for the on-premises server.

Follow the below steps,

Step 1: Go to virtual machine resource group and select Network Security Group.

Step 2: Create a new inbound security role with service as HTTP, it will take 80 as a port.

Step 3: Create a same inbound rule inside the virtual machine. Use the below power shell script to create a rule.

New-NetFirewallRule -DisplayName “Report Server (TCP on port 443)” -Direction Inbound –Protocol TCP -LocalPort 80

Step 4: Enable DNS if not configured already. Click Public IP in Virtual Machine overview section in the portal. Set up the DNS name. It will look like below.

Step 5: Make sure report server is configured and working without an issue.

Step 6: Pass the DNS name with extension in your local machine as like below.

Step 7: Pass the credentials and it will show your reports.

Share your comments below.

ByHariharan Rajendran

Connect Azure Virtual Machines on a Same Domain

Connecting multiple machines on the same domain would be one of the requirement to all the network administrator to set up the environment for any scenario.

This post explains this scenario how administrators can they do this setup easily.

Requirement:

  1. Azure Virtual machine with Domain controller
  2. Virtual Network
  3. Another VMs

I pretend that you have a machine with a domain controller on it.

While creating another machine, you need to choose the first machine’s virtual network instead of creating a new one as like below.

Next, you can identify the IP address by typing ipconfig in command line tool.

Also, you can ping the machines each other. In case, if you are not able to ping then turn off your firewall.

Then, try to add the domain and it will throw an error message as like below.

To rectify the above issue, you need to add the domain control IP address on DNS Server. Go to Azure Portal and Network Interface.

In DNS Server add the domain controller IP address in custom and restart the machine.

 

Again log into the machine and now try to add the domain and this time you can add successfully.  Share your comments, if any issues.

ByHariharan Rajendran

Date Table in Power BI – M Language

Power BI Desktop supports Power Query Language which is nothing but M language for data modeling.

This article explains how we can create date table with all the necessary columns. Generating date table is easy in Power BI as we have different methods.

Refer my below article to generate date table manually or with single DAX query.

Date table in Power BI

Use the below M query to create a date table,

//Create Date Dimension

(StartDate as date, EndDate as date)=>

let

    //Capture the date range from the parameters

    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),

    Date.Day(StartDate)),

    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),

    Date.Day(EndDate)),

//Get the number of dates that will be required for the table

    GetDateCount = Duration.Days(EndDate – StartDate),

//Take the count of dates and turn it into a list of dates

    GetDateList = List.Dates(StartDate, GetDateCount,

    #duration(1,0,0,0)),

//Convert the list into a table

    DateListToTable = Table.FromList(GetDateList,

    Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),

//Create various date attributes from the date column

    //Add Year Column

    YearNumber = Table.AddColumn(DateListToTable, “Year”,

    each Date.Year([Date])),

//Add Quarter Column

    QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,

    each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Week Number Column

    WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,

    each Date.WeekOfYear([Date])),

//Add Month Number Column

    MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,

    each Date.Month([Date])),

//Add Month Name Column

    MonthName = Table.AddColumn(MonthNumber , “Month”,

    each Date.ToText([Date],”MMMM”)),

//Add Day of Week Column

    DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,

    each Date.ToText([Date],”dddd”))

in

    DayOfWeek

 

It asks for start and end date. Once entered the date then click Invoke, it will generate the query.

ByHariharan Rajendran

DAX Leap Year In Power BI

Identifying the Leap is very simple logic, this post explains that how you can identify a year which is a leap year or non-leap year in Power BI.

Consider I have a year column on my table. If not then use the Year function on the data column and get the year column.

You need to find the below values,

  1. Year Start Date
  2. Year End Date
  3. Days between Year Start Date and Year End Date.
  4. Check the number of days

That’s it, very simple.

Use the below DAX expressions to identify the above values.

YearStartDate = DATE(‘Year'[Year],01,01)

YearEndDate = DATE(‘Year'[Year],12,31)

TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1

IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)

Happy Learning.

ByHariharan Rajendran

Power BI Pre-defined Transformations

This article talks about the pre-defined transformations that we can directly use in Power BI Desktop. These transformations are part of Power Query Editor.

As a business intelligence developer, usually we spend most of the time to prepare the dataset for report and dashboards. Preparing the dataset includes, getting the data from different sources, data modelling and transform the data.

Data transform is applicable for both rows and columns. Check the list of pre-defined transforms in Power BI Desktop.

These transforms are grouped into different categories.

Table Level

To add a new column

To play with the columns,

To format a text column,

To format a number column,

Date and Time columns,

The above sections are specific to columns. To play with rows,

There any many situations where we need to combine the tables and queries. To handle those scenarios, we can use the below options in Power BI.

To sort the columns,

We also have R script which we can use to perform any complex transform or logics with the data.

Apart from the pre-defined options still we can do complex logics with M language (Power Query Language).

Happy Learning & Share your comments.

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.