The COUNT() is an important function in writing the DAX formula in Power BI used. It is one of the time intelligence functions of DAX, which means it can manipulate data using time periods like days, weeks, months, quarters etc. and then use them in analytics.
We apply DAX to slice and dice the data to extract valuable information. To import data from different data sources and perform required transformations we need to know the use of Power Query. If you are curious to know the difference between Power Query and DAX, Here is an article you may be interested in.
Use of COUNT() in Power BI
The syntax for count function is very simple, we have to pass only the column name as argument like below
Measure = COUNT (Table_name [Column_name])
Count function when applied on any column, it returns the count of cells containing numbers. So it returns only whole numbers and skips the blank cells. If any cell of a column does not contain anything (string, date or numerical) then the function returns blank.
Here is an example of the application of COUNT() on the data set I have on the rainfall of different Indian states. The dataset has three columns “SUBDIVISION” containing different ecological zones of the country, “YEAR” from 1901 to 2019 and “ANNUAL” containing rainfall in mm of the corresponding year.
First, I have created a new measure using DAX (see here how can you create a new measure in Power BI). A measure has a default name “Measure” which I have changed to “Measure_count“.
Using COUNT() function in Power BI
Here you can see COUNT() is used to get the count of ANNUAL column cells having numbers. To see the result of COUNT() I have used a “Card”. The number “4090” in the card shows the cell count of the ANNUAL column having a number.
If we change the column and replace ANNUAL with SUBDIVISION, then the count function returns “4116”. This is because rainfall of all the subdivisions are not present in the ANNUAL column. We can check the difference and know how many subdivisions and year combinations do not have rainfall data.
The COUNTA() function
If a column consists of binary values like True and False, COUNT() fails to count them. To count such values COUNT() has another version which is COUNTA(). COUNTA() is for counting any logical value or text and also the empty cells of the column.
In this data set we dont have any logical values. If COUNTA() function is applied on the same columns i.e. ANNUAL and SUBDIVISION, the results are same as COUNT() gave.
The COUNTAX() function
For those columns which have values other than strings, digits, logical values, date like formulae then there is another useful variation of COUNT() which is COUNTAX(). It returns the count of non-blank rows evaluating the result of an expression on a table.
It also returns whole number and unlike COUNTA() function, it iterates through the cells of that column, evaluates the expression and returns count of nonblank rows.
Here is an example of the application of COUNTAX() on the same table. I have used this function to calculate the count of row number of ANNUAL column for a particular YEAR in the rainfall table. I have used the FILTER() function nested under COUNTAX() to filter the particular rows corresponding to the YEAR=1910 and 2010.
Application of COUNTAX()
From the above figure we can see that the COUNTAX() function has returned two different whole numbers for two different years 1910 and 2010. This is because not all the SUBDIVISION has the record of annual rainfall for the year1910.
As the name suggests Data Analysis eXpressions or DAX in Power BI is nothing but collection of operators, functions and constants which we use in writing formula or expressions to return value/values. It is a native language for data analytics tools of Microsoft. DAX is also a highly versatile and functional language with the capacity to work with a relational database.
DAX helps us to dig into the data we already have in our hand to explore new information. It helps us to perform dynamic aggressions, slice and dice the data. It is different from Power Query with M language at its core. Power Query performs the data extraction from different sources. Whereas DAX is applied to the extracted data source for analysis purpose.
It is very common to confuse between DAX and Power Query. You can refer this article to know a detailed comparison between Power Query and DAX.
Excel formula is similar to the DAX formula. Anyone with experience in writing Excel formula finds it easy to write DAX formula. However, DAX is far advanced than the Excel worksheet formula.
DAX is mainly used to create “Measures” and “Calculated Columns”. Below is an example of creating a measure using DAX.
Example of DAX formula
Writing effective DAX formula is the key. An effective DAX formula helps us to get the most out of the data. Writing the DAX formula in Power BI is easy. Power BI DAX editor has a smart complete feature, which automatically prompts us with probable options.
Now let’s try writing a DAX formula to perform a simple calculation. I already have a data set in the Power BI desktop on the rainfall of different Indian subdivisions. The data was scraped from the web using the data scraping tool of Power BI. You can get the details of how to do it in this article.
Below is an example of how a DAX measure has been created on the Power BI desktop. The screenshots from my Power BI desktop shows the steps of creating a measure. The purpose of the measure is to create total annual rainfall.
First of all to create a new measure, right-click on the “Fields” pane of the Power BI desktop report/data window and then choose “New measure“.
Creating new measure
The default name of the measure is “Measure“. I have changed it to “Rainfall“. As you start writing the function name Power BI starts suggesting with relevant functions name. Here I have selected “CALCULATE“. It is a very popular and frequently used function of DAX.
Steps for creating a measure using DAX
As we enter into the “CALCULATE” function, it starts to prompt us to show that it will accept an expression followed by filters. I have selected the “SUM” function and the “ANNUAL” column of the “rainfall_india” table inside it as we want to calculate the total annual rainfall.
With this, the measure has been created. We can check the “Rainfall” measure in the “Fields” pane under the “rainfall_india” table.
Nested function in DAX
Inside the “CALCULATE” function again I have chosen the “SUM” function. This is an example of a nested function, which is a function within another function. Nested functions help us to narrow down the query to achieve the desired result.
DAX can have up to 64 nested functions. Although using this many numbers of nested functions is very uncommon as debugging of such complex functions is very tough and the execution time of such functions is also high.
Using a measure in another measure
Another useful feature of the DAX formula is it allows using a measure already created within another measure. For example, if want to further narrow down the result to calculate the total annual rainfall of any particular subdivision, we can use the “Rainfall” measure we already created. Let’s see how to do it.
For example, we want to know the total annual rainfall of the state “Kerala“. The measure “Rainfall” calculates the total annual rainfall. So, we need to provide a filter within the calculate function along with the “Rainfall” measure.
Using a measure within a measure
See the above image where I have nested one measure within another. A table and a bar chart are also created to compare the total annual rainfall and Kerala_rainfall just show how the measures are performing.
Row context and filter context of DAX
These two concepts of context are very important for the effective use of DAX. Context refers to the dynamic analysis of the data.
Row context is related to functions while applying filters to identify a single row from the table. In most of the cases, we even dont realize that we are applying the concept of row context.
Filter context is a more complex concept than row context. It applies to narrow down the data. For example, here you can see how the column “SUBDIVISION” of “rainfall_india” has filtered the context and helped us to get the annual rainfall of a particular subdivision.
Different webpages are rich source of data. Either structured or unstructured, these data are very useful and can provide good insights.
Power BI has recently added and enhanced the existing feature of data extraction from the web. This feature was already compelling, with the recent enhancement it has become even more powerful.
In this article I will discuss this feature in detail with a practical example.
A practical example of web scraping with Power BI desktop
I have a data set with information on state-wise crop production in India. The data was collected from data.world. I have discussed this data and how I have analyzed it with the Power BI desktop in this article.
Now my purpose was to analyze this crop production data in context with India’s economic growth. As we know that any country’s GDP (Gross Domestic Product) and GSDP (Gross State Domestic Product) are very good indicators of its economic growth.
So we need to collect this data in order to correlate the state wise crop production with GDP and GSDP of corresponding state.
But the problem is such data is not readily available. So…..
Is web scraping an alternative option?
In this scenario, web scraping is generally the only solution.
You can read this article to know how to write web scrapers with python to collect the necessary information.
But writing a web scraper with Python needs coding knowledge. It is not possible for a person with zero knowledge in software development or at least any single programming language (preferably Python).
Add data from website to Power BI desktop
Here comes Power BI desktop with its immense powerful feature to add data directly from the website. It is really a boon for the data analysts/scientists who want this data import process smooth real smooth.
It does not require any software development background. Anyone with no idea about any programming language at all (really!!) can use it.
So without any further ado, lets jump to see how you can also do the same.
The data source
I will use an authentic website like Wikipedia for open-source data. A data source that has unquestionable authority. If you simply search Google with the search query “Indian states and union territories with their GDP”the first result you will get is from Wikipedia.
Google search result for Indian GDP
Importing the data from website to Power BI desktop
Power BI allows importing data from numerous sources as I have mentioned in the introductory article on Power BI. Among several sources, one is from “Web“.
As you can see in the below figure, you need to select the “Web” from “Get data” option under “Home” tab. Consequently, a new window will open where the URL is to be provided.
Importing data using URL of particular website
If you have already imported the data once from a website, then the address gets stored in “Recent sources“. It will help you to quickly import the data in case you need the data again to import.
“Recent source” of previously used website URL
As you provide the URL, it will first establish a connection to fetch the data. Next, a “Navigator” will open to show you a preview of the data.
See the below screenshot of the Power BI app on my computer; on the left side, all the tables from the web page will be listed. You can click the particular table you are interested and the table preview will be displayed on the right-hand side.
Navigator and table view
Similarly, if you want to get a glimpse of the web view, just click the “Web view“. A web view of the page will be displayed as shown below.
Web view of the page
Transform/load data to Power BI desktop
Now if you are satisfied and found the particular information you are looking for, proceed with the data by clicking “Load” or “Transform". I would suggest going for the transform option as it will enable you to make the necessary changes in the data.
As I have shown the data below before loading it in Power BI. With the help of Power Query,I have made minor changes like changing the column name, replacing the blank rows or replacing the “Null” values, adding necessary columns etc.
I have already described all these operations in data transformation steps here.
Window for data transformation
Once you are satisfied with the table you created, you can load it in Power BI for further processing.
Add table using examples
Another very useful feature is to "Add table using examples". As you can see this option at the bottom left corner of the window in the below screenshot.
Add table using example
This option is very helpful when the tables Power BI automatically shows do not cater to your purpose.
Suppose for the above web page you can see almost all the structured data in table form on the left pane. But you are looking for some information which is scattered on the page and not in a table form.
In that case, if you click the option “Add table using example” you will be provided with a blank table along with the web view as shown in the above image.
Upon clicking the row of the table Power BI provides several options which you can choose from to fill the table. As shown in the above image, some information with no table structure are there to populate the table.
You can also add several columns by clicking on the column header with “*”. Also change column name or later at the data transformation stage.
Final words
So, I hope this article will help you to collect required information from any web page using this feature of Power BI. It is very simple to use only you need to be aware of its existence.
My purpose was to provide you with a practical example of real world data which will make you familiar to this feature. And also to document all the steps for my future reference.
For data analysts and those who just want to get their desired data, writing web scrapers is pure time waste. I myself have written several web scrapers. It obviously has some benefits and can get you some very specific data from several webpages.
But if your data is not scattered in multiple webpages and can be fetched from specific URL, Power BI is your best friend. It will save your lots of time of collecting data and you can straight way jump to the main task of data analysis.
If you find the article helpful, please let me know commenting below. Also if you have any question regarding the topic I would love to discuss with you.
Map visualization in Power BI is a very useful feature to show location-wise values. In this article, I will demonstrate how to use this feature with a practical example.
I have used maps a lot with ArcGIS to create boundary maps and display locations as supplementary information. It certainly requires expertise and considerable time.
Being data analytics guy, I always wondered if there is any tool which will make the mapping task easier and I can focus more on the analysis part. And here comes the Map feature of Power BI to make my life easier.
This feature has been well described by the Microsoft in its Power BI help document. So no point to repeat the same thing here. Rather I will discuss the map feature with some real-world data so that you can apply the same with yours.
I will also discuss some common problems a Power BI user may face while using this feature for the first time (which I have also faced). Certain things you need to keep in mind while using the map in Power BI which are:
The data you want to map needs to be correctly geocoded. Otherwise, it will cause ambiguity while mapping the locations
The purpose of map visualization is to show the geospatial information or the distance between the locations. So, your project should have variables assigned to locations
To remove any ambiguity regarding any locations you may have to produce the latitude and longitude of locations in decimal values.
Types of Map visualization in Power BI
Power BI offers three types of Map visualizations.
A simple Map option creates a bubble map. The size of the bubble varies with the variable set in the value field.
A filled map option. It displays the area of particular locations.
The third option is to create Map using ArcGIS tool.
All these options are available in Power BI visualization pane as shown in the below image.
Different Map visualizations of Power BI
All of the above options are powered by Bing. So you need an active internet connection to use these feature. The map gets created dynamically with the help of internet.
Power BI is very much flexible in order to take location values either city name or PIN number or specific latitude or longitude.
Many a time you may face an error thrown by Power BI due to some ambiguity in the location name. For example, a state and district both have the same name( a good instance is Washington DC of US).
In such cases Power BI itself can not decide how to mark the particular location you want. And so you need to guide Power BI to mention if it is a state name or district name.
You can do that either by editing in Power query or providing specific latitude and longitude of the location.
Practical example: map visualization of crop production in India
Without much ado. Lets dive into the example where I tried this Map option to create some beautiful and informative maps.
The data I have used here contains the crop production statistics of different states of India. The data I have collected from data.world website. And it is in a very refined state. So you don’t need to do any serious transformation.
Just some removal of blank values, filtering as I have shown in this article is enough to start with. Also, I have made custom keys from the main table for the purpose of data modelling (here is the process).
The data set contains following columns
“State_Name” containing different states of India.
“District_Name” containing state-wise different districts name
“Crop_Year” has the year of production
“Season” has the particular season of crop production
“Crop” is the particular crop name
“Area_ha” this column contains a total area of under the crop in Hectare
“Production_tonnes” has a total production of the season in Tonnes
Creating the Bubble map
This is the most interesting Map visualization offered by Power BI and here we will create this map first. Bubble map is very frequently used in data analytics. The size of bubble helps us to compare the magnitude of any parameter across different locations.
Here we need to click the Map icon from the visualization pane as shown in the first image.
Then as shown in the below figure I have selected “India_states” for the location field and “Crop_productivity” for the Size field. The purpose is to show the crop productivity of different states on the map of India.
Selecting location and size for bubble in map visualization
So here is the report output. See the below screenshot of my Power BI desktop report page. Different states of India has been located with the bubbles. The bubbles are of different size and proportional to values of crop productivity.
You can see now how it has become to visually compare the crop productivity of different states of India. Which is otherwise a tough call in a table form visualization.
The bubble map with different state wise crop productivity
Changing the fields to add more information in the map visualization
Lets make little changes in the fields of the map we created. And try to add some more information. Suppose we need the district level information. We also want the map to show different states separately.
To do that we can use the field “Legend” which we have left blank earlier. See that we have added “India_states” in that field. Also this time I have replaced “Crop_productivity” with “Area_ha“.
Here I want to mention one important point. You should drag the particular field from the right pane to the left drop-down fields instead of selecting them with left click (see the figure below).
If you select them with a click then Power BI automatically assigns them field which may not lead to something you want.
Selecting the fields
See the result below. All the states are shown with different colour shades. Different districts within states have bubbles with size proportional to the cropped area. Such a map can quickly provide an overview of the crop production scenario with a glance.
You can see the “Bing” icon at the extreme bottom-left corner. It suggests that all these map functionalities are provided by Microsoft Bing. So you need to be online to create the maps.
Formatting the Map visualization in Power BI
The basic map feature has several formatting options. You have all these formatting options available under the visualization pane with a brush icon as shown in the below figure.
Format option for basic map visualization
With the help of this format option, you can change the category, data colors, bubble size, map styles, map controls and many other visual appeal of the map you created.
You can also change the themes of your map if you are using Power BI “online service”. By default the “Road” theme is applied. The other themes available to opt from are Aerial, Dark, Light and Grey scale. Power BI desktop does not have this option.
Filled map
This is another basic map visualization option where the territory of the particular location is displayed as a filled one. For example, if we apply the same criteria as we have set for the map option, then it looks like as in the below image.
To create this map I have just clicked the “Filled map” icon in the visualization selecting the existing map. And the old map has been replaced by this new filled map in the report.
Filled map visualization in Power BI
Here we can see that the selected states are simple filled with different shades as shown in the legend. The same format options are also available for filled map also. If you want some variations in its appearance other than the default one, you can apply them.
ArcGIS map visualization in Power BI
The last and third map visualization option available in Power BI is ArcGIS map. The process of creating such map is also same. You just need to select the ArcGIS option while selecting the already created filled map.
See the below image the same map with the same options has been converted to an ArcGIS map.
ArcGIS map visualization in Power BI
An ArcGIS map has some advanced features which other map visualizations do not offer. These features are as given below:
When you zoom in and zoom out in the map the clustering features
It has advanced options like drivetime and distance radii
You can have heatmap visualization
You can add reference layers from ArcGIS online repositories
Some infographics options are also available as default in the report
All these advanced features come with some limitations like:
This map visualization option is not available for Power BI report server
In case you want to publish or embed the map in the web then this map option is not visible
The custom shape options are available only if the maps are added to the ArcGIS online and publicly shared
Final words
So, we have completed all the map visualization options available with Power BI. A practical data set I have used to demonstrate all of them.
I have kept this as simple as possible and discussed all the important functions so that you do not need to waste time. Otherwise, all the first time users invariably waste a lot of valuable time looking for the correct steps.
Hope you will find the article helpful. I would appreciate if you point out any other queries regarding the topic and suggestions by commenting below. I would like to answer them and also try to improve the content further.
Measures in Power BI are really a beautiful feature. They are fast in the calculation, has the benefit of reusability. Measures can be applied to multiple tables. We create measures to obtain counts, averages, sums, ranking, percentiles, aggregating year to dates and many more handy calculations.
Measures are dynamically calculated. And most importantly it gets imported in other applications like MS Excel through its report format. A good data modelling and creating useful measures are among the core skills Power BI Pro users have.
So, in nutshell, measures in Power BI are one of the most important features you must know and use. In this article, I am going to discuss how to use them with a simple application using real-world data.
I would suggest going through this article for in-depth knowledge of Power BI measures. It is from the creator Microsoft itself. This article will give you the overview and the necessary idea about measures to apply the steps described here.
NB: Being a non-native English speaker, I always take extra care to proofread my articles with Grammarly. It is the best grammar and spellchecker available online. Read here my review of using Grammarly for more than two years.
The data set used here is the same used to demonstrate the data modelling process in Power BI. For the first time readers, a brief description of the data is given below.
The data I used here is crop production data of different states of India. This data is accessible at Data.world. This is real-world data and without any garbage.
The columns are
“State_Name” containing different states of India.
“District_Name” containing state-wise different districts name
“Crop_Year” has the year of production
“Season” has the particular season of crop production
“Crop” is the particular crop name
“Area_ha” this column contains a total area of under the crop in Hectare
“Production_tonnes” has a total production of the season in Tonnes
Use of DAX (Data Analysis Expressions) to create Measures
We use Data Analysis Expressions (DAX) in Power BI to create measures. The same DAX is used for Excel formulas. The only difference is, in Excel, DAX is applied on cells and columns whereas, in Power BI, DAX is applied on tables and columns of the data model. And they are very fast in calculations.
Implicit and explicit measures: which one you should use?
Measures are of two kind one is implicit measures which gets calculated by default and the other one is explicit measure. Now both of these measures yield the same result.
But there are subtle differences between the calculation approaches of these two. Which you should know and use accordingly.
Implicit measures are easy to calculate and you need not write any DAX expressions for it. But it has its own share of disadvantages. Which is Implicit measures are not reusable.
On the other hand, explicit measures require some knowledge of writing DAX expressions to achieve desires result. But they can be reused. Also, you can make little changes in the DAX expression you wrote to use it for other calculations.
An example of limitations of Implicit Measures is its inability to handle division by 0(zero). If there are some calculations have divisions and your data contains some zeros in the numerator, then Implicit Measures will give garbage values.
But such a situation is well handled by Explicit Measures. Where these measures through a tiny error displaying “NAN” i.e. “Not A Number”. In this way, you can understand the possible problem and can correct it.
New Measure and Quick Measure
These two are another options provided by Power BI when you need to create measures. Now again, creating a Quick Measure is easy and does not require writing DAX expressions.
Where creating New Measure needs writing few lines of DAX expressions. But it has some advantages over Quick Measures. I will discuss it with example here in a bit.
The figure below displays the options for creating “New measure”, “Quick measure”, “New column” and “New table”.
Different measures option in Power BI
While in case of “Quick measure” a wizard opens which help us to define the calculation. Again it is easy as we don’t have to bother about writing DAX at the cost of losing the flexibility and reusability offered by it.
Common mistake while creating new measure
Here I would like to mention a very common mistake while you are in the process of creating you first ever New measure. It may sound silly but related to very basic concept of measure in Power BI.
The role of a measure in Power BI is to apply some aggregate functions of the column values in the report. So, it is important to remember that measures can not be created using single row values.
See the error thrown by Power BI in the below figure when I have tried to create a new measure using single row values. My intention was to calculate productivity by dividing crop production with the area.
Common mistake while creating measures for the first time
And the error clearly directed me that a “single value” can not be determined. We need to create “New column” for that purpose.
Creating a “New column”
For creating a column to calculate the productivity from the crop production and area values, we will create a “New column” here. Simply click the “New column” option. A formula bar will appear where we need to write the DAX for the calculation.
Creating “New column”
Now see in the below figure where the DAX has been written for crop productivity calculation. Important to note that I have mentioned an alternate value as 0 at the end of the expression. It avoids the problem that arises whiling dividing by 0.
Creating new column with crop productivity
Creating “New measure”: an Explicit measure
While we create new measure by clicking the “New measure” option, a formula bar appears in the window. See the below figure. The “Fields” pane at the right also starts to display the Measure with a calculator icon. By default, a new measure has the name “Measure”.
Creating a “New measure”
Now we have written a DAX for calculating the average crop production. Its very simple and remember selecting the relevant table while writing the DAX. It will help you select the right column by prompting from the selected table columns.
Creating a new measure
As I have already said that explicit measures are reusable, it means that one explicit measure can be used to create another explicit measure.
Application of an Explicit measure
Use of explicit measure
Implicit measures
Here we will see how a measure can be created implicitly. It is an easier option compare to explicit measures. But it is always advisable to go for an explicit measure for its several advantages as I have discussed before.
In the below figure you can see that a bar plot has been created for the Area_ha column. By default it is the sum which is selected. You can change to other options as available in the figure.
And all these options create an implicit measure for each of them. You will not get them in the field options there. Which means that implicit measures are not reusable. Every time you want to do the same calculation, you have to go through the same process.
Creating an implicit measure
The bar plot takes only the measure “Area_ha” which gives the sum of all the cropped area from all the states. So, it gives a single bar for a single value. You can drill down further to any level adding them as values.
Like in the below image we can see that the cropped area has been displayed district wise.
Application of implicit measure
Final words
In this article, we have mainly focused on creating measures both implicit and explicit. Both measures have been shown using practical examples.
As I have discussed the merits and demerits of both the measures, it is prudent that creating explicit measures always has the edge. Implicit measures are easy to apply. That’s why any new Power BI users always prone to use the implicit measures.
Although in the long run, when he delves into more advanced use of Power BI, the data size and complexity increases, he finds that using implicit measures are not helpful in terms of their reusability.
Also, as the Explicit measures get dynamically calculated, it puts less burden on the computer memory and quick to give result.
Hope this article will help you to get a good grip and understanding on the use of measures. If you have any queries or issues regarding application of measures let me know through comments below.
I would like to address them which will enrich my knowledge too.
Data model relationships are the core of Power BI. The report preparation and visualization becomes very easy if data modelling is done well. So here in this article, we will discuss data modelling relationship with a simple example.
A common misconception among Power BI users is that Power BI is all about visualization. But that is not the case. Visualization is only the icing on the cake. Whereas the data modeling part is the cake itself.
You should give 75% of your time in data transformation and modeling. For data visualization rest 25% is enough. A good data model will enable you to dig into the data insight and to know the relationship between all the variables.
You can get a good detailed discussion on data modeling in Power BI here. The document is prepared by Microsoft itself. So no point to discuss the same thing here again. My target will be here to present you a small practical example of data modeling.
Use of multiple narrow tables
I will encourage you to use multiple narrow tables instead of a big flat table with several columns. And here lies the beauty of data modeling and relationships. Power BI can work with big wide table. But it is a kind of constrain while scaling the model.
With time your project is going to increase in size. The tables will increase in rows. Chances are there that table number will also increase as well as the complexity of your model. Fetching required information from a big table with lots of columns is time-consuming. It occupies a good amount of computer memory too.
In this scenario use of multiple tables with few columns can make your life easier.
Data model relationships in Power BI: from the Business Intelligence point of view
Business Intelligence or BI is of very critical importance in industries. Almost all businesses have a well-developed database to store all kind of transactions. Over time such databases get huge and extracting information becomes complex.
Extracting information from such big data requires experts in the field. Business houses appoint software developers to extract data, transform and load in a well-maintained data warehouse.
Creating a data warehouse is essential because no industry wants to disturb its always busy database. It may lead to jeopardies the real-time data transaction process. That’s why software developers keep their required information in a data warehouse of their own.
Now for a person without software development background it is tough to handle a data warehouse. Here comes the power of Power BI. It makes the whole ETL (Extraction, Transform and Load) process of data a cake walk for anyone with zero knowledge in software development.
A practical example of data model relationships in Power BI
Here is a simple example of how you can model relationships in Power BI. The data I used here is crop production data of different states of India. This data is accessible at Data.world. This is a real world data and without any garbage.
Data world already has provided the data in a refined manner. So, very little left for us on the part of data cleaning. And we can straight way jump into the modeling process.
Below is a glimpse of the data file imported into Power BI. This is the screenshot of the data transformation window of Power BI.
Glimpse of the crop production data
The columns are
“State_Name” containing different states of India.
“District_Name” containing state-wise different districts name
“Crop_Year” has the year of production
“Season” has the particular season of crop production
“Crop” is the particular crop name
“Area_ha” this column contains a total area of under the crop in Hectare
“Production_tonnes” has a total production of the season in Tonnes
Now this is not a very big table. Still we can reduce its size and create a custom key to further improve the data model.
Creating a custom key
Using a column to create a custom key needs selecting a variable to create a separate table. Let’s select the “District_Name” to create a new table.
Now, why should I select this column? the answer is easy this column has some redundant values. The same way the “State_Name” also does not have unique values so either of them can be selected.
We will create a separate table with this column where they will be unique with a far lesser number of rows. In this way, these tables will help us data munging, data wrangling and whatever fancy term you want to use 🙂
Trimming the column
The first step is to trim the column to remove any unwanted white spaces from the column. Just select the column in question, right-click and select transform to get the trimming option. See the below image.
Trimming the column
Removing the blank values
The second most important step is to replace any blank values. Blank values are a real problem in data analytics. Blank values can lead to spurious result as tools generally not capable to handle them. So, we should filter them out carefully in data transformation stage itself.
We can easily inspect the column quality and distribution from the view tab as shown in the below image.
Checking column quality and distribution from the view tab
So we can conclude that this particular variable is already free from any white space. See that data quality option shows “Empty as 0%”
But if your data has blank values then it is a convenient option to check it. For example, if we check the “Crop_production” variable we can see that the empty as <1%. That means it has some blank rows.
Checking for rows with “null” values
To check for the blank values we need to click the filter icon at the left of the column name. Uncheck the “Select All” option and check only the null values as shown in the above figure. Now Empty fields are 100%.
Replacing null values with 0
In order to replace the null values, you have to right click on the column name and select the “Replace Values…” option. In the new window replace the null with 0 and click OK. Now you can check the Empty has become 0% and all null values have been replaced by 0.
As no other column shows any value as Empty, so we have made our data completely free from any blank values. And we can now proceed for next step.
Creating new query and converting to table
In the next step we will create new query using the “Add as New Query” option. See the below image for the steps you should follow to create new query and then converting to a new separate table.
Creating new query and converting to new table
Removing duplicate entries
The newly created column has several duplicate entries. To make it a table with only unique entries, we need to remove the duplicates. As given in the below image, click the option “Remove Duplicates“. Now you have a column with all different districts of India.
Creating table with “District_Name”
Creating IDs for the column
It is very helpful creating a custom ID for the values in newly created column. Since the IDs are created by you, you have full control over it. Creating ID has a simple option in Power BI. See the following image to understand the steps.
Creating index column
The “Add Column” tab has options for both custom column and default column IDs starting from 0 or1. You can choose any of them. For example, here I have selected column ID starting from 0.
Merging the tables
The next step is to merge queries. As we have completed creating custom keys, now we need to merge the designated tables. See the following images to complete the tasks. Go to the “Home” tab and select “Merge Queries” option.
Selecting merge queries option
Now a “Merge” window will open and you need to verify that both the tables have exact same number of rows match. Here you can see that in both tables we have 246091 number of matching rows.
Merging the tables
Now the newly created column will appear in the table. You can see in the below image that the whole table is displayed as a column. So you need to select the particular column and deselect the option “Use original column as prefix“.
Selecting particular column from merged table
Data model relationships in Power BI: task completed
With the last step, we have completed creating a data model relationship between the two tables. You can verify that in the “Model” view of Power BI. It displays a “One to Many” relationship between the tables.
Final data model relationship
Final words
So, here is a simple example of data model relationships in Power BI with practical data. It also displays how can you create custom keys to join or merge into different tables. It is an effective way of creating a relationship as you have full control over the keys.
I hope this article will help you to start with data model relationships in Power BI with ease. In case of any doubt you can comment below. Also suggest if there any interesting topic regarding data modeling you have.
With this article, I am planning to start a series of articles over the Power BI application to solve real-world problems. So, keep visiting the website for new and interesting articles.
Microsoft’s Power BI is a very popular and most frequently used data visualization business intelligence tool. This is an introductory article on Power BI which will be followed by a series of practical problem-solving articles. So it will be “learning by doing”.
Data exploration and visualization is the most basic yet very important step in data analytics. It reveals important information to understand the data and relationships between the variables. Without a thorough understanding of these variables, we can not approach for in-depth analysis of the data.
Although there are programming tools like R and Python which are the most capable of advance data science jobs, using them for data exploration every time is tedious.
They prepare exhaustive analytics on the data with some beautiful, professional-looking charts, interactive dashboard and real-time reports really quickly. Another added advantage is collaboration with your team by publishing the report through their cloud service.
For industry leaders, it does not matter if you have created a report writing a thousand lines of codes or used a tool like Power BI. They need just a good analytics and insights from the data. So, besides tools like Python and R a data analyst should have a working knowledge of these tools too.
But why Microsoft Power BI for data visualization?
See the below figure where the different Analytics and Business Intelligence Platforms are compared with respect to two different parameters. On the X-axis it shows the completeness of vision and on the Y-axis it has the ability to execute. We can see that with respect to both these parameters, Microsoft finds its place at the top.
Analytics and Business Intelligence Platforms comparison
Increasing popularity: Google trends
Microsoft Power BI has a close competitor as Tableau analytical software for Business Intelligence. Tableau has been a popular choice of business houses since long. Compare to Tableau, Power BI came very late for public use.
Tableau made its presence in the field of business intelligence way back in 2004. Whereas Microsoft Power BI was designed by Ron George only 10 years back in 2010 and made available for public download on July,11 of 2011.
Soon after its availability, it rapidly became popular mainly because of its more affordability, user friendly interface and most importantly its compatibility with hugely popular widely used Microsoft office products.
Below is an example (Google trends) how Power BI, since its inception, gradually overtake Tableau in popularity and became the most Google-searched term in business intelligence tool in recent past.
Installation of Power BI for data visualization
I will discuss the free version of Power BI which is Power BI desktop here and you can install it from the Microsoft store itself and the process is well documented here. Just search Power BI in the windows store and click download. It will start downloading a file sized nearly 500 MB.
Below is the screenshot of my computer when the installation was complete and Power BI was ready to launch.
Power BI desktop in Microsoft store
When it is opened for the first time, you will get to see a window like below.
First screen after Power BI gets installed
Just click “get started” and you are ready to work with the Power BI. The first step is to bring the data in Power BI. Here you will get an idea why Power BI is so popular. It has the capacity to load from any kind of data source you can name.
Compatible data sources for Power BI
Power query
Power query is the most powerful feature of Power BI. It helps us clean the messy raw data. In real-world the data, we have to deal is never clean. Sometimes the data is arranged in a fashion which is only human friendly.
A human-friendly data means lots of tags and long strings within the data file for the ease of human understanding. But a machine does not have anything to do with these texts and arrangements. Rather they create problems to get read by a computer.
Power query helps to clean the data and also transform to create desired information with the available data. Below is an image showing how you can create a custom column from the columns available.
Custom column in Power BI
Query editor
So in this data transformation step, we need to convert a human-friendly data to a machine-friendly data. Don’t just click on “Load”, spend some time in data cleaning before loading it. And here comes the most powerful part of Power BI “Query editor”.
In the below image a query editor has been used to modify the data. According to your transform action, the query is built by Power BI by default. The query used the popular “M language”. You can make necessary changes in the query to get the desired result.
The syntax of the query is very easy and in case any problem a quick Google search is always helpful.
Use of query editor
You need to master the use of Power BI query editor in order to dig deep into the analysis. It is a common misconception that Power BI is only about visualization. Many Power BI users directly jump into the visualization without proper data transformation and data modelling.
But it is a wrong practice. Query editor is refered as the “Kitchen” of Power BI. The better you will able to use it the more information you can extract from the data.
Use narrow tables
A good practice for data modelling is to use narrow tables. A wider table will create a problem when you attempt some complex calculations. Always break down a wider table identifying variables which are redundant and place them in a separate table.
Using tables with lesser columns instead of wide tables
As your table size grows with time i.e. number of rows increases. And there are chances that you need to add more tables in data modelling. In both cases narrow tables will help you to create relationships with ease and fetch required data.
Create as many tables as required for analysis. And later link them using primary key and foreign keys. We will discuss this process in a separate article dedicated to data modelling.
Power query is the built-in data tool for both M.S Excel & Power BI. It helps us to transform the data as we wish in order to build a meaningful model between the dimension tables and fact tables.
Star schema: Fact tables & dimension tables
Fact tables and dimension tables are components of dimensional modelling with a star schema.
Power BI for data visualization: Star schema
Fact tables
These tables are generally long tables containing numeric values.
Dimension table
Now dimension tables are those table which generally contains information related to Who, What, Where, When and How about the fact tables. These are short tables compare to fact tables and contains mainly string variables.
Query settings
It is the particular part of Power BI data transformation window where all the magics happen. It automatically keeps track of all the data transformation steps you perform. You can go to any of these steps any time and make any kind of changes you want also replicate them further.
It kind of helps you to time travel to see the changes, how the data was cleaned and what are the data transformation steps.
DAX: Data Analytics Expressions
These are functions and operators available in Power BI which enable us to perform analytical services, building formulas, data modelling and information extraction (source: Microsoft document).
DAX functions are almost 250 in numbers used in different data transformation applications.
Measures Vs calculated columns
Implicit measures
Implicit measures are already defined measures in Power BI.
Explicit measures
These are measures defined by the Power BI user and has many benefits over the implicit measures. Explicit measures give the Power BI user more freedom, reusability and flexible applicability through connected reports.
Measures and calculated columns
Both of these options yield the same result and may appear as with the same functionalities. But it is always a good practice to use Measures over calculated columns for the reusability and flexibility of Measures.
Measures also have a plus side when you want to publish your analytical report with Excel or other applications. If you open any Power BI published file with excel, you can find the Measures you created but not Calculated columns. Hence you can reuse only the Measures not the calculated columns.
Measures make your application more light and memory friendly
Measures are being calculated dynamically and do not consume any space in the file. Hence use of measures instead of calculated columns keeps the Power BI application file very light in size even lighter than an excel file.
Use of Explicit measures also makes your data model execute very fast. You should always keep the scalability factor in mind while building models. Your model should be flexible so that it remains equally efficient with the increasing data size.
Hybrid measures
These measures are to calculate some statistics using variables from different tables. Once such measures are created they become a part of the model and can be used everywhere.
Unpivot columns
This is also a beautiful feature of Power BI. With a click it can make the rows converted to columns and vise versa in a table.
Model & relationship view
As soon as you complete your transformation/editing the data file the files are saved as Power BI files. Power BI by default identifies the relationship between the files and displays them in the Model and relationship view. You can change the model relationship and build your own.
Below is an example of such modelling. A typical star schema consists of one to many relationships from dimension tables to fact tables. In this image, I have one many to many relationship also. But keep in mind you should have a clear idea of why you are creating it.
Data relationship view
This modelling part is so important. An efficient data modelling leads to easy and effecitive data analysis. A basic idea of data warehousing can be helpful here.
Once you are done with data transformation and model building, click “Close and apply” to exit the query editor.
Here we begin…
Power BI is such an interesting and powerful analytical platform and I wish to devote a complete blog category on this. As I am learning it the interest continues to grow.
It may appear overwhelming at first. With more practice, things become easier. Even you will become hungry for a more complex problem with messier data.
This is just starting off learning Power BI and here is only a glimpse of some important features. I will be covering more advanced applications in coming articles. I will update you about different analytical techniques as I learn them.
Let me know how you find this article and which topics you want to be covered in coming blogs by commenting below. Your suggestion will help me to improve the quality of articles as well as to introduce new interesting topics.
