Module 4 – Data Modelling
Copyright Intellipaat. All rights reserved.
Agenda
What are
01 Relationships? 02 Creating Relationships
03 Cardinality 04 Cross Filter Direction
05 What is DAX? 06 Why DAX?
Copyright Intellipaat. All rights reserved.
Agenda
07 DAX Syntax 08 DAX Functions
09 Context in DAX 10 Calculated Columns
11 Calculated Tables 12 Measures
Copyright Intellipaat. All rights reserved.
What are
Relationships?
Copyright Intellipaat. All rights reserved.
What are Relationships?
Relationships are used to join tables together so that we can work with them as if they are one
Calendar Customers
Index
Customer Name
Date
Day Customer ID
Month
Sales
Order Date
Ship Date
Sales ID
Customer ID
Relationships in Power BI are much similar to the relationships we create in relational databases, such as
Microsoft SQL Server®, or data warehouse databases, such as SQL Server Analysis Services (SSAS)
Copyright Intellipaat. All rights reserved.
What are Relationships?
Relationships in a relational database/OLTP, a data warehouse or Power BI are a part of normalization
Benefits of using relationships in a table are:
Reduces Redundant data
Benefits It refines table structures and
minimizes redundant data
Establishes a Connection
It establishes a connection between
a pair of tables that are logically
related to each other
Copyright Intellipaat. All rights reserved.
Creating Relationships
Copyright Intellipaat. All rights reserved.
Creating Relationships
There are two ways of creating a relationship in Power BI:
When data is imported into the
model, Power BI automatically creates
Creating relationships. If we then create
Relationships calculated tables or use Enter Data to
Using add new tables, relationships will not
Autodetect exist. However, Autodetect is the best
guess, and it might need adjusting
after it runs
The easiest way to create a
relationship between two tables is to
Creating
drag a column from the first table to
the related column in the second table
Relationships
where we want to join. If the data is Manually
valid for creating a relationship, the
columns will be connected
Copyright Intellipaat. All rights reserved.
Cardinality
Copyright Intellipaat. All rights reserved.
Cardinality
In data modeling, cardinality refers to the relationship that one table has with another table
There are three types of cardinality in Power BI data modeling:
Many to One (*:1)
Many to one means that one
table can have more than one
instance of the value used in
the column to join to the other
table. The other table would
have only one value. This is the
most commonly used type in
One to Many (1:*) Types of Power BI
This is the same as many to one, Cardinality
except that the position of the
tables is reversed in this
relationship type
One to One (1:1)
In a one-to-one relationship,
both tables in the relationship
have one instance of the value
Copyright Intellipaat. All rights reserved.
Cardinality
Many to One (*:1) One to One (1:1)
Employee Additional
Sales Customer Employee Details
Customer ID Employee ID
1 1
Customer ID * Employee ID 1
One to Many (1:*)
Customer Sales
* Customer ID
Customer ID 1
Copyright Intellipaat. All rights reserved.
Cross Filter Direction
Copyright Intellipaat. All rights reserved.
Cross Filter Direction
The cross filter direction of the relationships in our dataset decides how Power BI has to treat the tables in
visualizations in our reports
Both
Both is the most common and the default. When we apply
Types filtering, the two tables are considered as one for
aggregating the data in a visualization
When we manually create a
Single
relationship, or the Autodetect feature
When we apply a single cross filter direction, the
filters in related tables operate on the table
generates the relationship for us,
where the values are aggregated Power BI makes the best guess at the
cross filter direction
Copyright Intellipaat. All rights reserved.
Demo: Working with
Relationships
Copyright Intellipaat. All rights reserved.
What is DAX?
Copyright Intellipaat. All rights reserved.
What is DAX?
Data Analysis Expressions (DAX) is a formula language that consists of
a library of more than 200 functions, constants, and operators
Power BI
DAX is used in a formula or expression to calculate and return a single
DAX value or multiple values
DAX is not a new feature. It is used in Power Pivot for Excel or SQL
Server Analysis Services (SSAS)
Copyright Intellipaat. All rights reserved.
Why DAX?
Copyright Intellipaat. All rights reserved.
Why DAX?
01 Using DAX in a effective way will help us get the
most of our data
02 Measures created using DAX help us solve real-
world problems easily
DAX helps us perform calculations over large data
03 easily to get valuable insights to make it more
useful
Copyright Intellipaat. All rights reserved.
Why DAX?
DAX helps us find the insights that we want to extract from our data to make it more useful
For example, suppose we want to compare the sales of
this year so far, like-for-like with the last year. If the
current month is May, we only want to compare till
May of the previous year
Last Year Sales = CALCULATE ([Total Sales],
SAMEPERIODLASTYEAR('Date'[Date]))
Copyright Intellipaat. All rights reserved.
DAX Syntax
Copyright Intellipaat. All rights reserved.
DAX Syntax
The key for understanding and using DAX is learning the syntax for structuring formulas, the functions
that we use to make calculations, and the context
DAX formula syntax includes various elements that make up a formula
A. The measure name
A B. The equal sign operator, which indicates the
D
beginning of the formula
TotalSales = SUM(Sales[SalesAmount]) C. The DAX function, SUM
D. Parentheses(), which surround an expression that
E contains one or more arguments
B C F
E. The referenced table (Sales)
F. The referenced column (SalesAmount) in the
referenced table
Copyright Intellipaat. All rights reserved.
DAX Functions
Copyright Intellipaat. All rights reserved.
DAX Functions
DAX functions are predefined formulas that perform
calculations on one or more arguments
1
We can pass a column, function, expressions, formulas, constants,
2 numbers, text, and TRUE or FALSE as an argument
3 DAX is similar to Excel, but DAX refers to an entire column or a
table. We can filter to refer to a particular value
Copyright Intellipaat. All rights reserved.
DAX Functions
The DAX library of more than 200 functions, operators, and constructs is segmented into the
following 10 categories:
Date and Time Time Intelligence Filter Information Logical
Math and Trig Parent-Child Statistical Text Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
The Date and Time DAX function is similar to the date and
Filter time functions used in Excel, but it is based on the datetime
data types used by Microsoft SQL Server
Information
Logical
DAY EOMONTH
Math and Trig
DATEDIFF NOW
Parent-Child
Statistical YEAR WEEKDAY
Text
WEEKNUM
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
Using Time Intelligence functions, we can create date and
Filter time ranges combined with aggregations, and it is useful for
building comparisons across time periods
Information
Logical NEXTYEAR
Math and Trig
NEXTQUARTER TOTALYTD
Parent-Child
Statistical DATEADD PREVIOUSMONTH
Text
CLOSINGBALANCEMONTH
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
Using Filter functions, we can get specific data types, look
Filter up values in related tables, or filter by related values
Information
Logical RELATED
Math and Trig
CALCULATE ISFILTERED
Parent-Child
Statistical FILTER VALUES
Text
RELATEDTABLE
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
Information functions evaluate a table or a column
Filter provided as an argument to another function and inform us
if the value matches the expected type
Information
Logical
ISTEXT
Math and Trig
ISBLANK ISEVEN
Parent-Child
Statistical ISERROR USERNAME
Text
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
The Logical function returns information about the value in
Filter our expression
Information
Logical IFERROR
Math and Trig
NOT
Parent-Child FALSE
Statistical
IF OR
Text
TRUE
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
The Math and Trig functions perform a wide variety of
Filter mathematical calculations, and they are similar to the
mathematical and trigonometrical functions in Excel
Information
ROUND
Logical
Math and Trig ABS DEGREES
Parent-Child
Statistical SQRT SUM
Text
FLOOR
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
The Parent-Child functions work on the data that is
Filter presented in a parent/child hierarchy in the data model
Information
Logical
PATHITEM
Math and Trig
Parent-Child
PATH PATHLENGTH
Statistical
Text PATHCONTAINS
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
Statistical functions are used to perform aggregations, and
Filter we can filter a column prior to aggregations
Information
Logical
MAX
Math and Trig
MIN COUNTBLANK
Parent-Child
Statistical COUNT ROW
Text COUNTROWS
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence Text functions operate on string values. We can use Text
functions for searching a text within a string, returning a
Filter substring, formatting dates, times, and numbers, and
concatenating strings
Information
Logical
SEARCH
Math and Trig
CONCATENATE LEN
Parent-Child
Statistical FIND REPLACE
Text TRIM
Others
Copyright Intellipaat. All rights reserved.
DAX Functions
Date and Time
Time Intelligence
These are some unique functions that do not fall into any of
Filter the other categories
Information
Logical UNION
Math and Trig
EXCEPT INTERSECT
Parent-Child
Statistical GROUPBY VAR
Text
Others
Copyright Intellipaat. All rights reserved.
Context in DAX
Copyright Intellipaat. All rights reserved.
Context in DAX
Context is an important concept to understand if we want to write expressions that return
the results we expect
Types
A row context is a formula that includes a function that
uses filters to identify a single row in a
1. table. The function applies a row context to each
Row Context
Row in the table to which the filter is applied
A filter context is one or more filters applied in a
2. calculation, which determine a single value or the Filter Context
result. We can use a filter context to reduce the
values that are included in a calculation
Copyright Intellipaat. All rights reserved.
Context in DAX
The following measure demonstrates how a row context and a filter context operate on a
calculation in the formula
&
Using Row Context and Filter UK Sales = CALCULATE([Total Sales],
Context in a Measure
Customers[Country] = "UK")
This formula uses Total Sales and applies a filter of UK so that only the sum of UK Sales will be
returned in the result
Copyright Intellipaat. All rights reserved.
Demo: Using Row
Context and Filter
Context
Copyright Intellipaat. All rights reserved.
Calculated Columns
Copyright Intellipaat. All rights reserved.
Calculated Columns
Calculated columns are added to tables by applying DAX formulas to the existing data
The DAX formula defines the values in the new column rather
than querying the data source to create the column
We can create calculated columns by concatenating strings or
multiple numbers together, combining the data from anywhere
in the model
Calculated columns are similar to measures, but the difference
lies in how they are used. Measures are used in the Values area
of a visualization, and Calculated columns are used in the Axis,
Legend, or Group fields
Copyright Intellipaat. All rights reserved.
Calculated Columns
There are two ways in which we can create calculated columns in a data model
This code does not include table names since the columns
exist within the same table, but it is a good practice to Creating a calculated
include the table name for clarity column using the existing
Full Name = [First Name] & “ “ & [Last Name] data
If we refer to a column in another table, then we must Creating a calculated
include the table name using the Related function column using the
Location= RELATED(Countries[Region]) & “, “ & [City] Related function
Copyright Intellipaat. All rights reserved.
Calculated Tables
Copyright Intellipaat. All rights reserved.
Calculated Tables
Like calculated columns, calculated tables are also created using the data that already exists in the model,
which uses a DAX formula to define the values
Calculated tables are created in both the Report view and the Data view in Power BI Desktop
Combine the existing table
1 using UNION
Create a calculated table using
2 NATURALINNERJOIN
Create a calculated table using
3 the DATATABLE function
Copyright Intellipaat. All rights reserved.
Measures
Copyright Intellipaat. All rights reserved.
Measures
Power BI measures help us discover insights into our
data that might otherwise be hidden
1
Measures use aggregating functions such as average,
Measures 2 minimum, maximum, count distinct, or more complex
calculations that use a DAX function
3 Measures are created using DAX formulas and with an
extensive library of functions, operators, and constructs
Copyright Intellipaat. All rights reserved.
Demo: Using Calculated
Columns and Measures
Copyright Intellipaat. All rights reserved.
India: +91-7847955955
US: 1-800-216-8930 (TOLL FREE)
sales@[Link]
24/7 Chat with Our Course Advisor
Copyright Intellipaat. All rights reserved.