Data Analytics

Using
Microsoft Power BI

Lecturer: Nguyen Chi Cuong

golive.isb.edu.vn
 OBJECTIVES

Understand the fundamentals of Business Intelligence

(BI) and MS Power BI

Learn data organization techniques for efficient

analysis

Practice using Power BI Desktop

golive.isb.edu.vn 2
 TAKE AWAY

Basic knowledge of
MS Power BI for
self-learning

Data flow and

related components

Sample Dashboard

golive.isb.edu.vn 3
 AGENDA

Step 01 Step 03 Step 05 Step 07

Getting started Clean, transform Creating measures Work with Power BI
with Power BI and load data using DAX visuals

01 02 03 04 05 06 07 08

Step 02 Step 04 Step 06 Step 08

Get data in Power BI Design data model Optimize data model Create a data-
for performance driven story

golive.isb.edu.vn 4
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 5
 1.1 Business Intelligence Introduction

Technology
• Cloud or On-premises
Process • Integration Application
• Collect
• Aggregation, preparation
• Extract, Load & Transfrom
• Analysis
• Presentation

Decision
making

golive.isb.edu.vn 6
 1.2. BI Components
Data Sources Storage Presentation

ERP

Data Lake Analysis

CRM ETL

Files
Data Warehouse Dashboard

Media
Datamart KPIs

golive.isb.edu.vn 7
 1.4. Microsoft Power BI Introduction

• Collection of software services, apps, and connectors

• Turn unrelated sources of data into coherent, visually immersive, and interactive insights
• Easily connect datasources, visualize, and share.

golive.isb.edu.vn 8
 1.5. Parts of Power BI

Power BI Desktop Power BI Service Mobile Power BI apps

Microsoft Windows Online SaaS Available on any devices
desktop application (Software as a Service) Windows, iOS, and Android

Common flow of activity:

1. Bring data into Power BI Desktop, and create
a report.
2. Publish to the Power BI service (can create
new visualizations or build dashboards).
3. Share dashboards with others
4. View and interact with shared dashboards and
reports in Power BI Mobile apps.

golive.isb.edu.vn 9
 1.6. Building blocks of Power BI (1)

Building blocks are what you have to build/create in Power BI

Dataset Visualization Reports Dashboard

Collections of data (Visual) Collections of visuals Collections of reports
Presentation of data & visuals

golive.isb.edu.vn 10
 1.6. Building blocks of Power BI (2)

Dataset

Dashboard

Visuals/ Tile Reports

golive.isb.edu.vn 11
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 12
 2.1. Power BI Desktop Introduction (1)
1. Ribbon: displays
common tasks
2. Report view, or canvas:
1 create and arrange
visuals. Include: Report
View, Data View and
View mode Model View
3. Pages tab: select or add
a report page
4. Filters: limit data show
2 4 5 6 on the report page
5. Visualizations: add or
change visuals
6. Fields: cover data, can
be dragged onto the
3 Report View or dragged to
the Filters

golive.isb.edu.vn 13
 2.1. Power BI Desktop Introduction (2)

1. Data View: show and

manipulate data
2. Model View: set up data
model

golive.isb.edu.vn 14
 2.2. Data structure and Power BI Connector

Structured data Semi-structure data Unstructured data

File

Database/
Cloud/
Online
Services

golive.isb.edu.vn 15
 2.3. Get data from file(s) (1)
1. In Power BI Desktop → Home tab → Select
Get data
1
2. In the list that displays, select the option
Excel or Text/CSV
3. Select the file location then open
2

Connector 3

golive.isb.edu.vn 16
 2.3. Get data from file(s) (2)

4. Select the check box(es) of the table(s),

then select Load or Transform data
5. Select Load to automatically load data into
the Power BI model 4

6. Select Transform Data to launch the

Power Query Editor, where to review and
clean data before loading it into the Power
BI Model

5 6

golive.isb.edu.vn 17
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 18
 3.1. Power Query Editor Introduction
1. To begin, select Transform data
from the Navigator window to
launch Power Query Editor
2. Ribbon – active buttons enable 1
to interact with the data in the
query
3. Table, entity – available for
selecting, viewing and shaping 2

4. Data from selected query is

displayed and available for
shaping
5. The Query Settings window 5
lists the query’s properties and 3 4
applied steps

golive.isb.edu.vn 19
 3.2. Shape the initial data (1)
Identify column headers and names

The first step in shaping your initial data is to identify the column headers and names within the data and then evaluate
where they are located to ensure that they are in the right place.

In the following screenshot, data did not import as expected. Several columns have undescriptive names, such as
Column1, Column2

golive.isb.edu.vn 20
 3.2. Shape the initial data (2)

Promote headers
• When a table is created in Power BI Desktop, Power Query Editor assumes that all data belongs in table rows.
However, a data source might have a first row that contains column names, which is what happened in the previous
SalesTarget example. To correct this inaccuracy, you need to promote the first table row into column headers.
We can promote headers in two ways:
1. by selecting the Use First Row as Headers option on the Home tab
2. by selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.

golive.isb.edu.vn 21
 3.2. Shape the initial data (3)
Remove top rows

1. When shaping your data, you might need to remove some of the top rows, for example, if they are blank or if they
contain data that you do not need in your reports.

2. To remove these excess rows, select Remove Rows → Remove Top Rows on the Home tab.

golive.isb.edu.vn 22
 3.2. Shape the initial data (4)
Remove columns

A key step in the data shaping process is to remove

unnecessary columns. We can remove columns in two
ways:
1. The first method is to select the columns that you want 1
to remove and then, on the Home tab, select Remove
Columns 2
2. Alternatively, you can select the columns that
you want to keep, select Remove
Columns → Remove Other Columns.

golive.isb.edu.vn 23
 3.2. Shape the initial data (5)
Unpivot columns

Unpivoting is a useful feature

of Power BI. You can use this
feature with data from any
data source, but you would
most often use it when
importing data from Excel
1. Select the column to
unpivot. In the Ribbon,
Transform → Unpivot
Columns

golive.isb.edu.vn 24
 3.2. Shape the initial data (6)
Pivot columns

Pivot Column feature to convert your flat data into a table that contains an aggregate value for each unique value in a
column. For example, you might want to use this feature to summarize data by using different math functions such
as Count, Minimum, Maximum, Median, Average, or Sum.
1. On the Transform tab, select Transform → Pivot Columns. Then choose the values column and aggregation
function (count, sum, average, etc.).

golive.isb.edu.vn 25
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 26
 4.1. Data model (1)
Data modeling - Star schema
Data modeling is the process of analyzing and defining all the different data types your business collects
and produces, as well as the relationships between those bits of data
Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers
to classify their model tables as either dimension or fact.

golive.isb.edu.vn 27
 4.1. Data model (2)
Dimension and Fact
• Dimension tables describe business entities - the things you model. Entities can include products, people,
places, and concepts including time itself.
• Fact tables store observations or events, and can be sales orders, stock balances, exchange rates,
temperatures, etc. A fact table contains dimension key columns that relate to dimension tables, and numeric
measure columns.
• Dimension tables support filtering and grouping
• Fact tables support summarization

Measure
• In Star schema design, a measure is a fact table
column that stores values to be summarized
• Measure expressions often leverage DAX (Data
Analysis Express) aggregation functions like SUM,
MIN, MAX, AVERAGE, etc. to produce a scalar value
result at query time (values are never stored in the
model).

golive.isb.edu.vn 28
 4.2. Work with table (1)
Configure data model and build relationships between tables

1. To manage these relationships, open Model View then go to Manage Relationships on the ribbon.

• In this view, you can create, edit, and delete

relationships between tables and also
autodetect relationships that already exist.
• When you load your data into Power BI,
the Autodetect feature will help you
establish relationships between columns
that are named similarly.

golive.isb.edu.vn 29
 4.2. Work with table (2)
Configure table and column properties
The Model View in Power BI desktop provides many options within the column properties that you can view or update.

Under the General tab, you can: Under the Advanced tab, you
• Edit the name and description can:
of the column. • Choose the storage mode
• Add synonyms that can be
used to identify the column
when you are using the Q&A
feature.
• Add a column into a folder to
further organize the table
structure.
• Hide or show the column.

golive.isb.edu.vn 30
 4.3. Work with dimension (1)

Hierarchies
A Power BI hierarchy is a collection of related fields within a
dataset, arranged in a way that displays one element ranked over
the other fields. By using hierarchies, end-users can easily drill
down into the data and have a quick overview and a better
understanding.

To create a hierarchy, go to the Fields pane on Power BI and

then right-click the column that you want the hierarchy for.
Select New hierarchy, as shown in the following figure.

golive.isb.edu.vn 31
 4.3. Work with dimension (2)

Hierarchies (Cont.)
Next, drag and drop the subcategory column into this new
hierarchy that you've created. This column will be added as a
sublevel on the hierarchy

Now, you can build the visual by selecting a stacked bar chart in the
Visualizations pane. Add your Category Name Hierarchy in
the Axis field and Total Sales in the Values field.

golive.isb.edu.vn 32
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Work with Power BI visuals

7 Create a data-driven story

golive.isb.edu.vn 33
 5.1. Introduction to DAX
Data Analysis Expressions (DAX)
A programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and
custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to
calculate and return one or more values.

Calculated Colum
In Power BI, a calculated column is a column that is added to a table within the data model. Calculated columns are
typically used for creating new attributes or performing row-level calculations, such as concatenating strings,
performing arithmetic operations, or applying logical conditions.
1
1. Create the new column by selecting the ellipsis (...) button on the
table in the Fields list and then selecting New column.
2

Measure
A measure in Power BI is a calculation that performs aggregations,
summarizations, or complex calculations on data. Measures are used in
visualizations to provide aggregated results across multiple rows or tables.

2. Create the new column by selecting the ellipsis (...) button on the
table in the Fields list and then selecting New measure.
golive.isb.edu.vn 34
 5.2. DAX function group

Maths & Statistical Functions Relationship Functions

Date & Time Functions Filter Functions

Logical Functions Text Functions

Time Intelligence Functions Information Functions

golive.isb.edu.vn 35
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 36
 6. Optimize data model for performance

Analyze performance Control cache

Optimize

Optimize DAX Manage aggregation

golive.isb.edu.vn 37
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 38
 7.1. Add visualization items to report (1)

Two ways to create a new visualization in Power BI Desktop

1. Drag field names from the Fields pane and then drop 2
them on the report canvas. By default, your visualization
appears as a table of data. 1
2. Select Chart in Visualizations pane to change chart type

3. In the Visualizations pane, select the type of visualization

that you want to create. With this method, the default visual is
a blank placeholder that resembles the type of visual that you
selected.

golive.isb.edu.vn 39
7.1. Add visualization items to report (2)

While the visual is selected, you can change

the visualization type by selecting a different
visual from the Visualizations pane.

golive.isb.edu.vn 40
 7.2. Choose an effective visualization (1)
It is important that you choose an effective visualization to ensure that you display the data in the best way possible.

Table Matrix

• Use a table visual when you want to display data in a tabular • Use a matrix visual when you want to summarize and
format, similar to a spreadsheet. group data based on multiple dimensions.
• Tables are suitable for presenting detailed information with • You can use a matrix to create hierarchies and
individual rows and columns. aggregate data at different levels, such as year, quarter,
and month.
golive.isb.edu.vn 41
 7.2. Choose an effective visualization (2)
Bar and column charts

• Use a bar or column chart when

you want to compare data across
different categories or groups.
• Or you want to compare data
within a single category or group.
• Particularly useful for displaying
data with longer category labels
or when you want to emphasize
the differences between
categories
• Display numerical data and show
comparisons or trends within a
single category or over time

golive.isb.edu.vn 42
 7.2. Choose an effective visualization (3)
Line and area charts

• Use a line chart to visualize trends and

changes over time or continuous data
points.
• Use an area chart when you want to
emphasize the magnitude and compare
the distribution or proportion of different
categories over time.

golive.isb.edu.vn 43
 7.2. Choose an effective visualization (4)
Pie chart, donut chart, and Treemaps

• Use a pie chart to show the proportion or percentage distribution of categories within a whole.
• Use a donut chart when you want the same distribution but with additional space for annotations.
• Use a treemap to visualize hierarchical or nested data with multiple categories and subcategories, proportions
between each part and the whole.

golive.isb.edu.vn 44
 6.2. Choose an effective visualization (5)
Card visualization
• The card visualization displays a single value: a single data point
• Ideal for visualizing important statistics: Total value, YTD sales, or year-over-year change

The multi-row card visualization displays one or

more data points, with one data point for each row.

golive.isb.edu.vn 45
 7.3. Format and configure visualizations (1)
Format

In this example, you will format and configure the

default clustered column chart visualization to
better meet the needs of your report
requirements.

Start by selecting the visualization on the

canvas, and then select the Format button (paint
roller icon) to display the Format pane.

golive.isb.edu.vn 46
 7.3. Format and configure visualizations (2)
Title

You can edit a default title and add a title, if

you don't have one. In this example, you
will select the column chart visualization
and then, in the Format pane, scroll down
and expand the Title section. Edit the
current title by adding a space between
Sales and Amount, and then increase the
font size to 16 points.

golive.isb.edu.vn 47
 7.3. Format and configure visualizations (3)
Background

It is best practice to keep the default white

background so the presented data can be
clearly seen. However, you can change the
default background color to make a
visualization more colorful and easier to
read or to match a particular color scheme.
In this example, continue with the column
chart that is selected and then, in the
Format pane, expand the Background
section and change the color to light grey.

golive.isb.edu.vn 48
1 Business Intelligence and Microsoft Power BI Overview

2 Get data in Power BI

3 Extract, transform and load data

4 Design data model

5 Creating measures using DAX

6 Optimize data model for performance

7 Work with Power BI visuals

8 Create a data-driven story

golive.isb.edu.vn 49
 8.1. Dashboard type

Strategic dashboards Operational dashboards Analytic dashboards

focuses on long-term tracks short-term consists of the datasets
goals and strategies at performance and and the mathematics
the highest level of intermediate goals used in these sets
metrics

golive.isb.edu.vn 50
 8.2. Design a report layout (1)
Report design best practice
• Draw a sketch of the report layout before designing it to visualize different ideas.
• Highlight important information using bright colors or summary icons to draw attention.
• Choose a background that suits the context of the report, such as a white background for
a clean and professional look or a black background to emphasize colorful highlights.
• Consider using images as a background to add visual interest to the report.

Report page

• The default display view is Fit to page, which means that the contents are scaled to best fit
the page. If you need to change this view, go to the View tab, select Page view, and then
select your preferred page view option, as illustrated in the following screenshot.
• To access the page settings, select the white space on your report canvas to open the
Format pane. You can then configure the following settings to suit your needs: Page
information, Page alignment, Page size, Wallpaper, Page background, and Filter pane.
golive.isb.edu.vn 51
 8.2. Design a report layout (2)
Number of visuals
• More visuals might make your report look too busy, causing users to feel overwhelmed
• The fewer visuals you use, the better the performance will be.

Position of visuals
• Use CTRL+click to select all visuals that you want to align, select
the Format tab, and then select Distribute horizontally.

Interaction of visuals
• The visuals that you add to your
report will interact with each other.
• Select visuals you want to set
interactions. Go to Format, then
Edit interactions

golive.isb.edu.vn 52
 8.3. Use interactions (1)
In Power BI Desktop, your report is dynamic. When you make a selection on one visual in the report, other visuals might
change to reflect that selection.

View interactions
When multiple visuals exist on
the same report page, they all
interact with each other. For
that reason, you should
become familiar with these
interactions to see how your
report changes.
Compare the following two
images. In the first image, the
data displays at a high level.

golive.isb.edu.vn 53
 8.3. Use interactions (2)
View interactions (cont.)

When you select an element in a visual,

such as Components in the Product
Category visual, the other visuals
update to reflect your selection, as
illustrated in the second image.

When you have become familiar with

the interactions, you might want to
make changes to control how those
interactions flow between the visuals.
This process is further explained later in
this module.

golive.isb.edu.vn 54
 8.3. Use interactions (3)
View interactions (cont.)

To change an interaction of a selected

visual, select the required interaction
icon. Remember, the applied interaction
is displayed in bold. The following
image shows that:
• The selected interaction is cross-
filtered because the Filter icon is
displayed in bold.
• You can change the interaction to
cross-highlight by selecting the
Highlight icon.
• You can remove the interaction
altogether by selecting
the None icon.

golive.isb.edu.vn 55
 8.4. Slicing
Add a slicer
A slicer is a type of filter that you can add to your report, so users can segment the data in the report by a specific
value, such as by year or geographical location. Slicers narrow the portion of the dataset that is shown in the other
report visualizations.

1. To apply a slicer, select

the Slicer icon in the Visualizations
pane. Then, in the Fields pane,
select the fields that you want to
include in the slicer or drag them into
the slicer visualization.
2. The visualization then changes to a
list of items (filters) with check boxes
that you can use to segment the
data. When you select an item's
check box, Power BI will filter (slice)
all the other visualizations on the
same report page, as illustrated in
the following image.

golive.isb.edu.vn 56
THANK YOU

golive.isb.edu.vn 57
 Go-live for Success - Tư vấn
và đào tạo chuyển đổi số
Định hướng chiến lược và
thành thạo các công cụ công
nghệ để chuẩn bị hành trang
trong thời đại 4.0

17 Phạm Ngọc Thạch, Phường Võ Thị Sáu, Quận 0909 536 709 consulting@isb.edu.vn golive.isb.edu.vn
3, TP.HCM
golive.isb.edu.vn