What is DAX

Before we understand PowerBI, lets learn:

What is BI?

Business intelligence is a technology-driven method which helps you to analyze data and to provide actionable information which helps corporate executives, business managers, and other users to make informed business decisions.

In this Microsoft Power BI tutorial for beginners, you will learn Power BI basics like:

What is Power BI?

Power BI is a Business Intelligence and Data Visualization tool for converting data from various data sources into interactive dashboards and analysis reports. Power BI offers cloud-based services for interactive visualizations with a simple interface for end users to create their own reports and dashboards.

Different Power BI versions like Desktop, Service-based (SaaS), and mobile Power BI apps are used for different platforms. It provides multiple software connectors and services for business intelligence.

In this Power BI training, you will learn all the important concepts of Power BI and develop a foundational understanding of how to use Power BI tool.

Why use Power BI?

Here are the most prominent use of Power BI tool:

Pre-built dashboards and reports for SaaS Solutions
Power BI allows real-time dashboard updates.
Offers Secure and reliable connection to your data sources in the cloud or on-premises
Power BI offers Quick deployment, hybrid configuration, and secure environment.
Allows data exploration using natural language query
Offers feature for dashboard visualization regularly updated with the community.

Types of Power BI tools

Now in this Power BI desktop tutorial, we will learn about types of Power BI tools.

Some Important Power BI tools are:

Power BI Desktop

Power BI desktop is the primary authoring and publishing tool for Power BI. Developers and power users use it to create brand new models and reports from scratch.

Costs: Free

Power BI service

Online Software as a Service (SaaS) where Powe Bl data models, reports, dashboards are hosted. Administration, sharing, collaboration happens in the cloud.

Pro license: $10/users/month

Power BI Data Gateway

Power BI Data Gateway works as the bridge between the Power Bl Service and on-premise data sources like DirectQuery, Import, Live Query. It is Installed by Bl Admin.

Power BI Report Server

It can host paginated reports, KPIs, mobile reports, & Power Bl Desktop reports. It is updated every 4 months and installed/managed by the IT team. The users can modify Power Bl reports other reports created by the development team.

Power BI Mobile Apps

Power BI mobile app is available for iOS, Android, Windows. It can be managed using Microsoft Intune. You can use this tool to view reports and dashboards on the Power Bl Service Report Server.

Data sources for the Power BI

Data source	Description
Excel (.xlsx, xlxm)	A workbook can have data entered manually or data, which is queried and loaded from external data sources.
Comma Separated Value (.csv)	Files are simple text files with rows of data. Every row can contain one or more values, which is separated by a comma.
Power BI Desktop (.pbi)	You can use Power BI Desktop to query and load data from external data sources.
Databases in the Cloud	It allows you to connect live to Azure SQL Database, Azure SQL Data Warehouse, etc.
Databases on-premises	You can connect directly to SQL Server Analysis Services Relational model databases. A Power BI Enterprise Gateway is required.

Key terms used in Power BI

Term	Description
Visualization	A visual display of information to achieve one or more objective. It offers a single-screen display of information. It alerts users on issues or problems Operational, Performance, Personal, etc.
Datasets	A dataset is something which you import or connect to. Datasets can be renamed, refreshed, removes, and explored.
Dashboard	The dashboard is a collection which contains zero or more tiles and widgets. It is used to represent a customized view of some subset of the underlying datasets.
Reports	A Power BI report is one or multiple pages of visualizations. It can be created from scratch, imported to a dashboard, and created using datasets.
Tile	It a single visualization found in a report or on a rectangular dashboard box which contains each visual.

History of Power BI

Power BI was conceptualized by Ruler and Dhers Netz of the SQL server coverage services team at Microsoft.
It was designed by West Chadic George in the year 2010 and named as a Project Crescent.
In 2011, It was bundled with SQL Server Codenamed Mount McKinley.
Microsoft unveiled the first preview to Power BI in September 2014.
The first version of Power BI released on 24 July 2015. It was based on Excel Based Add-ins like Power Query, Pivot, view, and Map.

Architecture of Power BI

Data Integration:

An organization needs to work with data which comes from different sources which can be in various file formats. The data should be extracted from a different source which can be from different servers or databases. This data is integrated into one standard format in a common staging area.

Data Processing:

In this stage, the integrated data is still not prepared for visualization as the data needs processing. This data is pre-processed. For example, redundant values, missing values will be removed from the data set.

The business rule should be applied to the data when the data is cleaned. You can load that data back to Data Warehouse.

Data Presentation

Once the data is loaded and processed, it can be visualized much better with use of various visualization that Power Bi has to offer. Use of dashboard and report helps one represent data more intuitively. This visual report helps business end users to take business decision based on the insights.

Install and run Power BI Desktop

Step 1) Visit https://powerbi.microsoft.com/en-us/desktop/

And select Download free button

Step 2) You will be redirected to a Microsoft store and select Get button.

Step 3) Click on the Install button.

Step 4) You can see progress status on the screen.

Step 5) Before welcome screen you will ask to register to enter.

Step 6) When you run Power BI Desktop, a Welcome screen is displayed.

In 2022 the reported cost of a breach reached an all-time high.

Do you want to spend money running your business? Or getting your business back up and running? Get the Cost of a Data Breach Report 2022.

Power BI Dashboard

Below figure demonstrates the Power BI Dashboard:

Power Bl Desktop is an enlargement tool used to generate dashboards and reports. Power Bl applications will be accessed by other users through desktop and mobile devices.

There are Power BI components are 5 main components released in the market.

Power Query: It can be used to search, access, and transform public and/ or internal data sources.
Power Pivot: It is used for data modeling for in-memory analytics.
Power View: This component allows you to visualize, analyze, and display data.
Power Map: It brings data to life with interactive geographical visualization.
Power BI Service: It allows you to share data views and workbooks, which you can refresh from on-premises and cloud-based data sources.
Power BI Q&A: It allows you to ask questions and get immediate answers using a natural language query.
Data Management Gateway: It offers periodic data refreshers, view data feeds, expose tables.
Data Catalog: This component allows the user to discover and reuse queries using the Data Catalog. Metadata can be facilitated for search functionality.

What is Dax Function?

DAX is a formula expression language which is called (DAX) which can be used with various visualization tools like Power BI. It is also known as a functional language, where the full code is kept inside a function. DAX programming formulas contain two data types: Numeric and Other.

Each is linked to the other by having common columns. Here is a simple diagram of Power BI Dashboard Example showing the relationships.

There are 3 things in Power BI where you can use

DAX –

Calculated Columns
Measures
Tables

Let’s see all these Power BI examples and see how DAX functions works.

Calculated Columns

Calculated column allows you to create new columns based on the given data.

For example, there is no ‘ Final price’ column available in the Items table. Here, the DAX function is used to calculate a new column when only total price & quantity are available.

Price = List_Items[MRP]*List_Items[Qty]

In the data shown in above Power BI example, each row will now have the respective calculated price.

Measures

You can perform a calculation using measure without the need to add any data as shown in the below Power BI example. This is very helpful for reports where the price can be displayed, without requiring an entirely new column to store it.

Example:

1] Total of the MRP column * Total of Qty column

Tables

DAX functions in tabular model return entire tables. For example, In order to generate a list of all the country the organization has clients in, use the function:

cities touched = DISTINCT(Customers[City])

What’s your IT org’s sustainability and climate strategy?

Get the State of Multicloud report and see if you’re a leader or a laggard in your industry.

A word on Filters

Filters hide rows that don’t fit given criteria. A calculation after filtering out data will be applicable only on a row which matches those criteria.

Power BI DAX Functions

Some Important DAX functions are:

Average

This DAX function allows you to find the average from a given set of values as shown in the below Power BI example.

Example –

AvgComm = AVERAGE(List_Items[Price])

Max

Helps you to find the maximum from a given set of values.

Example – Find out the highest order.

HighSale = MAX(List_Items[Price])

Min

Helps you to find the minimum set of values.

Example – Allows you to find out the lowest order.

LowestSale = MIN(List_Items[Price])

Count

Count any umerical data.

Example – Count number of ticket issued.

TicketVolume = COUNT(Invoices[Ticket])

Concatenate

This function helps you to join values in calculated columns. You can use ConcatenateX if using in measures.

Example – Concatenate the Item names, and MRPs will give a unique code for all the price points at which each product is sold.

ProMrp = CONCATENATE(List_Items[Item],List_Items[MRP])

TotalYTD

The function allows you to calculate the sum from the start of the current Year to the specified date. It performs calculate base on a calendar year, not a financial year.

Example – Calculate Sales totals for the price column.

CumiSales = TOTALYTD(SUM(List_Items[Price]),Invoices[Date])

All

Returns everything. Ignores filters.

Example – Used with the calculate function above.

Power BI vs. Tableau

Here, are major differences between Power BI vs. Tableau:

Parameters	Power BI	Tableau
Year of establishment	2013	2003
Application	Complete Dashboards for analysis	Allow Ad Hoc Analysis
Use by	Technical or Non Technical users	Only use by Analysts
Support	Very limited	Full support
Scalability	Good	Excellent
Infrastructure	SaaS	Flexible

Who uses Power BI?

Here, are an important professional who uses Power BI tool:

PMO – Project and Portfolio Manager
Business & Data Analyst
Developer & Database Administrator
IT Team, IT Professional
Consumer for End User Report
Data Scientist

Advantages of Power BI

Here are the advantages of using Power BI:

Offers pre-built dashboards and reports for SaaS Solutions
Provide real-time dashboard updates.
Secure and reliable connection to your data sources in the cloud or on-premises
Power BI offers quick deployment, hybrid configuration, and a secure environment.
Data exploration using natural language query.
Feature for dashboard visualization
New features frequently added that are great for excel users.
Extensive database connectivity capabilities Q&A feature publish to the web.
integration with both Python and R coding to use visualizations.
Power Query provides many options related to wrangling and clean the data.
Post publishing the data into Power BI web service can schedule refresh without manual intervention.
Power BI backed by the superpower of with artificial intelligence and machine learning.

Disadvantages of Power BI

Here, are Cons/drawbacks of using Power BI:

Dashboards and reports only shared with users having the same email domains.
Power Bl will not mix imported data, which is accessed from real-time connections.
Power Bi will not accept the file larger than 250MB and zip file which compressed by the data of X-velocity in-memory database.
Power BI can’t accept file size larger than 1 GB.
Dashboards never accept or pass user, account, or other entity parameters.
Very few data sources that permit real-time connections to Power BI reports and dashboard.