Training Module | POWER BI EFFICIENCY AND DATA MODELLING

📘 TRAINING MODULE

POWER BI EFFICIENCY, DATA PIPELINES

AND DATA MODELLING
Building lean dashboards with Power Query, semantic models and DAX

Generated from provided Power BI training transcripts

SECTION 0 | Module Overview

📂 Module Overview
This module explains how to build lean, efficient Power BI dashboards and data models. It turns a raw
training discussion into a structured guide covering scope, pipelines, Power Query, semantic models,
DAX and governance.
The central idea is simple: good reporting does not only mean attractive visuals. Good reporting means
useful insights delivered with less wasted data, compute, time and maintenance effort.

🎯 Learning Objectives
 Explain what efficiency means in dashboard and reporting work.
 Connect scope gathering and stakeholder communication to better report outcomes.
 Describe the data pipeline as a chain of extract, transform and load steps.
 Decide when to push transformations upstream or keep them downstream.
 Compare star schema and snowflake schema thinking for Power BI models.
 Use Power Query in a way that supports query folding and reduces unnecessary data load.
 Design semantic model relationships using keys, granularity and clear data flow.
 Choose measures before calculated columns when aggregation can solve the problem.
 Recognise useful DAX functions for flexible measures and debugging.
 Identify governance actions that reduce refresh load, storage and long-term maintenance effort.

SECTION 1 | Why Efficiency Matters in Power BI Reporting

🔹 What is Efficiency in reporting?

Power BI Efficiency Training Page 1

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

Efficiency means achieving the useful outcome with less wasted time, storage, compute, rework and
cost. In Power BI, an efficient report is not just a report that looks tidy. It is a report that loads quickly,
uses only the data it needs, is easier to test, and gives users a smoother experience.

💡 Think of it this way...

The trainer opened with a paper aeroplane activity. At first, groups made different designs and lost time
deciding what 'professional grade' meant. Once a group copied one agreed design and aligned their
work, they became faster and more consistent. Dashboards behave the same way: unclear scope and
inconsistent methods waste effort.

💡 In the paper aeroplane activity, the fastest groups were the ones that aligned on
a single design and copied the best approach. That mirrors a reporting team
agreeing on one method, one scope and one standard.

📌 Key Points:
 Efficiency is about maximising useful output while minimising wasted resources.
 A clunky dashboard costs time because it is slower to build, load, test and use.
 Reducing unnecessary data reduces storage, compute and business cost.
 Standardised ways of working make reporting easier to scale across teams.
 The goal is not to remove useful detail; the goal is to remove avoidable waste.

✅ Key Takeaway: An efficient dashboard gives users the insight they need while using the
least practical amount of data, compute and maintenance effort.

SECTION 2 | Scope Gathering and Stakeholder Alignment

🔹 What is Scope gathering?

Scope gathering means finding out exactly what the report must do before building it. A dashboard
often has multiple stakeholder groups using the same product, so the team must understand each
group's requirements before deciding the model, visuals and data.

💡 Think of it this way...

If a client asks for a 'professional grade' paper aeroplane, the phrase sounds clear until people start
building different versions. Scope gathering is the moment where you ask what 'professional grade'
means before time is spent in the wrong direction.

💡 Before building, ask: Who will use this dashboard? What decisions will they

Power BI Efficiency Training Page 2

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

make? What data must be visible? What data can be excluded? What refresh
frequency is actually needed?

📌 Key Points:
 Better scoping reduces rework later in the report build.
 Stakeholder meetings should capture requirements across all teams using the dashboard.
 Active communication keeps the build aligned with the specification.
 Visibility and accuracy improve when requirements are gathered clearly.
 A scope form can help, but the quality of stakeholder conversations still matters.

✅ Key Takeaway: Good scope gathering is the first efficiency tool because it prevents
teams from building the wrong thing quickly.

SECTION 3 | Understanding the Data Pipeline

🔹 What is ETL and the data stream?

A data pipeline is the route data travels from its original source to the final report. The trainer described
the pipeline as a chain of extract, transform and load steps, often called ETL. Extract means getting
data from a source. Transform means changing, filtering, joining or shaping it. Load means placing the
result somewhere another process can use it.

💡 Think of it this way...

Think of data like water moving through a series of pipes. At several points, the water may be collected,
cleaned, redirected and stored. A Power BI report is near the end of that stream, but it still depends on
every earlier pipe.

💡 The transcript mentions source systems, Snowflake, holding repositories,

semantic models and reports as parts of the same data stream. The exact
system names matter less than the principle: data is repeatedly moved and
shaped before users see it.

📌 Key Points:
 ETL steps can happen many times across the full data journey.
 One system's load step may become another system's extract step.
 Upstream means closer to the original data input or enterprise storage.
 Downstream means closer to the report, semantic model, Excel file or user-facing output.
 Power BI itself contains smaller ETL stages: Power Query, semantic model and reporting layer.

Power BI Efficiency Training Page 3

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

✅ Key Takeaway: To make a report efficient, you must understand where data is being
extracted, transformed and loaded across the pipeline.

SECTION 4 | Upstream vs Downstream Workloads

🔹 What is Workload placement?

Workload placement means deciding where a transformation should happen. A simple task such as
combining first name and surname could happen near the source, in a warehouse, in Power Query, in
the semantic model, or even near the visual layer. Each position changes the balance between
efficiency, control and flexibility.

💡 Think of it this way...

Imagine printing name badges. If everyone in the organisation needs the same full name format, make
it once upstream. If one small event needs a special format, keep that change downstream for that
event only.

⚠️ Do not push everything upstream automatically. If a transformation only

supports one report or one subset of users, forcing it into a shared upstream
layer may reduce flexibility.

📌 Key Points:
 Pushing work upstream can create one version of the truth.
 Upstream systems are often better at processing large data volumes efficiently.
 Downstream work gives report developers more local control and flexibility.
 Downstream work is useful when a change applies only to a subset of data or a specific report.
 The best choice balances scale, cost, standardisation and local control.

✅ Key Takeaway: Push shared, high-volume, standard logic upstream where possible, but
keep report-specific logic downstream when flexibility is needed.

SECTION 5 | Star Schema and Snowflake Schema Thinking

🔹 What is Schema design?

Power BI Efficiency Training Page 4

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

A schema is the structure of your data model: how tables are laid out and connected. A fact table stores
events or transactions, such as one row per clock-in, contract, plan or sale. Dimension tables store
descriptive context, such as crew member, role, geography, date or document information.

💡 Think of it this way...

A fact table is like a till receipt log: lots of repeated events. Dimension tables are like reference cards
that explain the people, places, dates and categories connected to those events.

💡 For geography, do not copy country and city descriptions into every event row if
a small geography dimension can hold that information once and relate it back
to the fact table.

📌 Key Points:
 A star schema has a central fact table connected to dimension tables.
 A very wide fact table can become bloated if it contains many columns nobody uses.
 A snowflake-style schema keeps the fact table lean and uses more normalised dimension tables.
 Normalised means repeated descriptive data is kept once in a reference-style table instead of
copied many times.
 Snowflake is both a product name and a schema concept; the transcript uses both ideas, so keep
the distinction clear.
 Power BI developers can shape the model; the end user usually sees only the report experience.

✅ Key Takeaway: A lean model keeps the fact table focused and uses dimension tables to
add the context needed for analysis.

SECTION 6 | Power Query Efficiency and Query Folding

🔹 What is Power Query and query folding?

Power Query is the Power BI stage where data is extracted and shaped before being loaded into the
model. Query folding is Power BI's attempt to translate your Power Query steps back into source-
system work, such as SQL, so the source does more of the heavy lifting.

💡 Think of it this way...

Ordering only the ingredients you need is faster than receiving the whole warehouse and throwing most
of it away. Power Query is most efficient when it asks for the right data early instead of importing
everything first.

Power BI Efficiency Training Page 5

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

📝 Recommended Power Query order from the transcript: 1) define required rows
and columns, 2) group or aggregate, 3) join or merge, 4) replace values or
errors, 5) rename at the end.

📌 Key Points:
 Use Get Data carefully so the report extracts only the data it needs.
 For SQL-style sources such as Snowflake, advanced options may allow a SQL statement during
extraction.
 Use Import for most small and medium report models because it gives a snappier user experience.
 DirectQuery is mainly for very large semantic models, but it can feel clunkier for users.
 Query folding is helped by doing simple, source-friendly steps early.
 Steps such as indexing, buffering, removing duplicates and local-machine-time logic can break
query folding.

✅ Key Takeaway: Power Query should reduce the dataset early and keep foldable steps
before steps that block folding.

SECTION 7 | Semantic Model Relationships and Granularity

🔹 What is Relationships, keys and grain?

The semantic model is the Power BI layer where tables, relationships, measures and calculated
columns are defined. Relationships connect tables using keys. A primary key uniquely identifies rows in
one table. A foreign key is a matching field in another table that points back to it.

💡 Think of it this way...

Keys are like reference numbers on forms. If two forms share the same reliable reference number,
Power BI can understand how they belong together. If the reference is not unique or the route is
ambiguous, the model becomes unreliable.

⚠️ The transcript warns that one person can sometimes have multiple records
across time. That is why a name or date-of-birth combination is not a safe
unique key.

📌 Key Points:
 Review automatically created relationships instead of trusting them blindly.
 Delete relationships that do not make sense for the model.
 Avoid ambiguous data paths where Power BI can reach the same table through multiple routes.

Power BI Efficiency Training Page 6

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

 Granularity means what one row represents: one crew member, one contract, one plan, one
document, one transaction and so on.
 Do not use fields such as name, date of birth or employee number as keys if they may repeat or
change.
 Use true unique IDs such as crew member ID, plan ID or contract ID where available.

✅ Key Takeaway: A good semantic model has clear relationships, unique keys and a
precise understanding of what each row represents.

SECTION 8 | Measures vs Calculated Columns

🔹 What is DAX measures and calculated columns?

A measure is a DAX calculation designed for aggregation, such as a sum, count, average or filtered
result. A calculated column creates a physical column and calculates a value row by row. Measures are
generally more efficient because they calculate only in the context needed by the current visual or filter.

💡 Think of it this way...

A calculated column is like writing a label on every single item in a warehouse. A measure is like
counting only the items on the shelf the user is looking at right now.

💡 A common inefficient pattern is creating separate calculated columns for many

scenarios. Often, the same logic can be handled more efficiently inside a
measure.

📌 Key Points:
 Measures calculate on the relevant filtered subset of data.
 Measures are lightweight and designed for fast aggregation.
 Calculated columns calculate for every row and take more storage and compute.
 Calculated columns are still needed when Power BI needs a physical field, such as an axis value.
 The trainer strongly recommended trying a measure-based solution before using calculated
columns.

✅ Key Takeaway: Use measures first when you can; use calculated columns only when
the report genuinely needs a physical row-level field.

Power BI Efficiency Training Page 7

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

SECTION 9 | Useful DAX Functions for Flexible Measures

🔹 What is DAX function choices?

DAX is the formula language used in Power BI for measures, calculated columns and model logic. The
transcript focused on functions that make measures flexible: functions that read selected values, filter
data, remove filters, calculate conditional aggregates and create debugging flags.

💡 Think of it this way...

Think of DAX functions as tools in a small toolkit. CALCULATE changes the working context, FILTER
narrows the data, ALL and REMOVEFILTERS lift filters away, and SWITCH helps choose between
multiple possible outcomes.

📝 Example pattern from the transcript: use CALCULATE around an aggregation,

then apply a condition such as country equals UK. The key idea is to compute
the aggregation only for the filtered context.

📌 Key Points:
 SELECTEDVALUE turns the currently selected value from a column into a scalar value for a
measure.
 VALUES returns the list of values in a column.
 FILTER narrows a table based on conditions.
 ALL can ignore existing filters and return everything for a table or column.
 REMOVEFILTERS removes specified filters without necessarily removing every filter.
 CALCULATE is similar in spirit to Excel's SUMIF or AVERAGEIF, but you can define the
aggregation and filters.
 COUNTROWS can become a Boolean-style flag when combined with conditions such as greater
than zero.
 SWITCH is cleaner than many nested IF statements when there are multiple conditions.

✅ Key Takeaway: Good DAX measures use filter context deliberately rather than forcing
data into extra columns.

SECTION 10 | Avoiding Lookup Habits and Debugging Safely

🔹 What is Relationship-aware DAX?

Power BI Efficiency Training Page 8

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

The trainer warned against relying heavily on RELATED and LOOKUPVALUE. These functions can
feel familiar because they mentally move values from one table into another, but frequent use can
indicate that the relationship model is not strong enough.

💡 Think of it this way...

The trainer compared relationships to railway tracks. If the data needs to travel a different track, use the
relationship controls to switch track, rather than dragging values across after the journey has already
gone the wrong way.

⚠️ The transcript's guidance is practical: Copilot is useful for filling knowledge gaps
and debugging, but do not rely on it without checking how the code affects the
model.

📌 Key Points:
 If relationships are correct, related dimensions can often stay in their own tables.
 Do not build one bloated table simply because Excel-style habits make joining feel natural.
 USERELATIONSHIP can activate a specific inactive relationship in a measure.
 TREATAS can help when there is no direct inactive relationship to activate.
 Use Copilot cautiously for DAX debugging; it can help, but it can also break things if you accept
suggestions blindly.
 Create a separate measure named Debug or similar when testing generated or experimental DAX.

✅ Key Takeaway: Prefer a clean relationship model over lookup-heavy formulas, and test
risky DAX separately before changing production logic.

SECTION 11 | Date Tables, Many-to-Many Relationships and

Parameters

🔹 What is Advanced model controls?

The later part of the training introduced several more advanced model controls: date/calendar tables,
bridge tables, cross-filter direction and parameters. These tools help keep models flexible while
avoiding ambiguity and repeated work.

💡 Think of it this way...

A date table is like one shared calendar pinned to the wall. Instead of every table inventing its own date
logic, the model can relate events to the same calendar where appropriate.

Power BI Efficiency Training Page 9

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

📝 The transcript did not fully teach bridge table creation, visual calculations,
hierarchies or calculation groups. It flagged them as topics to explore further.

📌 Key Points:
 A DAX calendar table can create a shared date dimension with columns such as year, month and
formatted month-year.
 A calendar table should relate to the date field that matches the report purpose, such as application
start date rather than date of birth when analysing applications.
 One calendar table can connect actively to only one date field in the same fact table at a time.
 Many-to-many relationships can create ambiguity and should usually be avoided.
 Bridge tables are the preferred pattern when many-to-many relationships cannot be avoided.
 Cross-filter direction is single by default; both-direction filtering has use cases but must be
investigated carefully.
 Front-end field parameters can let users switch visual fields on the fly.
 Numeric range parameters act like slider-controlled variables for what-if style measures.
 Back-end Power Query parameters can store reusable variables such as file paths, start dates or
end dates.

✅ Key Takeaway: Advanced modelling tools are powerful, but they should be used to
reduce ambiguity and repetition, not to hide poor model structure.

SECTION 12 | Workflow Recommendations for Better Power BI

Governance

🔹 What is Report governance and team habits?

Governance means the habits, roles and controls that keep reporting work manageable over time. The
trainer closed by connecting technical efficiency to team practices: knowing what reports exist, what
refreshes happen, what datasets are large, and who will champion good Power BI practice.

💡 Think of it this way...

A report estate is like a shared workshop. If nobody knows what tools are being used, what machines
run daily, or who maintains them, cost and clutter grow quietly.

💡 A practical first action from the training: review current reports and refreshes,
then ask which ones still need to run, how often they should refresh, and
whether their datasets are unnecessarily large.

Power BI Efficiency Training Page 10

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

📌 Key Points:
 Maintain visibility of existing reports and refresh schedules.
 Ask whether each refresh needs to happen daily or whether weekly is enough.
 Restrict large unfiltered datasets unless building a shared semantic model that genuinely needs
them.
 For report-specific models, define the needed dataset early in Power Query.
 Use focused fact tables and only the dimensions the report needs.
 Document Power Query steps so other users can understand what has been done.
 Assign one or two Power BI champions to reinforce best practice and share learning.

✅ Key Takeaway: Efficient Power BI work is not only a technical practice; it also depends
on visibility, documentation and shared ownership.

SECTION A | DAX and Power Query Quick Reference

📚 Power Query Step Order

# Step Purpose

1 Define rows and Filter and choose only the data needed before bringing too much into
columns early the model.

2 Group or aggregate Let source-friendly grouping reduce volume where possible.

3 Join or merge Merge after reducing the data so joins run on smaller tables.

4 Replace values or Clean only the remaining useful data.

errors

5 Rename at the end Avoid renaming columns that may later be removed.

📚 DAX Function Reference

Power BI Efficiency Training Page 11

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

Function Plain-English Meaning Training Use

SELECTEDVALU Returns the current selected value Useful for measures driven by slicer
E from a column when there is one selections.
clear selection.

VALUES Returns the list of values in a column. Useful when a measure needs a value
list rather than a single scalar.

FILTER Narrows a table based on a condition. Often used inside CALCULATE for more
explicit filtering.

ALL Returns everything by removing Useful when a measure must ignore

existing filtering from a table or page or visual filters.
column.

REMOVEFILTER Removes specific filters. Useful when one filter should not affect
S a measure.

CALCULATE Evaluates an aggregation under Works like a flexible SUMIF or

modified filter conditions. AVERAGEIF pattern.

COUNTROWS Counts rows in a table expression. Can become a flag when tested as
greater than zero.

IF / SWITCH Returns different results based on Use SWITCH when many nested IF
conditions. conditions become hard to manage.

USERELATIONS Activates a specific inactive Useful when the model has multiple
HIP relationship in a measure. possible relationship paths.

TREATAS Applies values from one table as Useful when no relationship exists but a
filters on another table. measure needs relationship-like
behaviour.

Power BI Efficiency Training Page 12

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

Prompt Library: No prompt library is included because the transcripts do not contain AI
prompts, prompt templates or prompt-writing instructions.

SECTION R | Revision System

🔁 Revision System
🃏 Flashcards (Q&A)

Q A
What does efficiency mean in Power BI Achieving useful dashboard outcomes with less
reporting? wasted time, storage, compute, rework and cost.

Q A
Why was the paper aeroplane activity used? To show that unclear scope and inconsistent
methods reduce efficiency, while alignment
improves speed and consistency.

Q A
What are the three parts of ETL? Extract, Transform and Load.

Q A
What is the benefit of pushing shared logic It can create one version of the truth and use
upstream? larger infrastructure more efficiently.

Power BI Efficiency Training Page 13

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

Q A
What is the benefit of keeping some logic It gives report developers more local control and
downstream? flexibility for specific use cases.

Q A
What is a fact table? A table that stores events or transactions, usually
with many rows.

Q A
Why should calculated columns be used They calculate row by row and can increase
carefully? storage and compute compared with measures.

Q A
What does query folding try to do? It tries to push Power Query transformations
back to the source system for more efficient
processing.

🧠 Quick Quiz
1. Which choice best describes an efficient dashboard?
a) One with the most visuals
b) One with all possible data loaded
c) One that delivers useful output with minimal waste
d) One that uses only calculated columns
✅ Answer: c) One that delivers useful output with minimal waste

2. True or false: Scope gathering is only a paperwork exercise and does not affect report
efficiency.
a) True
b) False
✅ Answer: False

3. What does the Transform step in ETL usually involve?

a) Deleting the report
b) Changing, filtering, joining or shaping data
c) Publishing a dashboard
d) Buying more storage
✅ Answer: b) Changing, filtering, joining or shaping data

Power BI Efficiency Training Page 14

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

4. Which is usually better for small and medium report models in the transcript guidance?
a) Import
b) DirectQuery
c) No relationships
d) Many-to-many only
✅ Answer: a) Import

5. Which Power Query step should generally happen early?

a) Rename every column
b) Index rows
c) Buffer the table
d) Choose/filter required rows and columns
✅ Answer: d) Choose/filter required rows and columns

6. What is granularity?
a) The colour of the report
b) What one row in a table represents
c) A page refresh schedule
d) A Power BI licence type
✅ Answer: b) What one row in a table represents

7. When should a calculated column be considered?

a) Whenever a measure feels difficult
b) When a physical row-level field is genuinely needed, such as for an axis
c) Before checking relationships
d) Only for page titles
✅ Answer: b) When a physical row-level field is genuinely needed, such as for an axis

8. What is the trainer's recommended safe habit when testing Copilot-generated DAX?
a) Paste it directly into the final measure
b) Never read it
c) Use a separate debug measure first
d) Delete the model relationships
✅ Answer: c) Use a separate debug measure first

⭐ Top 5 Things to Remember

1 Define the scope clearly before building the report.

2 Reduce data early and avoid loading columns or rows you do not need.

3 Push shared, large-scale logic upstream when it improves standardisation and cost.

Power BI Efficiency Training Page 15

Training Module | POWER BI EFFICIENCY AND DATA MODELLING

4 Use a clean semantic model with clear keys, grain and relationship paths.

5 Prefer measures over calculated columns unless a physical field is truly required.

⚠️Missing or Incomplete Topics

 Bridge table creation was mentioned as best practice but not taught step by step.
 Visual calculations were introduced briefly but not demonstrated in detail.
 Hierarchies and calculation groups were named as useful tools but not covered deeply.
 The DAX calendar table example was partially transcribed; the module captures the concept but not
a verified final code block.
 Costing details for Snowflake and compute usage were intentionally not explored in depth.

Module generated from the provided Power BI training transcripts. Review technical examples before using
them in production.

Power BI Efficiency Training Page 16