Power BI: IPL Analytics- Dashboard

In my earlier blogs, we have seen how to develop Reports using Power BI desktop. Let us use these reports, publish those in Power BI service  and create Power BI dashboards.

Once we have detailed reports available in Power BI desktop, we can publish those in Power BI service.  As we have seen in earlier post of deployment models, if we want to share the Power BI report, publishing the report to Power BI service is one of the options.

Open the report in Power BI desktop and go to Files, select Publish, this will give an option to Publish to Power BI. Once we click on this, it opens a window and asks for credentials to connect to the Power BI subscription. Once we  enter the credentials, it connects and asks if report to be deployed to which workspace. Select the workspace, or default my workspace would be selected.

PublishtoPowerBI

Once we publish the report , we get success message. We can open this report either directly from here by clicking on the link as shown in the below snippet or by going to workspace and locating the report to open.

PublishtoPowerBI2

Now let us see how Power BI Service UI looks like and what features it provides. As soon as we open the Power BI Service, it shows below UI. If you see on left hand side menu, various options are displayed. PublishtoPowerBI3

PublishtoPowerBI4

At the bottom we can see Workspace, which has default My Workspace. Once we click on My Workspace, we can see all components of the workspace such as dashboards, Reports , workbooks and DataSets. In the below snippet, under reports section we can see recently published report. We can open that report.PublishtoPowerBI5

Report would be displayed as shown below. Here we can edit report, however we neither do any ETL activity nor any DAX calculations. We can edit the  report in terms of visuals, ask questions and add additional visuals etc. You can click on Edit report and start editing the report.

I would recommend that one should go though various options available in the menu and explore the components as shown in below snippet.

PublishtoPowerBI8

PublishtoPowerBI9

 

 

 

Let us create a dashboard in Power BI. We already have reports developed in Power BI, As we think of Dashboard, we think of some important information which should highlighted. Hence few information from the report would be pinned to dashboard. As shown in below snippet, for every visual in the report, there would be a pin icon shown at the top left side. e.g. click on the pin icon of Overs Bowled visual.PublishtoPowerBI10

As we click on this pin icon, it will give below screen asking if it is to be pinned to existing dashboard if any dashboard is already created, or to a new dashboard. we can decide as per our requirement.

PublishtoPowerBI11

I am selecting new dashboard and naming it as IPL Dashboard as shown in below snippet and click on the Pin button.PublishtoPowerBI12Power BI would create the new dashboard and pin this visual to the dashboard. We can directly navigate to the  dashboard from Go To Dashboard button as shown in below snippet or can even create phone view of the dashboard.

PublishtoPowerBI13

Visuals which are pinned to the dashboard are called Tiles in dashboards. for  each tile , can take below actions by clicking on the three dots at top right of the tile . If we click on the tile from the dashboard, it would navigate to the report. We can try to explore what every action does and meant for.

PublishtoPowerBI14

We can also view the mobile view of the dashboard as shown in below snippet

PhoneView1

In this blog we have understood about the Power BI service and how create dashboard from Power BI report.

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like button if you like what I am writing.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI: IPL Analytics- Dashboard”

Power BI Jargons: DAX/M/Power Query

In earlier blogs, we have seen how to analyse data in Power BI with the help of data modeling and visualization techniques. Let us go further in depth of data modeling and how we can tweak the data with the help of different ways which are available in Power BI.

When we hear about Power BI, we hear different jargons like Power Query, DAX , Formula language, M etc. Let us see one by one what these jargons mean and how helpful and important these are while analyzing the data in Power BI.

  • Power Query: is the data extraction and transformation engine. The engine comes with a formula language and a graphical tool. Power Query can connect to various data sources. The graphical interface of Power Query is less powerful than the actual scripting language used behind which is formula language
  • M: is the scripting language behind the Power Query which is actually Power Query Formula language. However since this name is quite lengthy, even Microsoft prefers to call it as M. It is used for data transformation and to be used before you load the data for analysis.
  • DAX: is the Data Analysis eXpression language. It is made for analytical data calculations. Once the data is loaded, DAX can be used to analyse the data in depth. Someone who is knows Excel formulas, would find this familiar.

Both M and DAX serve different purposes such as M is used only in query editor and DAX is used in data modelling. Yes! The same data modelling techniques which we have seen in my earlier posts.

Let us see one example how we can add custom column using both the languages.

Referring to our previous data source of IPL, if we want to add column displaying the runs by which match was won. Let us add this column first using the query editor. As explained in above definition, this would be done before the data is loaded in the Power BI. In Power BI desktop, let us click on Edit query button as shown in below snippet.

Then click on the Add Column Section

There we can see custom column button, which actually asks for the definition of the custom column. Kindly refer to below snippet.

We can give desired name to the column and then write the definition of the column. Here we are adding a column to show by how many runs the match was won, we have to check if win_type is runs, show the win_margin. Referring to below snippet, we can give a name to the column and write the formula. As we type the formula and press ok , it would show if there are any errors. 

 Once all syntax errors are removed, it wil give the message that no syntax errors have been detected, as shown at the bottom of below snippet.

I found the scripting of M similar to old VB code. This way we have created a new column before the data gets loaded in Power BI. 

Once the column is created, lets apply the changes and close the query editor.

Below would be definition of the new column created 

If we look at the advance query editor, our data query would look like as shown in below snippet.Power Query15

As M is step by step language, we can see the “added column” step in the right hand section of the query settings.

Power Query16

Now let us see how to create similar column using DAX. Let us go to Modeling tab and  click on New column as shown in below snippet.

Let us add the definition of the new column and new column will be created. This is very similar to Excel formulas

Now we have same calculated columns created using M -Power Query and DAXDAxandPQ

In this blog , we have seen how we can use Power Query- formula language and DAX to achieve the same results . We can always remember that Power query is the ETL langauge and is step by step language, where as DAX is your analysis language. Now the million dollar question is when to use what?

You can refer below blog to understand the  difference between these two. Hats of to the writer, really liked the way it is explained.

http://www.sqlgene.com/2018/04/03/m-vs-dax-chopping-broccoli-vs-planning-a-menu/

Hope you enjoyed learning these jargons!

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like / share button if you like my article.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI Jargons: DAX/M/Power Query”