All Episodes

October 14, 2025 • 37 mins
An introduction to Power BI, outlining its use in business intelligence applications. It covers fundamental concepts like data modeling, DAX calculations, and various visualization types such as bar, line, waterfall, scatter, donut, treemap, and map charts. The text also explains how to connect and shape data from diverse sources using Power Query Editor, optimize data models, implement Data Analysis Expressions (DAX) for calculated columns and measures, and create reports and dashboards within the Power BI Service. Furthermore, it touches upon advanced topics like Row-Level Security (RLS) and best practices for report development, including themes and templates.

You can listen and download our episodes for free on more than 10 different platforms:
https://linktr.ee/cyber_security_summary

Get the Book now from Amazon:
https://www.amazon.com/Mastering-Power-Intelligence-Applications-Visualizations/dp/9391030726?&linkCode=ll1&tag=cvthunderx-20&linkId=d8c2b7190648ab3a97d10d45ce2be320&language=en_US&ref_=as_li_ss_tl
Mark as Played
Transcript

Episode Transcript

Available transcripts are automatically generated. Complete accuracy is not guaranteed.
Speaker 1 (00:00):
Have you ever wondered how the biggest companies sift through
just mountains of information, how they find those crucial insights
that give them a real competitive edge.

Speaker 2 (00:09):
Yeah, it's a huge challenge.

Speaker 1 (00:11):
That's the fascinating question we're tackling today. Welcome to the
deep dive.

Speaker 2 (00:15):
It's a question that's more relevant than ever really in
our data rich world. The ability to quickly understand what
your data is telling you isn't just like good practice,
it's essential for.

Speaker 1 (00:27):
Survival, absolutely, and our mission today is to give you
a clear, streamlined understanding of a tool right at the
forefront of this transformation, powerbi. Right, think of this deep
dive as your shirtcut to understanding how powerbi transforms raw
data into actionable intelligence. We'll try to cut through the noise,
connect the dots, and deliver the essential insights you need

(00:50):
to feel truly well informed.

Speaker 2 (00:52):
Sounds good, Let's do it.

Speaker 1 (00:53):
So let's start at the beginning. We hear the term
business intelligence or BI thrown around a lot for anyone
new to it. What exactly talking about and where does
powerbi fit into that big picture?

Speaker 2 (01:04):
Okay, so, at its core, business intelligence is really about
taking messy raw data just you know, unorganized facts and
figures and turning it into something meaningful, something useful.

Speaker 1 (01:18):
Information information, right, not just data exactly.

Speaker 2 (01:22):
And then presenting that information in a way that helps
people make smarter decisions. Now, BI systems don't actually make
the decisions for you, no, of course not, but they
make the analysis much much easier. They surface the key stuff.

Speaker 1 (01:33):
That distinction between raw data and meaningful information is really key,
isn't it. So what kind of advantages does a good
BI system and you can like power BI bring to
a business.

Speaker 2 (01:44):
Oh, the benefits are huge. I mean, imagine having all
your company's information, from old legacy systems to cloud apps,
even simple spreadsheets, all brought together in one central place.
That's what a BI system does. It helps create what
we call a single version.

Speaker 1 (01:58):
Of the truth, a single source of truth. Everyone's working
off the same page precisely.

Speaker 2 (02:02):
Then it delivers that truth visually through interactive charts and dashboards,
making complex trends instantly understandable.

Speaker 1 (02:09):
And secure too, you mentioned, Yes.

Speaker 2 (02:11):
What's also crucial is that it's secure. People only see
the data they're authorized to view. We'll touch on that
later with something called row level security. Okay, And finally,
it empowers everyday business users. They can find answers themselves,
reducing reliance on the IT department. It's all about putting
powerful insights directly into the hands of those who need them.

Speaker 1 (02:30):
That sounds incredibly liberating for a business user. No more
waiting weeks for.

Speaker 2 (02:35):
A report exactly. Powerbi has really taken.

Speaker 1 (02:37):
Off, as you said, so what makes it stand out
against maybe other tools out there?

Speaker 2 (02:42):
Well, Powerbi has evolved into a real powerhouse suite. The
author of our source material, who's worked with lots of
other reporting tools, Tableau, Quick Cognos, you name it, finds
Powerbi at par and sometimes more advantageous. It's highly integrated.
It brings together everything from data connection and preparation and
powerbi Desktop to advance calculations with this language called DAX.

Speaker 1 (03:06):
We'll get to DAX later, I.

Speaker 2 (03:07):
Bet well, yeah, and then all the way through to
publishing and sharing with the Powerbi service online. It's a
comprehensive ecosystem that's powerful for data pros, but also intuitive
enough for business users.

Speaker 1 (03:20):
And speaking of users who typically interacts with Powerbi within
an organization, is it just the tech experts, the IT folks.

Speaker 2 (03:28):
Not at all? You generally see about four key types
of users. First, there's the Powerbi desktop developer. This person
is kind of the architect. Okay, they love data, They
write queries, model relationships, build those initial reports. Really gets
into the nuts.

Speaker 1 (03:43):
And bolts, got it, the builder.

Speaker 2 (03:44):
Then there's the Powerbi analyst. They deeply understand the business data,
work closely with stakeholders, and often build their own reports
to explore trends and importantly ensure data quality.

Speaker 1 (03:56):
So the people who really get into the weeds with
the data itself. What about others? You might just you know,
use the reports right.

Speaker 2 (04:02):
Next up is the power user. Think as someone who's
really good with Excel maybe but wants more power.

Speaker 1 (04:08):
Ah, the Excel wizards exactly.

Speaker 2 (04:11):
They use the existing data models that the developer build,
but they create new visualizations, AD filters, slice and dice.
They basically bridge that gap between the technical side and
the business side.

Speaker 1 (04:22):
Makes sense.

Speaker 2 (04:23):
And the last group, and finally, the executive user. These
are your department heads, your decision makers. They want those
high level dashboards clear quick KPIs.

Speaker 1 (04:32):
Like traffic lights green, yellow, red.

Speaker 2 (04:34):
Pretty much green for good, red for caution. They need
to see the overall health of their business unit quickly,
without needing to dive into all the technical details. POWERBI
really caters to all these different roles.

Speaker 1 (04:47):
That makes perfect sense, a tool for everyone. Really. Okay,
so we know what powerbi does and who uses it.
But before we dive deeper into the tool itself, what
are the fundamental building blocks of business intelligence that powerbi
RELI lies on? Sort of the underlying concepts?

Speaker 2 (05:02):
Excellent question. Yeah, let's unpack that. Think of it like
building a house. Your raw materials are your data sets. Okay,
these come from all sorts of places, old databases, cloud apps,
maybe excel files, even emails. Sometimes all the raw ingredients, right,
but those materials aren't ready used just yet. They need
to be prepared. That's where ETL comes in stands for extract,
transform load ETL.

Speaker 1 (05:24):
Okay, that sounds like a process, a journey for the data.

Speaker 2 (05:28):
It really is. You extract the raw data from all
those different sources. Then you transform it. That's where you
clean it up, maybe aggregated, apply business rules. Think of
it like washing, chopping and seasoning your ingredients before you.

Speaker 1 (05:39):
Cook, gotcha, cleaning and prepping exactly.

Speaker 2 (05:42):
Finally, you load that cleaned transform data into a target
system ready for analysis. This whole etl process is crucial
for building what we call a data warehouse.

Speaker 1 (05:54):
Ah, the data warehouse, I've heard that term. So this
is where all the good, clean, prepped data lives exactly.

Speaker 2 (06:00):
To warehouse is like your perfectly organized pantry designed specifically
for cooking up insights. It stores historical data from all
your operational systems, providing that single version of the truth
we talked about earlier. This central repository allows for much
faster queries and analysis. And sometimes you might have a
data mart, a datamark smaller, yeah, like a smaller specialized

(06:21):
pantry just for one part of the house, maybe specific
to the sales department or the marketing team. It holds
a subset of data, often pulled from the main data warehouse.

Speaker 1 (06:30):
Okay, So once this clean data is in its warehouse
or mart, how do we organize it for really fast
and effective analysis. This is where we get into data models.

Speaker 2 (06:40):
Precisely, a data model is essentially the blueprint for your
data warehouse. It's a pictorial representation of how all your
data pieces fit together, and importantly, it's designed to make
data access super fast for reporting.

Speaker 1 (06:53):
Faster access.

Speaker 2 (06:54):
Okay, The main ingredients in these models are dimension tables
and fact tables.

Speaker 1 (06:58):
Dimensions and facts.

Speaker 2 (06:59):
Yeah, dimensions whole descriptive information, things like a customer's name,
their address, product categories, mostly text. Factables hold the measurable stuff,
the numbers you want to analyze, like sales, amounts, quantities, profit,
things you can sum up or.

Speaker 1 (07:13):
Average, and these lead to that star shape people talk about.

Speaker 2 (07:17):
Yes, the star schema is the most popular model. Imagine
your fact table with all the numbers sitting right in
the middle, then surrounding it are all its related dimension tables,
like points on a star.

Speaker 1 (07:30):
Okay, I can picture that.

Speaker 2 (07:31):
This structure is fantastic for querying large amounts of data
very quickly because it minimizes the number of complex connections
or joins the system has to make.

Speaker 1 (07:40):
Makes sense less hopping around for the computer exactly.

Speaker 2 (07:44):
There's also something called the snowflake schema, which is basically
an extension where some dimensions might have further lookup tables
branching off them makes it look a bit like a snowflake. Yeah,
but the star is generally the go to for performance
in most BI tools, including POWERBI.

Speaker 1 (07:58):
Got it umer for speed. So once we have this
organized data, how do executives keep tabs on their business's health.
You mentioned KPI's earlier.

Speaker 2 (08:07):
Right, Key performance indicators or KPIs. Think of your car's
dashboard again. Fuel gauge, spiometer, engine warning light. Those are
your car's KPIs telling you it's overall health at a glance.
For a business, KPIs are those vital numbers like total
sales growth or customer retention rate that executives use to

(08:28):
track performance and guide decisions. They're usually specific to a
department like sales. Them out for the sales.

Speaker 1 (08:33):
Team, and we see these KPIs.

Speaker 2 (08:36):
Through through visualizations. Our brains process pictures much much faster
than rows and columns of text, so charts, graphs, maps,
even simple tables help us quickly grasp complex data, spot trends,
and find patterns that might be hidden otherwise. Visualizations display
those KPIs effectively.

Speaker 1 (08:53):
Which brings us to the dashboard exactly.

Speaker 2 (08:56):
A dashboard is like that car dashboard. A single page
visual snapshot combines several key visualizations onto one screen to
give you an immediate, high level overview of the most
important KPIs. It's all about quickly seeing the bigger picture.
But you know you have to be careful that the
visuals are truly data centric and provide context.

Speaker 1 (09:13):
Good point, not just pretty pictures. Okay, with those foundational
concepts in place, here's where it gets really interesting. I think,
how does powerbi itself act as your central hub for
all this data? What's its superpower in connecting and shaping
everything we've talked about.

Speaker 2 (09:30):
Well, powerbi is incredibly adept at handling a huge variety
of data sources, I mean, from ancient legacy systems right
up to the latest cloud platforms. Okay, it connects them,
extracts the data, and crucially it often compresses it so
your reports load much faster. But it's real magic, I think,
is in its data transformation capabilities.

Speaker 1 (09:49):
The t and etl Right.

Speaker 2 (09:51):
It allows you to clean, combine, and reshape data from
all those diverse sources into a robust, usable data model,
joining tables, combining data from excel csvs databases, cloud stuff
on premises, and handles.

Speaker 1 (10:05):
A lot and it makes it easier for regular users.

Speaker 2 (10:07):
Yeah, that's the goal. It provides an intuitive interface so
business users can gain insights themselves with self service aggregations
and visualizations. Plus it enables secure sharing of reports using
things like that row level security we.

Speaker 1 (10:18):
Mentioned, So it's not really just one single tool, is it.
It feels more like a collection of specialized tools working together.
Which ones are the key players within the Powerbi ecosystem.

Speaker 2 (10:28):
That's a great way to put it. The tightly integrated suite.
You primarily build your reports and models in Powerbi Desktop.
That's the main authoring toolsktop. Within Desktop, you use power
Query to connect to literally hundreds of data sources and
do all your data cleaning and transformation. It's like your
data prep kitchen.

Speaker 1 (10:45):
Power Career for prepping. Got it?

Speaker 2 (10:48):
Then DAS Data Analysis Expressions is the powerful formula language
you use inside Powerbi to create complex calculations and measures,
things that go beyond simple.

Speaker 1 (10:59):
Sums calculation engine pretty much.

Speaker 2 (11:02):
And once your report is polished in desktop, you publish
and share it via the Powerbi Service, which is the
online cloud based part. There are other pieces like Powerbi
Mobile and an on premises server option, but Desktop, Query,
tax and service are the core workflow.

Speaker 1 (11:17):
So if I'm say a Powerbi developer starting a new project,
what's my typical journey look like from start to finish?

Speaker 2 (11:24):
Well, a developer's journey U Stull begins with really understanding
the business needs, talking to stakeholders, figuring out the keykpis,
maybe looking at existing reports and pain points, understand the
why exactly. Then it's about getting access to the data,
understanding the sources that tables, the relationships that you connect,
extract and start cleaning and transforming that data in power Query.

(11:46):
Following best practices for data modeling is key here.

Speaker 1 (11:49):
Building that solid foundation right.

Speaker 2 (11:51):
Then you build the visualizations, maybe create some mockups first
to set expectations. You're often showing previews to users getting feedback. Finally,
the final steps are publishing the report to the powerbi
service or report server and setting up scheduled data refreshes
so the insight state current.

Speaker 1 (12:08):
That power Query editor you mentioned earlier sounds absolutely critical
for cleaning up messy data. What are some of the
most common maybe aha moments people have when they start
using it for transformations?

Speaker 2 (12:19):
Oh, the query editor is definitely where a lot of
magic happens. It shows you all your connected data sources
on the left, a preview of your data in the middle,
and crucially, it records every single transformation step you make
in an applied steps.

Speaker 1 (12:31):
List on the right, so you can undo things easily exactly.

Speaker 2 (12:34):
Or tweak a step later. It's non destructive.

Speaker 1 (12:37):
Some key transformations well, changing data types is fundamental making
sure a column that looks like a number, say a
postal code, is actually treated as text so powerbi doesn't
try to sum it up.

Speaker 2 (12:48):
Ah Yeah, that would be bad.

Speaker 1 (12:49):
Or making sure an ID field isn't automatically summarized. Another
huge aha moment is unpivoting columns. Imagine you get data
from an old report where years are spread across columns
like twenty fifteen sales, twenty sixteen sales, twenty seventeen sales. Yeah.

Speaker 2 (13:05):
Seen that format not great for analysis.

Speaker 1 (13:07):
Not at all. Unpivoting transforms that messy cross tab data
into a clean, normalized format maybe customer ID, year, sales
amount columns perfect for analysis. Power query makes that transformation
incredibly easy.

Speaker 2 (13:20):
That sounds really useful. What else? You can easily split
columns by a delimiter, Like if you have a product
code AA one zero three five time you can instantly
split it into two columns AA and one oh three
fifteen based on the hyphen and group by is invaluable.
Need to quickly check total sales by year or by
customer to verify things group by lets you do that
summarization right there in the editor. It really empowers you

(13:43):
to get your data into the perfect shape before you
even start building visuals.

Speaker 1 (13:47):
Amazing. So once we've prepared and cleaned our data using
tower query, the next big step is crafting that robust
data model. You said this is like the engine room
of our BI solution.

Speaker 2 (13:59):
It absolutely is. Data modeling is like creating the architectural
blueprint for your data. It organizes all the different data
elements your tables and defines how they relate to each other.
And crucially, it does this considering your business questions and KPIs.

Speaker 1 (14:11):
So it's not just connecting tables randomly definitely not.

Speaker 2 (14:13):
A well designed model is crucial because it feeds clean,
structured data to your visualizations. This ensures your reports are
not only accurate, but also perform efficiently. I mean without
a good model, even the prettiest charts can be slow
or worse misleading.

Speaker 1 (14:28):
Makes sense. Are there any golden rules or maybe best
practices for building a strong data model in POWERBI?

Speaker 2 (14:35):
Yeah, there are definitely some key ones. First, as we mentioned,
always aim for that star schema whenever possible, that central
fact table surrounded by dimensions. It's simply faster for reporting
in Powerbi's.

Speaker 1 (14:46):
Engine Star schema first, got it.

Speaker 2 (14:49):
Second, load only the data you actually need. If your
users only care about the last five years of sales,
filter out the older data before it even hits the model.
Don't bring in ten years if only five are required.

Speaker 1 (15:01):
Keep it lean less data faster reports exactly.

Speaker 2 (15:05):
Third, simplify. Reduce the number of tables and relationships where
you can maybe combined tables if it makes sense, and
critically hide unnecessary technical fields or intermediate tables from the
final report view. Keep the view clean for the end users.
These practices keep your model lean, fast and much easier
to understand and maintain.

Speaker 1 (15:22):
Okay, lean and clean. And how do we actually connect
these separate tables like linking customers to their orders? You
mentioned relationships right.

Speaker 2 (15:30):
Relationships are the glue holding your model together. They work
by matching data in common key columns between tables. Typically,
a primary key in one table, like customer ID in
the customer's table, matches a foreign key in another, like
customer ID in the order's table.

Speaker 1 (15:46):
And Powerbi helps with this.

Speaker 2 (15:47):
It does. Powerbi is actually quite smart and can often
auto detect these relationships based on column names, but it's
vital to review them, maybe refine them, or create them
manually if needed, to ensure they accurately reflect the real
world connections in your.

Speaker 1 (16:02):
Data, So you check the autodetected ones. What else defines
a relationship?

Speaker 2 (16:06):
Two key things cardinality and cross filter direction. Cardinality describes
how rose in one table relate to rose in another.
Most common is many to one point one. For instance,
many orders can belong to one customer. There's also one
to one point one and many.

Speaker 1 (16:22):
To many okay, And cross filter Cross.

Speaker 2 (16:24):
Filter direction tells power bi how filter should flow between
your connected tables. Should filtering the customer's table also filter
the order's table or should it work both ways or
only one way? You control that flow?

Speaker 1 (16:35):
Got it controlling the filter flow? Now?

Speaker 2 (16:38):
What off?

Speaker 1 (16:38):
My data is spread across several different files or tables,
and I actually need to combine them into one, like
merge them.

Speaker 2 (16:45):
For that, you'll typically use two powerful tools back in
the Power Query Editor merge queries and a pen queries
Merge in a pen Okay. Merge queries is essentially like
doing a sequel join. It combines columns from two table
based on matching values in a common field. For example,
you might have your main orders table, but the quantity
and unit price are in a separate order details excel file.

(17:08):
You can merge these using the order ID to bring
quantity and unit price into your main orders table.

Speaker 1 (17:14):
Ah, so you pull data across based.

Speaker 2 (17:16):
On a match exactly, and then you can often hide
the original order details table from the report view to
simplify your final model. Merge supports all the standard joint
types left out or inner, right, outer, et cetera.

Speaker 1 (17:27):
Okay, and append queries? How is that different?

Speaker 2 (17:29):
A pen queries is different? More like a SQL union.
It combines two tables that have the exact same columns
and structure by stacking the rows one on top of
the other.

Speaker 1 (17:38):
Stacking rows like adding more data precisely.

Speaker 2 (17:41):
So if you get a new file each month with
new customer records and it has the same columns as
your main customer's table, you can append the new files
data to your existing table, effectively adding more rows. The
key here is making sure the column headers and data
types match up perfectly before you append.

Speaker 1 (17:58):
That makes sense merge for adding a pen for adding rows.
So what does this all mean for our listener building reports.

Speaker 2 (18:04):
It means that a well crafted data model using these
best practices for relationships, using mergent dependent smartly is really
the silent hero of a great POWERBI report. It dramatically
reduces complexity behind the scenes, it boosts application performance, and fundamentally,
it ensures that your visualizations are built on a rock solid,
accurate foundation. This is how you truly achieve and trust

(18:27):
that single version of the truth.

Speaker 1 (18:29):
That's a fantastic blueprint for our data. Now let's shift
gears and talk about the calculations, the part that truly
brings data to life and unlocks those deeper insights DAX
data analysis expressions. How does this powerful formula language work?

Speaker 2 (18:42):
Its magic Ah, DAX, Yeah, you'd say. DAX is Powerbi's
secret superpower for calculations. The formula language kind of like
Excel formulas, but much more powerful, especially for analytics. It
allows you to create new, dynamic metrics and calculations that
aren't directly sitting there in your raw data tables.

Speaker 1 (19:01):
So going beyond just summing a column.

Speaker 2 (19:04):
Way beyond DAX lets you calculate things like you're over
year growth, running total sales profit margin for the top
ten products, or customer account for new customers this month.
It's how you get answers to more sophisticated business questions
directly within your reports.

Speaker 1 (19:18):
Okay, and I hear there's a really important distinction here,
one that's often a big aha moment for learners. Calculated
columns versus calculated measures. Can you break that down for us?
Why is this difference so fundamental?

Speaker 2 (19:30):
This is absolutely critical? Yeah, understanding this is key. Think
of calculated columns. First, When you create a calculated column,
it physically adds a new column to your table in
the data.

Speaker 1 (19:40):
Model, like adding a column in Excel kind of.

Speaker 2 (19:42):
Yes, it's calculated once for every single row in that
table when you define it or when your data set
is refreshed. So if you wrote uniprice order sales orders quantity,
it would calculate and store that unit price for every
single sales transaction line.

Speaker 1 (19:58):
Okay, stored for every row. It's the downside.

Speaker 2 (20:00):
The downside is it increases your data model size, sometimes
significantly because you're storing all those calculated values. This consumes
more memory ram and can slow things down. Do you
see them in your table view with a little column
icon right?

Speaker 1 (20:13):
Increase size uses more memory? So how are calculated measures different?

Speaker 2 (20:18):
Then measures are totally different. They're truly dynamic. They are
calculated on the fly, in real time, based on whatever
context you've applied in your report, like filters in a
chart or rows in a table.

Speaker 1 (20:27):
Visual calculated when needed exactly.

Speaker 2 (20:30):
They do not increase your data model size or RAM
because the results aren't stored physically row bi row in
the model. They typically use aggregation functions like SEM average count.
So a measure like total sales equals SEM order sales
only calculates when you actually drag total sales onto a visual,
and its value adapts instantly to whatever filters are active,

(20:52):
like year, region, product ah.

Speaker 1 (20:54):
So they're much more lightweight and.

Speaker 2 (20:56):
Flexible immensely so, measures are almost always preferred for aggregations, ratios, percentages,
and complex business logic because they respond to the user's interaction.
They appear with a little calculator icon in your field list,
and you won't see the results in the raw data
table view only in visuals. It's like asking a specific
question what were sales for the East region last year

(21:16):
and getting the answer right then, rather than having every
possible answer pre calculated and stored.

Speaker 1 (21:21):
For every row that is a huge difference. It explains
why sometimes reports can feel slow if maybe someone used
too many calculated columns instead of measures.

Speaker 2 (21:29):
It's often a big factor.

Speaker 1 (21:30):
Yes, okay, DAX sounds powerful, but maybe a bit intimidating.
Can you give us a quick taste of how DAX
tackles some common business calculations or problems, maybe some key functions.

Speaker 2 (21:40):
Sure, let's start with aggregations. You have your basic SOM
average count, but often you need to perform a calculation
row by row first, then aggregate the result. For that
you use iterator functions ending in x, like sumx sumx.

Speaker 1 (21:56):
How's that different.

Speaker 2 (21:56):
From s Okay, imagine you want total profit. Profit isn't
a column? Profit is a quantity unit price cost for
each order line. Sum can't do that multi step calculation,
but sums can iterate through your order's table, calculate the
profit for each row and then sum up those individual
row profits. It's essential for row level math, followed by aggregation.

Speaker 1 (22:18):
Got it iterate first, then some What about controlling the context?
That sounds powerful.

Speaker 2 (22:22):
That brings us to arguably the most important function in.

Speaker 1 (22:25):
All of daks, calculate, calculate.

Speaker 2 (22:27):
Calculate is magical Let's evaluate a DAX expression like belie
or sales, but temporarily change the filter context in which
it's evaluated. So if your chart is showing sales by region,
but you want to measure that always shows the total
sales for the year twenty eighteen, regardless of the region selected.
Calculate lets you override the existing region filter and apply
a new filter for just year twenty eighteen. It's the

(22:50):
key to comparisons, time, intelligence, and so much more. Wow.

Speaker 1 (22:54):
Okay, So let's you rewrite the rules for a specific
calculation precisely.

Speaker 2 (22:57):
And related to that is all ill often used to
inside calculate all removes existing filters. So calculate somes all
regions would give you the grand total sales across all regions.
Useful for calculating percentages of total.

Speaker 1 (23:09):
Okay, calculate and all work together. What about getting data
from related tables without merging them.

Speaker 2 (23:14):
For that, you often use functions like related or look
up value. If you're working in your order's table, which
has many orders per customer, and you need the customer
name from the related customer's table, which has one row
per customer, related customer's customer name can pull that value
across the many to one relationship. It needs row context,

(23:34):
so it works great in calculated columns or inside iterators
like CMMX. Look up value is similar but more flexible,
like v looakup in Excel.

Speaker 1 (23:44):
That sounds really useful for enriching data without cluttering the model.
What about basic conditional logic like f statements.

Speaker 2 (23:51):
If DAX has I, just like Excel, I have conditioned
value true, value false, you can nest them, but it
gets messy fast.

Speaker 1 (23:57):
Yeah. Nested ives are painful.

Speaker 2 (23:59):
Exactly, which is why the switch function is often much better.
Switch lets you evaluate an expression once and then list
pairs of results and values like switch month order date one,
Jan two, fab three, other, much cleaner than multiple nested
eyes for creating categories or handling multiple conditions.

Speaker 1 (24:15):
Switchach sounds like a life saver, So pulling it all together.
What does this mean for someone learning power BI and DAX.

Speaker 2 (24:21):
It means that while DAX definitely has a learning curve,
getting your head around these core concepts, especially that crucial
difference between calculated columns and measures, and understanding the power
of iterators like sumx and context modifiers like calculate will
fundamentally transform how you can analyze your data. It really
moves you beyond just basic reporting of what's already there

(24:41):
into dynamically asking and answering much more complex and valuable
business questions. Practice is key, but the payoff is huge powerful.

Speaker 1 (24:51):
Indeed, Okay, we've built our data model, We've crafted our
sophisticated DAX calculations. Now this is where it all comes together,
visually bringing our data in to focus with visualizations. This
is what the end user sees, right.

Speaker 2 (25:03):
Absolutely, visualizations are arguably the most important part for the
consumer because that's how they actually gain insights quickly. But
it's worth repeating the best, most insightful visuals are only
as good as the clean, well modeled data and accurate
DAX calculations that underpin them. Garbage in, pretty garbage out.

Speaker 1 (25:20):
Good point quality foundations matter.

Speaker 2 (25:24):
Now let's clear up something that often confuses people. Reports
versus dashboards and Powerbi. Are they the same thing? No,
they're definitely not, and it's a crucial distinction to understand.
Reports in Powerbi can be multipage. They are created primarily
in Powerbi desktop, though you can edit or create them
in the service too. Reports are highly interactive. Think filters slicers,

(25:45):
cross highlighting, drill downs. They're designed for detailed exploration and
analysis of a single data.

Speaker 1 (25:50):
Set, multipage interactive, single data set. Okay, how are dashboards different?

Speaker 2 (25:55):
Dashboards, on the other hand, are always single page canvases.
They're created only in the power Biel service, not desktop.
And here's the key. They're built by pinning individual visualizations
or even entire report pages from one or more underlying reports.
They generally don't have the same interactive filtering capabilities as reports.
Think of a dashboard as that high level, at a glance,

(26:15):
executive summary or monitoring view, often combining key visuals from
different reports and potentially different data sets onto one screen.

Speaker 1 (26:23):
Okay, so reports for exploring, dashboards for monitoring. Got it.
So when we're actually building these reports in power via desktop,
what are the main elements we're working with on the screen.

Speaker 2 (26:35):
Right, when you're in the report view, you have your
main report canvas. That's the big white space where you
range your visuals. You can have multiple pages. Here to
the right, you typically have three key pains.

Speaker 1 (26:45):
Three pains, Yeah.

Speaker 2 (26:46):
The visualization's pain. That's where you select your chart type
like a bar chart or a line chart. Below that
You can figure the fields for the selected visual dragging
data into access, legend, values, wells, etc. Then the field's pain,
which lists all the tables and fields available in your
data model. You drag things from here onto your visuals

(27:06):
or the canvas, and finally the filter pain. This is
essential for controlling what data your visuals display. You can
apply filters at the visual level, the page level, or
even across the entire report.

Speaker 1 (27:17):
Filters are key. Can you give us a few examples
of some core visualization types and what they're best used for?

Speaker 2 (27:23):
Absolutely, For displaying a single critical number like total sales
YTD or number of active customers, a card visual is perfect, simple, clear,
high impact, just the big number exactly. For comparing values
across different categories, like sales by product category or marketing
spend by channel, a bar chart horizontal bars or a

(27:44):
column chart vertical bars is your classic go to. You
can stack them too to show parts of a whole
within each category.

Speaker 1 (27:52):
Bar and column charts the workhorses. What about trends?

Speaker 2 (27:56):
For showing trends over time, this is crucial, like monthly
sales perform rmans or website visits per day. A line
chart is almost always the best choice. It clearly shows
the progression and fluctuations over a continuous period.

Speaker 1 (28:08):
Okay, lines for time. What about seeing how parts make
up a hole like market share for.

Speaker 2 (28:13):
Showing how parts contribute to a hole, A donut chart
or a pie chart works well, especially if you have
only a few categories, for example showing percentage of total
profit contributed by each business segment. Though use pie charts
with caution if you have too many.

Speaker 1 (28:25):
Slices right, they can get messy.

Speaker 2 (28:27):
A tree map is often better for that, especially with
hierarchical data. It displays data as nested rectangles, where the
size of each rectangle reflects a measure value like sales
by subcategory within category. What's great about tree maps and
many other visuals is how they can cross filter or
cross highlight other charts on the same page when you
click on a segment. Creates a very dynamic exploratory.

Speaker 1 (28:49):
Experience interactive filtering. Nice. What about location data.

Speaker 2 (28:53):
If you have geographical data Powerbi's map visuals are fantastic.
You can show data points as bubbles on a map
where the size reflects a measure like count of customers
by city, or use a filled map also called a
coropleth where entire regions like states or countries are shaded
based on a value like sales per capita by state,
really brings geographic patterns to life.

Speaker 1 (29:13):
Maps are always eye catching. What about more dynamic ways
for users to explore beyond just clicking on predefined visuals.

Speaker 2 (29:20):
Two really powerful features here, First the Q and a
question and answer visual. This literally lets users type questions
in plain English like show me top five customers by
profit in the West Region last quarter, and power Bi
attempts to understand the question and generate the appropriate visual
response on the fly.

Speaker 1 (29:38):
Wow, natural language queries.

Speaker 2 (29:40):
That's impressive, it really is. And the second is drill
through reports. This is super useful. Imagine you have a
summary page showing total sales by customer. You can set
up a drill through action so when a user write
clicks on a specific customer on that summary chart, they
can jump to a separate hidden detail page that shows
all the underlying order details just for that selected customer.

(30:01):
Powerbi even automatically adds a little back button to navigate
back easily. It allows for that summary review first, then
details on demand.

Speaker 1 (30:09):
Summary to details. That's very slick. Okay, we've built these
incredible interactive reports, maybe with drill throughs and natural language
Q and A. Now it's time to share those insights
with colleagues or clients. This brings us to the Powerbi service,
the cloud hub you mentioned earlier exactly.

Speaker 2 (30:24):
The Powerbi service sometimes called Powerbi Online, is where your
reports generally go to live and be shared. It's a
cloud based platform, software as a service or sauce that
acts as the central place to publish, share, collaborate on,
and consume reports and dashboards across your organization.

Speaker 1 (30:44):
So desktop is for building services for sharing and collaborating.
What's the typical workflow look like once you're ready to
move from desktop to the service.

Speaker 2 (30:52):
Yeah, the process is usually quite seamless. You create your
data model and design your reports primarily a Powerbi desktop.
Once they're polished and ready, you simply publish them from
desktop up to the Powerbi service just click button pretty much.
Once published, users with the right permissions can then access
and consume those reports through their web browser in the service.
They might also be able to modify reports there or

(31:15):
even create new reports based on the published data set
directly in the service, and critically, the service is where
you create those single page dashboards by pinning key visualizations
from your various reports.

Speaker 1 (31:26):
So the dashboards are built in the service using pieces
from the reports built in desktop.

Speaker 2 (31:30):
That's the most common pattern. Yes, you build the detailed
reports in desktop, publish them, then curate the high level
dashboard view in the service by pinning the most important visuals.

Speaker 1 (31:40):
And how easy is it to actually get your work
from your local desktop application into the cloud service.

Speaker 2 (31:47):
It's incredibly straightforward. Assuming you're signed into your powerbi account.
Within the desktop application, you just go to the home
ribbon and click the published button.

Speaker 1 (31:55):
Okay.

Speaker 2 (31:55):
It'll ask you to choose a destination workspace in the service,
maybe your personal my workspace or a shared team workspace.
You select it, click publish, and Parabia handles the rest,
uploading both your report file dot pbx and it's underlying
data set to the cloud. You then just navigate to
that workspace in your web browser to see it.

Speaker 1 (32:14):
Sounds simple enough, okay. One final, but truly critical aspect
for any business using data security. Once reports are published
in the service, how do we ensure that only the
right people see the right data, especially sensitive data?

Speaker 2 (32:29):
That is absolutely crucial and the primary mechanism for handling
this within Powerbi is Row level Security, often abbreviated.

Speaker 1 (32:36):
As ROLS Row level Security RLS. Its core objective is
pretty simple, restrict data access for specific users based on
defined roles. So, going back to our sales example, if
you have a sales manager for the East region and
another for the West, RLS ensures that when the East
manager logs in and looks at the company sales report,

(32:57):
they only see the data rose pertaining to the East region.
The West manager, looking at the exact same report, would
only see West region data.

Speaker 2 (33:06):
So the same report shows different data depending on who's looking.
That sounds powerful, but maybe complicated to set up.

Speaker 1 (33:13):
It sounds complicated, but it's actually surprisingly manageable to implement.
In Powerbi, the process generally involves a few key steps,
mostly done back in powerbi Desktop before.

Speaker 2 (33:22):
You publish, okay, what are the steps?

Speaker 1 (33:23):
First, obviously, in need your reports built the visuals that
will display the data you want to secure. Second, in
desktop's modeling ribbon, you go to managed roles. Here you
define your specific security roles. You just give them names
like salesperson East or salesperson West.

Speaker 2 (33:38):
Define the roles got it. Third, for each role you
create you are at a simple DAX filter expression on
the relevant table. For the salesperson East role, you might
filter the customer's table with the DAX expression like region
eg East. This tells Powerbi anyone assigned to this role
can only see rose where the region column is east.
Need to be careful with spelling and care.

Speaker 1 (34:00):
Here, of course, a dackx filter for each role.

Speaker 2 (34:02):
Okay, fourth and this is really neat. You can test
these roles right there in desktop using the view as option.
Also on the modeling ribbon, you can simulate logging in
as someone in the salesperson East role and instantly see
if you report visuals filter down correctly. Lets you verify
before publishing, test it first.

Speaker 1 (34:19):
Smart What happens after publishing good question.

Speaker 2 (34:22):
The roles and their dackx filters are published with the
data set to the Powerbi service. The final step happens
in the service. You navigate to the settings for the
published data set, find the security option, and this is
where you assign actual users or active directory of security
groups from your organization to the roles you created in desktop.
You map the real people to the abstract.

Speaker 1 (34:42):
Roles, assign users to roles in the service.

Speaker 2 (34:45):
Exactly Then when a user logs into powerbi service and
opens the report, Powerbi implicitly checks which roles they belong
to using functions like username. Behind the scenes applies the
corresponding DAX filters, and they only see the slice of
data they're authorized for. You should test it in the
service too, just to be sure.

Speaker 1 (35:03):
So wrapping up ROLS, what does this all mean for
delivering secure insights?

Speaker 2 (35:08):
It means RLS is a fundamental and relatively easy to
implement feature in powerbi. It empowers organizations to confidently deliver
highly contextual and secure insights to a broad audience. You
can share one report but trust that different users will
see only their relevant part of the picture, ensuring data
governance without sacrificing the power and flexibility of self service analytics.

(35:29):
It's really key to making that single version of the
truth trustworthy and usable across the board.

Speaker 1 (35:34):
Fantastic, What an incredible journey we've taken today, Seriously, from
understanding the very foundations of business intelligence and powerbis powerful components,
right through the intricate steps of data modeling and the
well sometimes mind bending magic of DAX calculations, all the
way to crafting those dynamic visualizations and crucially securely sharing

(35:55):
your insights using the powerbi service and ROLS. I truly
hope this deep dive has given you, our listener, a
robust framework for understanding and leveraging powerbi. Think of it
as a shortcut to being genuinely well informed in this
really critical area.

Speaker 2 (36:11):
Indeed, and maybe a final thought to leave folks with.
As we've explored how powerbi connects, transforms, visualizes and secure
as data, consider this. The true power of these tools
isn't just in presenting the truth as the data shows it,
but perhaps in democratizing the ability for more people to
ask sophisticated questions.

Speaker 1 (36:31):
Of their data, democratizing questions.

Speaker 2 (36:32):
I like that? Yeah, So, how might a deeper understanding
of your own data using tools like this lead you
to question assumptions you've maybe held for a long time
about your business, your work, or even the world around you.
What new questions could you ask?

Speaker 1 (36:45):
I truly thought provoking question to end on, We absolutely
encourage you to explore powerbi further. If this has piqued
your interest, perhaps even try out some of the concepts
we've discussed today, and definitely continue your own learning journey
into the world of data
Advertise With Us

Popular Podcasts

My Favorite Murder with Karen Kilgariff and Georgia Hardstark

My Favorite Murder with Karen Kilgariff and Georgia Hardstark

My Favorite Murder is a true crime comedy podcast hosted by Karen Kilgariff and Georgia Hardstark. Each week, Karen and Georgia share compelling true crimes and hometown stories from friends and listeners. Since MFM launched in January of 2016, Karen and Georgia have shared their lifelong interest in true crime and have covered stories of infamous serial killers like the Night Stalker, mysterious cold cases, captivating cults, incredible survivor stories and important events from history like the Tulsa race massacre of 1921. My Favorite Murder is part of the Exactly Right podcast network that provides a platform for bold, creative voices to bring to life provocative, entertaining and relatable stories for audiences everywhere. The Exactly Right roster of podcasts covers a variety of topics including historic true crime, comedic interviews and news, science, pop culture and more. Podcasts on the network include Buried Bones with Kate Winkler Dawson and Paul Holes, That's Messed Up: An SVU Podcast, This Podcast Will Kill You, Bananas and more.

24/7 News: The Latest

24/7 News: The Latest

The latest news in 4 minutes updated every hour, every day.

Dateline NBC

Dateline NBC

Current and classic episodes, featuring compelling true-crime mysteries, powerful documentaries and in-depth investigations. Follow now to get the latest episodes of Dateline NBC completely free, or subscribe to Dateline Premium for ad-free listening and exclusive bonus content: DatelinePremium.com

Music, radio and podcasts, all free. Listen online or download the iHeart App.

Connect

© 2025 iHeartMedia, Inc.