Module 5

Lesson 1
Welcome to Lesson 1 of our Power BI module — we’re going to be diving into one of
the most important yet often overlooked aspects of building effective reports:
relationships.
Now, when I say relationships, I don’t mean anything emotional — although if your
data isn’t getting along, it might get dramatic! What we’re really talking about here
is how Power BI allows you to connect multiple tables of data so they can talk to
each other. So instead of working with one massive table that’s bloated and hard to
manage, we can break our data down into separate, logical tables — and then link
them together using relationships.
Throughout this session, we’re going to explore the following things: what
relationships are, how to view and create them, what cardinality means, what cross
filter direction is, and finally, we’ll walk through a demonstration where I’ll show you
how to view and build relationships right inside Power BI.
Let’s start by understanding what relationships actually are.

🔹 What Are Relationships?

So, imagine you have a sales report. You might have one table for customer
information, another for the orders they placed, another for the products being sold,
and maybe one more table for regions or locations. If you were working in Excel,
you'd probably use VLOOKUPs or INDEX/MATCH to pull values across sheets. In
Power BI, we do that more elegantly — through relationships.
Relationships join tables together so you can work with them as if they were a
single combined dataset. Behind the scenes, Power BI links them through keys —
just like how relational databases work. Most of these relationships are created in
systems called OLTP systems — Online Transaction Processing systems — where the
normalization process breaks data down into separate tables using keys. This
structure prevents repeated values and makes sure each entity (like a customer,
product, or order) only contains the information that belongs to it.
This idea of separation and linking keeps your data organized and clean. It’s
especially important in data warehouses where we commonly see fact tables —
which store transactional data like sales or orders — and dimension tables, which
store descriptive information like customer names or product categories. These
tables are linked using keys.
Power BI is smart enough to help us here. It has an Autodetect feature that tries to
recognize these relationships based on the data model — so when you import tables
that have matching column names, Power BI can automatically create those
relationships for you.
Let me pause here and ask: has anyone worked with relationships in Excel or
databases before? Feel free to share your experience in the chat. And if this is your
first time, no worries at all — you’ll get a solid grasp by the end of today’s session.
🔍 Viewing Relationships
Now, let’s move into how we can view relationships inside Power BI.
When you load data into Power BI, it doesn’t just sit there silently. Behind the
scenes, Power BI is running something called the Autodetect feature. It scans
through your tables, looks for columns with the same or similar names, and tries to
guess which tables should be linked — and how.
If it finds a likely match, it creates the relationship automatically. This means you
don’t always have to do it manually — although it’s always a good idea to double-
check. Along with creating relationships, Power BI also guesses the cardinality
(we’ll come to that shortly) and the cross filter direction.
So, how do you actually see these relationships? That brings us to the Model View
or Relationships View in Power BI.
Let’s say we’ve already imported our tables — one for orders, one for customers,
one for products. On the left-hand panel, we switch to the “Model” icon — the third
one down, right below the “Data” icon. When we click that, Power BI shows a visual
diagram of all our tables — and any relationships between them are represented by
lines connecting the columns.
You can click on any of those lines to inspect details like which fields are linked,
what the cardinality is, and the direction of the filter. You can even delete a
relationship here or create a new one by dragging a field from one table onto a
matching field in another.
Here’s a good tip: when Power BI finds more than one possible relationship
between two tables, it will set only one as active. Only the active relationship is
used in your visualizations and DAX calculations — so if something looks off in your
data, it might be due to the inactive relationship being the one you need.
Let’s take a quick group discussion here:
👉 Question: Why do you think Power BI only allows one active relationship between
two tables?
Sample Answer: Because if multiple relationships are active at the same time, it
could create ambiguity and incorrect aggregations. Power BI wouldn’t know which
path to follow in the calculations, which can mess up the visualizations.
Perfect. Let’s continue!

Creating Relationships
Now that we know how to view relationships, the next question is: how do we
create them?
As I mentioned earlier, Power BI might create relationships automatically using the
Autodetect feature — this happens right when we import the tables. It looks for
matching column names, usually IDs like "CustomerID" or "ProductID", and makes a
best guess at how they’re connected. It also tries to guess the cardinality and filter
direction.
But we don’t have to rely on that guess — we can create relationships manually
too.
To do this, we go to the Relationships View. Then, we click and drag a column from
one table — for example, the "CustomerID" column from the Orders table — onto
the corresponding "CustomerID" column in the Customers table. Power BI will
automatically suggest a relationship.
If you want more control, you can also click “Manage Relationships” on the ribbon,
and then click “New.” This opens a dialog box where you can choose the tables,
columns, cardinality, and cross filter direction manually.
Now, here’s something important: sometimes, Power BI won’t let you create a
relationship. This usually happens when the values don’t match up — maybe there
are empty or null values, or maybe you’ve got duplicates where you shouldn’t. If
you run into this, try cleaning your data first.
Let’s do a live demonstration together now:
Demonstration: Viewing Relationships in Power BI
1. Open Power BI and load two datasets: one for Orders and one for Customers.
2. Go to the Model view (click the third icon on the left).
3. You’ll notice Power BI has created a line between “Orders[CustomerID]” and
“Customers[CustomerID]”.
4. Click the line to view details about the relationship.
5. Now delete the line, and recreate it by dragging CustomerID from Orders onto
CustomerID in Customers.
6. Click “Manage Relationships,” select your new relationship, and click “Edit” to
view cardinality and filter direction.
Let me know in the chat if anyone needs help — we can troubleshoot together!

🧮 Cardinality
Okay, next big topic: Cardinality. This is a fancy data modeling term, but don’t let
it intimidate you — it simply refers to how the values in the two tables relate to
each other.
There are three types of cardinality in Power BI:
1. *Many to One ( :1 ) – This is the default and most common type. Imagine a
sales table where you have many sales records, and each one is linked to a
single customer. So many sales → one customer. This type is often used when
connecting fact tables to lookup tables like products, countries, or customer
names.
2. One to One (1:1) – This is less common, but still useful. For example, let’s
say you have a table of employees and a second table with one additional
detail per employee. Each record in one table matches exactly one record in
the other. No duplicates on either side.
3. One to Many (1:*) – This is just the reverse of the Many to One. For
example, a customer table where each customer has multiple orders in the
Orders table. This type is commonly used when the primary table is a
dimension table feeding into a fact table.
To see the cardinality of any relationship, just click on the relationship line in the
Model view — it will show you the type right there. If you ever see unexpected
results in your visuals — like totals looking off — it’s a good idea to double-check
whether the right cardinality is being used.
Let’s do a quick check-in — can anyone in the chat give me an example of a Many
to One relationship? Don’t worry about being perfect — just give it a try.
🔄 Cross Filter Direction
Alright, now let’s discuss Cross Filter Direction — this one trips up even
experienced users sometimes, but it’s super important for how data flows in your
reports.
When two tables are connected, filters can flow in one direction or both
directions, depending on how you set it up.
Power BI tries to guess the right direction — and usually, it gets it right. But let’s
break down the two options:
 Single Direction (→): Filters flow from the dimension table to the fact table.
This is the default when you're doing standard lookup-style reporting. For
example, selecting a country from a slicer filters the sales table to only show
relevant records.
 Both Directions (↔): Filters can flow in both directions between tables. This
is useful when you need to treat two tables as one, like in a star schema
model where you want full filtering between related dimension tables. Be
careful with this — it can cause performance issues and confusing results if
overused.
Let’s say we have a Customers table and a Sales table. In most cases, we want
filters to flow from Customers → Sales. If we select a customer name, it filters the
sales. But if the filter direction were reversed, we’d be trying to filter customer
names based on what’s in the sales data — and that might not make sense.

👨‍🏫 Final Demonstration Recap

Let’s wrap up today’s session with a recap of the demonstration steps:
 We imported multiple tables into Power BI.
 We explored the Relationships View to see what Power BI created
automatically.
 We manually created and edited relationships.
 We looked at the cardinality of each relationship.
 And finally, we explored cross filter direction and saw how it affects filters in
visuals.

Lesson 2
Lesson 2—we’ll be diving into a really powerful part of Power BI: DAX, which stands
for Data Analysis Expressions. This is going to be an important turning point in your
Power BI journey because from this point on, you’ll not only be visualizing data—
you’ll be calculating with it, customizing it, and extracting deep insights that go way
beyond what the raw data alone can tell you.
So here’s what we’ll be focusing on today:
We’ll begin by answering the basic question—What is DAX?
Then we’ll move on to understanding the syntax and structure of DAX formulas.
We’ll explore the types of functions DAX offers.
We’ll look at two fundamental ideas in DAX: Row Context and Filter Context.
And then we’ll put everything together in a demonstration, showing you how
context impacts your calculations.
Finally, we’ll take a look at DAX formulas you’ll frequently use and where to apply
them.
Let’s jump right in.

🧩 What is DAX?
So, what exactly is DAX? Think of DAX as the language that allows you to talk to
your data in a meaningful way. DAX stands for Data Analysis Expressions, and
it’s a formula language used throughout Power BI, Power Pivot in Excel, and even
SQL Server Analysis Services.
Now, DAX is not just a Power BI thing. It’s been around—especially for Excel users
who’ve used Power Pivot or worked with tabular data models. But in Power BI, it
becomes extremely powerful because it’s fully integrated with relational data
models and visualizations.
Here’s how I want you to think about DAX: it’s a way to write formulas that
calculate new information from your data. That could be a single number like
total sales, or more advanced logic like calculating percentage growth, running
totals, or comparing this month’s sales to last month’s.
DAX includes a library of over 200 functions, constants, and operators—and
the best part is, many of them resemble Excel functions, which I know many of you
are familiar with. But what makes DAX special is its ability to understand
relationships in your model, work with filters, and calculate results across different
tables intelligently.
Let’s take a moment here.
Discussion Prompt:
"Can anyone share a scenario where they’d want to calculate something that isn’t
directly in the table?"
(Sample student responses: calculating profit margins, finding monthly
growth, calculating tax on total sales, etc.)
Exactly! That’s where DAX steps in. Whether it's year-over-year sales, running
totals, or like-for-like comparisons, DAX is the engine behind the calculations in
Power BI.

✍️Understanding DAX Syntax

Now, once we know what DAX is, the next big thing is learning how to write DAX
formulas correctly. DAX, just like any other language, has rules—this is called
syntax.
Let’s break down the anatomy of a DAX formula.
When writing a DAX formula, it always starts with the name of what you're creating
—either a measure or a calculated column. That’s followed by an equal sign
(=), which works much like it does in Excel. Whatever is to the right of the equals
sign is the formula or calculation logic itself.
So it looks like this:
Total Sales = SUM(Sales[Amount])
Here, "Total Sales" is the name of the measure. We use the SUM function to add up
values from the column called Amount in the Sales table.
Now, DAX is very particular about naming conventions. If you reference a column,
it must go inside square brackets, like [Amount]. If you’re referencing a table, it
needs to be in single quotes if the table name has spaces, like 'Sales Table'.
You also need to make sure functions include arguments—at least one. And
arguments are passed inside parentheses (). So every function you use must be
formatted correctly with its inputs.
Another important thing: Measures are calculated in the context of your report or
model. If you move that measure to another page, or use different slicers, the result
may change—and that’s not a bug. That’s DAX working as intended, and we’ll
explore that when we discuss context.

⚙️DAX Functions
Let’s talk about functions. DAX functions are predefined formulas that perform
operations on one or more inputs—called arguments.
What can you pass into a DAX function? You can pass:
 Columns
 Text
 Numbers
 TRUE/FALSE values
 Even other formulas
There are over 200 functions in DAX and they’re organized into categories:
 Date & Time
 Time Intelligence (like YTD, MTD, QTD)
 Filter functions (like CALCULATE, FILTER)
 Logical functions (like IF, AND, OR)
 Math & Trig
 Text
 Parent & Child
 Statistical functions
 And many more
Now if you’re thinking, “This sounds a lot like Excel,” you’re not wrong. Many DAX
functions behave similarly to Excel ones—but there’s a key difference. In DAX,
you’re often working with entire columns or tables, not just individual cells.
For example, if you use SUM(Sales[Amount]), it’s summing up a whole column. And
if you apply a filter, the function will automatically respect that filter context.
Important Note:
Some DAX functions return tables, not values. When that happens, you can’t just
show the result directly in a visual, because visuals display values—not tables.
Those functions are usually used as inputs into other functions, like when you wrap
a FILTER inside a CALCULATE.
And here’s something interesting—DAX doesn’t need VLOOKUP. That’s right. Power
BI uses relationships between tables, so you don’t have to write VLOOKUP to
bring in data from another table. DAX handles this beautifully through its model
engine.

🧠 Understanding Context in DAX

This brings us to one of the most powerful—and sometimes confusing—concepts in
DAX: Context.
DAX uses two kinds of context when calculating formulas:
1. Row Context
2. Filter Context
Let’s understand both.
Row Context is all about the current row being evaluated. Think of it as DAX
going row by row when it performs a calculation. So if you have a calculated column
like:
Total = Sales[Quantity] * Sales[Price]
DAX looks at each row’s quantity and price, multiplies them, and returns a result for
each row. That’s row context.
Now comes Filter Context, which is even more powerful. This context is added
when you use filters or slicers in your visuals. For example, if you use a bar chart
showing Sales by Country, each bar is filtered to only show data for that country. So
when you drop a measure like SUM(Sales[Amount]) into that chart, it gets
calculated in the filter context of that country.
So filter context determines which data is included in a calculation, based on
visual filters, slicers, or even DAX expressions.
And yes—they can work together. Often, your measures are evaluated in both row
and filter context at the same time.

👨‍💻 Demonstration: Row vs. Filter Context

Alright, time to make this real with a demonstration. Please open up Power BI
Desktop and follow along with me. I’ll walk you through step by step.
Step 1: Import a sample dataset. You can use the basic Sales table with columns:
Product, Category, Quantity, Unit Price, and Region.
Step 2: Create a new calculated column by clicking on the table and selecting “New
Column.”
Type:
Total = Sales[Quantity] * Sales[Unit Price]
Notice how this returns a value for each row—that’s row context in action.
Step 3: Now, create a measure instead. Click “New Measure” and type:
Total Sales = SUM(Sales[Total])
Drag this measure into a visual—say, a pie chart—and add Region as the category.
You’ll now see the measure being calculated differently for each region. That’s
filter context.
Step 4: Try slicing the visual by Category or Product. Watch how the values update
—again, that’s DAX using filter context to refine its calculation.
Ask your students:
"What’s the difference between the calculated column and the measure in
this case?"
Expected answer:
Calculated column works row by row (row context), while the measure reacts to the
filter applied (filter context).

🧮 Writing Useful DAX Formulas

Let’s wrap up with a few DAX formulas you’ll commonly use:
 SUM(Sales[Amount]): Adds up values
 CALCULATE(SUM(Sales[Amount]), Region = "East"): Changes filter context
 IF(Sales[Amount] > 1000, "High", "Low"): Conditional logic
 RELATED(Products[Category]): Brings related column into another table
 TOTALYTD(SUM(Sales[Amount]), Date[Date]): Time intelligence
You’ll be using these a lot in dashboards and reports, especially when doing deeper
analysis.
Summary:
In today's session, we explored the fundamentals of DAX, or Data Analysis
Expressions, which is the formula language used in Power BI to create powerful
calculations and custom insights. We began by understanding what DAX is and how
it builds on Excel-like functions but works across entire tables and data models. We
discussed the correct syntax for writing DAX formulas, looked at common function
types like SUM, CALCULATE, and IF, and explored how DAX handles logic differently
through concepts like row context and filter context. Through a hands-on
demonstration, we saw how calculated columns operate row by row while measures
respond dynamically to filters in visuals. We wrapped up by applying DAX in
practical scenarios and emphasizing why understanding context is critical for
accurate analysis.

Lesson 3
Next, we’re going to explore Calculated Columns, Calculated Tables, and
Measures in Power BI. These are the building blocks of dynamic, custom reporting,
and they give you the power to create tailored insights directly within your data
model.
So, grab your notebooks, and please have Power BI open and ready, because we’ll
be walking through live demonstrations together. Also, I encourage you to ask
questions in the chat, and I’ll pause after each section for quick group discussion.
Let’s get started!

Part 1: What Are Calculated Columns?

Let’s begin by talking about Calculated Columns. Now, imagine you have a
dataset in Power BI – maybe a simple table of products with columns like Product
Name, Category, and Price. But now you want to create a new column that
calculates the price including tax. The source data doesn’t contain this column, and
you don’t want to go back to Excel to change it. That’s where a calculated column
comes in.
A calculated column is one that you create inside Power BI using a DAX formula.
You’re not querying the source again; instead, you're telling Power BI, "Hey, create a
new column using the data that's already in the model." That’s a big distinction. It’s
a way of enhancing your data model without touching the raw data.
Let me give you a few examples. You could use a calculated column to concatenate
first and last names into one full name. You could also use it to calculate the number
of days between an order date and ship date. You can even combine logic, like
adding a column that categorizes sales into “High”, “Medium”, or “Low” tiers based
on thresholds you define.
Let’s do a quick demonstration together. Follow along with me.

Demonstration: Creating a Calculated Column

 1. Open Power BI Desktop and load a sample dataset. I’ll be using the “Sales”
data from the sample workbook.
2. Go to the Data View on the left pane – that’s the one with the table icon.
3. On the top ribbon, click on the Modeling tab, then select New Column.
4. In the formula bar, type:
DAX
CopyEdit
TotalPrice = Sales[UnitPrice] * Sales[Quantity]
5. Press Enter. Just like that, we’ve added a new column to the table that
calculates the total price per transaction.
Notice that this column now behaves just like any other column. You can use it in
visuals, sort by it, filter it – anything.
Group Discussion Prompt:
"When might you prefer using a calculated column rather than adding a
new column in the original Excel or SQL file?"
Possible answers: When you don’t have access to modify the source, when you
need flexibility, or when you want to create context-aware columns within your
model.

Part 2: Calculated Tables

Alright, moving on – let’s now look at Calculated Tables. This might feel a bit
abstract at first, so let me simplify it.
Imagine you want to create a summary table – maybe a list of all the distinct
customers who purchased something in the last month. Your source doesn’t have a
table like that. So, you can use a DAX formula to create a new table from the data
you already have.
Think of it this way: instead of importing another table, you’re creating one from the
relationships and filters you define using DAX.
Here’s the key difference: with a calculated column, you're adding to an existing
table. With a calculated table, you're creating an entirely new one from scratch
using a DAX expression.
Let’s walk through an example together.

Demonstration: Creating a Calculated Table

1. Go to the Modeling tab, and click New Table.
 2. In the formula bar, let’s type:
DAX
CopyEdit
ProductSummary = DISTINCT(Sales[ProductName])
3. Press Enter. You’ll now see a new table called "ProductSummary" in the Fields
pane.
We’ve just created a list of all unique product names from the Sales table – and this
is now a usable table in its own right.
Calculated tables are really helpful when you want to build relationships, perform
advanced analysis, or create reference tables like date ranges, regions, or
combinations of values.
Another example of a calculated table might use functions like UNION,
NATURALINNERJOIN, or DATATABLE. These allow you to bring together data from
different sources or create complex joins.
Group Discussion Prompt:
"What could be a scenario in your organization where you'd want to build
a calculated table instead of importing one?"
Possible responses: Creating reporting hierarchies, custom date tables, filtered
views of transactions, or reference tables.

Part 3: Measures
Let’s shift gears now and dive into Measures – possibly one of the most powerful
features in Power BI.
So, what’s a Measure?
A Measure is a dynamic calculation that evaluates only in response to the filters
applied in your report. Unlike calculated columns, measures are not stored in the
table row-by-row. Instead, they are calculated on the fly when you load a visual.
That makes them super efficient and powerful.
Let’s take an example. Suppose you want to calculate total sales, average sales,
or count of unique customers. You can create measures to do exactly that.
Here’s the cool part: Measures adapt to the context of the report. If I drag my total
sales measure into a table showing data by month, it will calculate monthly totals. If
I show it by region, it recalculates accordingly. That’s what we call filter context –
and it’s the reason measures are so flexible.
Let’s go ahead and create one together.
Demonstration: Creating a Measure
1. In Power BI, go to the Data View or Report View.
2. Select the Sales table.
3. On the Modeling tab, click New Measure.
4. In the formula bar, type:
DAX
CopyEdit
TotalSales = SUM(Sales[TotalPrice])
5. Press Enter. Now you’ll see a small calculator icon next to "TotalSales" in the
Fields pane.
Drag this measure into a card visual – boom! You now have a live metric that
updates automatically based on any filters or slicers applied.
Measures can also use advanced DAX functions like CALCULATE, FILTER, DIVIDE, and
time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD.
For example, if you wanted to compare current year sales to last year’s sales, a
measure can do that.
Group Discussion Prompt:
"Why might you choose a measure over a calculated column for something
like total sales?"
Suggested answer: Because measures are calculated only when needed, are more
efficient, and automatically respect report filters.

Part 4: Recap and Module Review

Before we wrap up today, I want to briefly review everything we covered.
We started with calculated columns, which allow you to create new data fields
within a table using DAX – perfect when your model doesn’t quite have the structure
you need. Then we looked at calculated tables, which are new tables created
using existing model data. They’re great for creating reference lists, summary
views, and joining data. Finally, we dove deep into measures, which are dynamic
calculations that respond to filters and drive insights in your visuals.
All three of these tools use DAX – and while they may look similar at first, they
behave quite differently.
Let’s revisit our demonstration tasks to reinforce what we’ve learned. If you haven’t
already, try the following before our next session:
 Create a calculated column for tax-included prices.
  Build a calculated table showing distinct regions or product categories.
 Create a few key performance measures: total sales, average quantity,
number of unique customers.

Final Thoughts
Microsoft Power BI is transforming how we interact with data. Today’s session
showed how you can go beyond simple reporting and start building analytical
intelligence into your models. With tools like calculated columns, tables, and
measures, you can turn static data into living, breathing dashboards that answer
real business questions.
Don’t worry if DAX feels a bit intimidating at first – that’s completely normal. Like
learning a new language, it takes practice and repetition. But the more you use it,
the more natural it becomes.