Speaker 1: Have you ever wondered how the biggest companies sift through
Speaker 1: just mountains of information, how they find those crucial insights
Speaker 1: that give them a real competitive edge.
Speaker 2: Yeah, it's a huge challenge.
Speaker 1: That's the fascinating question we're tackling today. Welcome to the
Speaker 1: deep dive.
Speaker 2: It's a question that's more relevant than ever really in
Speaker 2: our data rich world. The ability to quickly understand what
Speaker 2: your data is telling you isn't just like good practice,
Speaker 2: it's essential for.
Speaker 1: Survival, absolutely, and our mission today is to give you
Speaker 1: a clear, streamlined understanding of a tool right at the
Speaker 1: forefront of this transformation, powerbi. Right, think of this deep
Speaker 1: dive as your shirtcut to understanding how powerbi transforms raw
Speaker 1: data into actionable intelligence. We'll try to cut through the noise,
Speaker 1: connect the dots, and deliver the essential insights you need
Speaker 1: to feel truly well informed.
Speaker 2: Sounds good, Let's do it.
Speaker 1: So let's start at the beginning. We hear the term
Speaker 1: business intelligence or BI thrown around a lot for anyone
Speaker 1: new to it. What exactly talking about and where does
Speaker 1: powerbi fit into that big picture?
Speaker 2: Okay, so, at its core, business intelligence is really about
Speaker 2: taking messy raw data just you know, unorganized facts and
Speaker 2: figures and turning it into something meaningful, something useful.
Speaker 1: Information information, right, not just data exactly.
Speaker 2: And then presenting that information in a way that helps
Speaker 2: people make smarter decisions. Now, BI systems don't actually make
Speaker 2: the decisions for you, no, of course not, but they
Speaker 2: make the analysis much much easier. They surface the key stuff.
Speaker 1: That distinction between raw data and meaningful information is really key,
Speaker 1: isn't it. So what kind of advantages does a good
Speaker 1: BI system and you can like power BI bring to
Speaker 1: a business.
Speaker 2: Oh, the benefits are huge. I mean, imagine having all
Speaker 2: your company's information, from old legacy systems to cloud apps,
Speaker 2: even simple spreadsheets, all brought together in one central place.
Speaker 2: That's what a BI system does. It helps create what
Speaker 2: we call a single version.
Speaker 1: Of the truth, a single source of truth. Everyone's working
Speaker 1: off the same page precisely.
Speaker 2: Then it delivers that truth visually through interactive charts and dashboards,
Speaker 2: making complex trends instantly understandable.
Speaker 1: And secure too, you mentioned, Yes.
Speaker 2: What's also crucial is that it's secure. People only see
Speaker 2: the data they're authorized to view. We'll touch on that
Speaker 2: later with something called row level security. Okay, And finally,
Speaker 2: it empowers everyday business users. They can find answers themselves,
Speaker 2: reducing reliance on the IT department. It's all about putting
Speaker 2: powerful insights directly into the hands of those who need them.
Speaker 1: That sounds incredibly liberating for a business user. No more
Speaker 1: waiting weeks for.
Speaker 2: A report exactly. Powerbi has really taken.
Speaker 1: Off, as you said, so what makes it stand out
Speaker 1: against maybe other tools out there?
Speaker 2: Well, Powerbi has evolved into a real powerhouse suite. The
Speaker 2: author of our source material, who's worked with lots of
Speaker 2: other reporting tools, Tableau, Quick Cognos, you name it, finds
Speaker 2: Powerbi at par and sometimes more advantageous. It's highly integrated.
Speaker 2: It brings together everything from data connection and preparation and
Speaker 2: powerbi Desktop to advance calculations with this language called DAX.
Speaker 1: We'll get to DAX later, I.
Speaker 2: Bet well, yeah, and then all the way through to
Speaker 2: publishing and sharing with the Powerbi service online. It's a
Speaker 2: comprehensive ecosystem that's powerful for data pros, but also intuitive
Speaker 2: enough for business users.
Speaker 1: And speaking of users who typically interacts with Powerbi within
Speaker 1: an organization, is it just the tech experts, the IT folks.
Speaker 2: Not at all? You generally see about four key types
Speaker 2: of users. First, there's the Powerbi desktop developer. This person
Speaker 2: is kind of the architect. Okay, they love data, They
Speaker 2: write queries, model relationships, build those initial reports. Really gets
Speaker 2: into the nuts.
Speaker 1: And bolts, got it, the builder.
Speaker 2: Then there's the Powerbi analyst. They deeply understand the business data,
Speaker 2: work closely with stakeholders, and often build their own reports
Speaker 2: to explore trends and importantly ensure data quality.
Speaker 1: So the people who really get into the weeds with
Speaker 1: the data itself. What about others? You might just you know,
Speaker 1: use the reports right.
Speaker 2: Next up is the power user. Think as someone who's
Speaker 2: really good with Excel maybe but wants more power.
Speaker 1: Ah, the Excel wizards exactly.
Speaker 2: They use the existing data models that the developer build,
Speaker 2: but they create new visualizations, AD filters, slice and dice.
Speaker 2: They basically bridge that gap between the technical side and
Speaker 2: the business side.
Speaker 1: Makes sense.
Speaker 2: And the last group, and finally, the executive user. These
Speaker 2: are your department heads, your decision makers. They want those
Speaker 2: high level dashboards clear quick KPIs.
Speaker 1: Like traffic lights green, yellow, red.
Speaker 2: Pretty much green for good, red for caution. They need
Speaker 2: to see the overall health of their business unit quickly,
Speaker 2: without needing to dive into all the technical details. POWERBI
Speaker 2: really caters to all these different roles.
Speaker 1: That makes perfect sense, a tool for everyone. Really. Okay,
Speaker 1: so we know what powerbi does and who uses it.
Speaker 1: But before we dive deeper into the tool itself, what
Speaker 1: are the fundamental building blocks of business intelligence that powerbi
Speaker 1: RELI lies on? Sort of the underlying concepts?
Speaker 2: Excellent question. Yeah, let's unpack that. Think of it like
Speaker 2: building a house. Your raw materials are your data sets. Okay,
Speaker 2: these come from all sorts of places, old databases, cloud apps,
Speaker 2: maybe excel files, even emails. Sometimes all the raw ingredients, right,
Speaker 2: but those materials aren't ready used just yet. They need
Speaker 2: to be prepared. That's where ETL comes in stands for extract,
Speaker 2: transform load ETL.
Speaker 1: Okay, that sounds like a process, a journey for the data.
Speaker 2: It really is. You extract the raw data from all
Speaker 2: those different sources. Then you transform it. That's where you
Speaker 2: clean it up, maybe aggregated, apply business rules. Think of
Speaker 2: it like washing, chopping and seasoning your ingredients before you.
Speaker 1: Cook, gotcha, cleaning and prepping exactly.
Speaker 2: Finally, you load that cleaned transform data into a target
Speaker 2: system ready for analysis. This whole etl process is crucial
Speaker 2: for building what we call a data warehouse.
Speaker 1: Ah, the data warehouse, I've heard that term. So this
Speaker 1: is where all the good, clean, prepped data lives exactly.
Speaker 2: To warehouse is like your perfectly organized pantry designed specifically
Speaker 2: for cooking up insights. It stores historical data from all
Speaker 2: your operational systems, providing that single version of the truth
Speaker 2: we talked about earlier. This central repository allows for much
Speaker 2: faster queries and analysis. And sometimes you might have a
Speaker 2: data mart, a datamark smaller, yeah, like a smaller specialized
Speaker 2: pantry just for one part of the house, maybe specific
Speaker 2: to the sales department or the marketing team. It holds
Speaker 2: a subset of data, often pulled from the main data warehouse.
Speaker 1: Okay, So once this clean data is in its warehouse
Speaker 1: or mart, how do we organize it for really fast
Speaker 1: and effective analysis. This is where we get into data models.
Speaker 2: Precisely, a data model is essentially the blueprint for your
Speaker 2: data warehouse. It's a pictorial representation of how all your
Speaker 2: data pieces fit together, and importantly, it's designed to make
Speaker 2: data access super fast for reporting.
Speaker 1: Faster access.
Speaker 2: Okay, The main ingredients in these models are dimension tables
Speaker 2: and fact tables.
Speaker 1: Dimensions and facts.
Speaker 2: Yeah, dimensions whole descriptive information, things like a customer's name,
Speaker 2: their address, product categories, mostly text. Factables hold the measurable stuff,
Speaker 2: the numbers you want to analyze, like sales, amounts, quantities, profit,
Speaker 2: things you can sum up or.
Speaker 1: Average, and these lead to that star shape people talk about.
Speaker 2: Yes, the star schema is the most popular model. Imagine
Speaker 2: your fact table with all the numbers sitting right in
Speaker 2: the middle, then surrounding it are all its related dimension tables,
Speaker 2: like points on a star.
Speaker 1: Okay, I can picture that.
Speaker 2: This structure is fantastic for querying large amounts of data
Speaker 2: very quickly because it minimizes the number of complex connections
Speaker 2: or joins the system has to make.
Speaker 1: Makes sense less hopping around for the computer exactly.
Speaker 2: There's also something called the snowflake schema, which is basically
Speaker 2: an extension where some dimensions might have further lookup tables
Speaker 2: branching off them makes it look a bit like a snowflake. Yeah,
Speaker 2: but the star is generally the go to for performance
Speaker 2: in most BI tools, including POWERBI.
Speaker 1: Got it umer for speed. So once we have this
Speaker 1: organized data, how do executives keep tabs on their business's health.
Speaker 1: You mentioned KPI's earlier.
Speaker 2: Right, Key performance indicators or KPIs. Think of your car's
Speaker 2: dashboard again. Fuel gauge, spiometer, engine warning light. Those are
Speaker 2: your car's KPIs telling you it's overall health at a glance.
Speaker 2: For a business, KPIs are those vital numbers like total
Speaker 2: sales growth or customer retention rate that executives use to
Speaker 2: track performance and guide decisions. They're usually specific to a
Speaker 2: department like sales. Them out for the sales.
Speaker 1: Team, and we see these KPIs.
Speaker 2: Through through visualizations. Our brains process pictures much much faster
Speaker 2: than rows and columns of text, so charts, graphs, maps,
Speaker 2: even simple tables help us quickly grasp complex data, spot trends,
Speaker 2: and find patterns that might be hidden otherwise. Visualizations display
Speaker 2: those KPIs effectively.
Speaker 1: Which brings us to the dashboard exactly.
Speaker 2: A dashboard is like that car dashboard. A single page
Speaker 2: visual snapshot combines several key visualizations onto one screen to
Speaker 2: give you an immediate, high level overview of the most
Speaker 2: important KPIs. It's all about quickly seeing the bigger picture.
Speaker 2: But you know you have to be careful that the
Speaker 2: visuals are truly data centric and provide context.
Speaker 1: Good point, not just pretty pictures. Okay, with those foundational
Speaker 1: concepts in place, here's where it gets really interesting. I think,
Speaker 1: how does powerbi itself act as your central hub for
Speaker 1: all this data? What's its superpower in connecting and shaping
Speaker 1: everything we've talked about.
Speaker 2: Well, powerbi is incredibly adept at handling a huge variety
Speaker 2: of data sources, I mean, from ancient legacy systems right
Speaker 2: up to the latest cloud platforms. Okay, it connects them,
Speaker 2: extracts the data, and crucially it often compresses it so
Speaker 2: your reports load much faster. But it's real magic, I think,
Speaker 2: is in its data transformation capabilities.
Speaker 1: The t and etl Right.
Speaker 2: It allows you to clean, combine, and reshape data from
Speaker 2: all those diverse sources into a robust, usable data model,
Speaker 2: joining tables, combining data from excel csvs databases, cloud stuff
Speaker 2: on premises, and handles.
Speaker 1: A lot and it makes it easier for regular users.
Speaker 2: Yeah, that's the goal. It provides an intuitive interface so
Speaker 2: business users can gain insights themselves with self service aggregations
Speaker 2: and visualizations. Plus it enables secure sharing of reports using
Speaker 2: things like that row level security we.
Speaker 1: Mentioned, So it's not really just one single tool, is it.
Speaker 1: It feels more like a collection of specialized tools working together.
Speaker 1: Which ones are the key players within the Powerbi ecosystem.
Speaker 2: That's a great way to put it. The tightly integrated suite.
Speaker 2: You primarily build your reports and models in Powerbi Desktop.
Speaker 2: That's the main authoring toolsktop. Within Desktop, you use power
Speaker 2: Query to connect to literally hundreds of data sources and
Speaker 2: do all your data cleaning and transformation. It's like your
Speaker 2: data prep kitchen.
Speaker 1: Power Career for prepping. Got it?
Speaker 2: Then DAS Data Analysis Expressions is the powerful formula language
Speaker 2: you use inside Powerbi to create complex calculations and measures,
Speaker 2: things that go beyond simple.
Speaker 1: Sums calculation engine pretty much.
Speaker 2: And once your report is polished in desktop, you publish
Speaker 2: and share it via the Powerbi Service, which is the
Speaker 2: online cloud based part. There are other pieces like Powerbi
Speaker 2: Mobile and an on premises server option, but Desktop, Query,
Speaker 2: tax and service are the core workflow.
Speaker 1: So if I'm say a Powerbi developer starting a new project,
Speaker 1: what's my typical journey look like from start to finish?
Speaker 2: Well, a developer's journey U Stull begins with really understanding
Speaker 2: the business needs, talking to stakeholders, figuring out the keykpis,
Speaker 2: maybe looking at existing reports and pain points, understand the
Speaker 2: why exactly. Then it's about getting access to the data,
Speaker 2: understanding the sources that tables, the relationships that you connect,
Speaker 2: extract and start cleaning and transforming that data in power Query.
Speaker 2: Following best practices for data modeling is key here.
Speaker 1: Building that solid foundation right.
Speaker 2: Then you build the visualizations, maybe create some mockups first
Speaker 2: to set expectations. You're often showing previews to users getting feedback. Finally,
Speaker 2: the final steps are publishing the report to the powerbi
Speaker 2: service or report server and setting up scheduled data refreshes
Speaker 2: so the insight state current.
Speaker 1: That power Query editor you mentioned earlier sounds absolutely critical
Speaker 1: for cleaning up messy data. What are some of the
Speaker 1: most common maybe aha moments people have when they start
Speaker 1: using it for transformations?
Speaker 2: Oh, the query editor is definitely where a lot of
Speaker 2: magic happens. It shows you all your connected data sources
Speaker 2: on the left, a preview of your data in the middle,
Speaker 2: and crucially, it records every single transformation step you make
Speaker 2: in an applied steps.
Speaker 1: List on the right, so you can undo things easily exactly.
Speaker 2: Or tweak a step later. It's non destructive.
Speaker 1: Some key transformations well, changing data types is fundamental making
Speaker 1: sure a column that looks like a number, say a
Speaker 1: postal code, is actually treated as text so powerbi doesn't
Speaker 1: try to sum it up.
Speaker 2: Ah Yeah, that would be bad.
Speaker 1: Or making sure an ID field isn't automatically summarized. Another
Speaker 1: huge aha moment is unpivoting columns. Imagine you get data
Speaker 1: from an old report where years are spread across columns
Speaker 1: like twenty fifteen sales, twenty sixteen sales, twenty seventeen sales. Yeah.
Speaker 2: Seen that format not great for analysis.
Speaker 1: Not at all. Unpivoting transforms that messy cross tab data
Speaker 1: into a clean, normalized format maybe customer ID, year, sales
Speaker 1: amount columns perfect for analysis. Power query makes that transformation
Speaker 1: incredibly easy.
Speaker 2: That sounds really useful. What else? You can easily split
Speaker 2: columns by a delimiter, Like if you have a product
Speaker 2: code AA one zero three five time you can instantly
Speaker 2: split it into two columns AA and one oh three
Speaker 2: fifteen based on the hyphen and group by is invaluable.
Speaker 2: Need to quickly check total sales by year or by
Speaker 2: customer to verify things group by lets you do that
Speaker 2: summarization right there in the editor. It really empowers you
Speaker 2: to get your data into the perfect shape before you
Speaker 2: even start building visuals.
Speaker 1: Amazing. So once we've prepared and cleaned our data using
Speaker 1: tower query, the next big step is crafting that robust
Speaker 1: data model. You said this is like the engine room
Speaker 1: of our BI solution.
Speaker 2: It absolutely is. Data modeling is like creating the architectural
Speaker 2: blueprint for your data. It organizes all the different data
Speaker 2: elements your tables and defines how they relate to each other.
Speaker 2: And crucially, it does this considering your business questions and KPIs.
Speaker 1: So it's not just connecting tables randomly definitely not.
Speaker 2: A well designed model is crucial because it feeds clean,
Speaker 2: structured data to your visualizations. This ensures your reports are
Speaker 2: not only accurate, but also perform efficiently. I mean without
Speaker 2: a good model, even the prettiest charts can be slow
Speaker 2: or worse misleading.
Speaker 1: Makes sense. Are there any golden rules or maybe best
Speaker 1: practices for building a strong data model in POWERBI?
Speaker 2: Yeah, there are definitely some key ones. First, as we mentioned,
Speaker 2: always aim for that star schema whenever possible, that central
Speaker 2: fact table surrounded by dimensions. It's simply faster for reporting
Speaker 2: in Powerbi's.
Speaker 1: Engine Star schema first, got it.
Speaker 2: Second, load only the data you actually need. If your
Speaker 2: users only care about the last five years of sales,
Speaker 2: filter out the older data before it even hits the model.
Speaker 2: Don't bring in ten years if only five are required.
Speaker 1: Keep it lean less data faster reports exactly.
Speaker 2: Third, simplify. Reduce the number of tables and relationships where
Speaker 2: you can maybe combined tables if it makes sense, and
Speaker 2: critically hide unnecessary technical fields or intermediate tables from the
Speaker 2: final report view. Keep the view clean for the end users.
Speaker 2: These practices keep your model lean, fast and much easier
Speaker 2: to understand and maintain.
Speaker 1: Okay, lean and clean. And how do we actually connect
Speaker 1: these separate tables like linking customers to their orders? You
Speaker 1: mentioned relationships right.
Speaker 2: Relationships are the glue holding your model together. They work
Speaker 2: by matching data in common key columns between tables. Typically,
Speaker 2: a primary key in one table, like customer ID in
Speaker 2: the customer's table, matches a foreign key in another, like
Speaker 2: customer ID in the order's table.
Speaker 1: And Powerbi helps with this.
Speaker 2: It does. Powerbi is actually quite smart and can often
Speaker 2: auto detect these relationships based on column names, but it's
Speaker 2: vital to review them, maybe refine them, or create them
Speaker 2: manually if needed, to ensure they accurately reflect the real
Speaker 2: world connections in your.
Speaker 1: Data, So you check the autodetected ones. What else defines
Speaker 1: a relationship?
Speaker 2: Two key things cardinality and cross filter direction. Cardinality describes
Speaker 2: how rose in one table relate to rose in another.
Speaker 2: Most common is many to one point one. For instance,
Speaker 2: many orders can belong to one customer. There's also one
Speaker 2: to one point one and many.
Speaker 1: To many okay, And cross filter Cross.
Speaker 2: Filter direction tells power bi how filter should flow between
Speaker 2: your connected tables. Should filtering the customer's table also filter
Speaker 2: the order's table or should it work both ways or
Speaker 2: only one way? You control that flow?
Speaker 1: Got it controlling the filter flow? Now?
Speaker 2: What off?
Speaker 1: My data is spread across several different files or tables,
Speaker 1: and I actually need to combine them into one, like
Speaker 1: merge them.
Speaker 2: For that, you'll typically use two powerful tools back in
Speaker 2: the Power Query Editor merge queries and a pen queries
Speaker 2: Merge in a pen Okay. Merge queries is essentially like
Speaker 2: doing a sequel join. It combines columns from two table
Speaker 2: based on matching values in a common field. For example,
Speaker 2: you might have your main orders table, but the quantity
Speaker 2: and unit price are in a separate order details excel file.
Speaker 2: You can merge these using the order ID to bring
Speaker 2: quantity and unit price into your main orders table.
Speaker 1: Ah, so you pull data across based.
Speaker 2: On a match exactly, and then you can often hide
Speaker 2: the original order details table from the report view to
Speaker 2: simplify your final model. Merge supports all the standard joint
Speaker 2: types left out or inner, right, outer, et cetera.
Speaker 1: Okay, and append queries? How is that different?
Speaker 2: A pen queries is different? More like a SQL union.
Speaker 2: It combines two tables that have the exact same columns
Speaker 2: and structure by stacking the rows one on top of
Speaker 2: the other.
Speaker 1: Stacking rows like adding more data precisely.
Speaker 2: So if you get a new file each month with
Speaker 2: new customer records and it has the same columns as
Speaker 2: your main customer's table, you can append the new files
Speaker 2: data to your existing table, effectively adding more rows. The
Speaker 2: key here is making sure the column headers and data
Speaker 2: types match up perfectly before you append.
Speaker 1: That makes sense merge for adding a pen for adding rows.
Speaker 1: So what does this all mean for our listener building reports.
Speaker 2: It means that a well crafted data model using these
Speaker 2: best practices for relationships, using mergent dependent smartly is really
Speaker 2: the silent hero of a great POWERBI report. It dramatically
Speaker 2: reduces complexity behind the scenes, it boosts application performance, and fundamentally,
Speaker 2: it ensures that your visualizations are built on a rock solid,
Speaker 2: accurate foundation. This is how you truly achieve and trust
Speaker 2: that single version of the truth.
Speaker 1: That's a fantastic blueprint for our data. Now let's shift
Speaker 1: gears and talk about the calculations, the part that truly
Speaker 1: brings data to life and unlocks those deeper insights DAX
Speaker 1: data analysis expressions. How does this powerful formula language work?
Speaker 2: Its magic Ah, DAX, Yeah, you'd say. DAX is Powerbi's
Speaker 2: secret superpower for calculations. The formula language kind of like
Speaker 2: Excel formulas, but much more powerful, especially for analytics. It
Speaker 2: allows you to create new, dynamic metrics and calculations that
Speaker 2: aren't directly sitting there in your raw data tables.
Speaker 1: So going beyond just summing a column.
Speaker 2: Way beyond DAX lets you calculate things like you're over
Speaker 2: year growth, running total sales profit margin for the top
Speaker 2: ten products, or customer account for new customers this month.
Speaker 2: It's how you get answers to more sophisticated business questions
Speaker 2: directly within your reports.
Speaker 1: Okay, and I hear there's a really important distinction here,
Speaker 1: one that's often a big aha moment for learners. Calculated
Speaker 1: columns versus calculated measures. Can you break that down for us?
Speaker 1: Why is this difference so fundamental?
Speaker 2: This is absolutely critical? Yeah, understanding this is key. Think
Speaker 2: of calculated columns. First, When you create a calculated column,
Speaker 2: it physically adds a new column to your table in
Speaker 2: the data.
Speaker 1: Model, like adding a column in Excel kind of.
Speaker 2: Yes, it's calculated once for every single row in that
Speaker 2: table when you define it or when your data set
Speaker 2: is refreshed. So if you wrote uniprice order sales orders quantity,
Speaker 2: it would calculate and store that unit price for every
Speaker 2: single sales transaction line.
Speaker 1: Okay, stored for every row. It's the downside.
Speaker 2: The downside is it increases your data model size, sometimes
Speaker 2: significantly because you're storing all those calculated values. This consumes
Speaker 2: more memory ram and can slow things down. Do you
Speaker 2: see them in your table view with a little column
Speaker 2: icon right?
Speaker 1: Increase size uses more memory? So how are calculated measures different?
Speaker 2: Then measures are totally different. They're truly dynamic. They are
Speaker 2: calculated on the fly, in real time, based on whatever
Speaker 2: context you've applied in your report, like filters in a
Speaker 2: chart or rows in a table.
Speaker 1: Visual calculated when needed exactly.
Speaker 2: They do not increase your data model size or RAM
Speaker 2: because the results aren't stored physically row bi row in
Speaker 2: the model. They typically use aggregation functions like SEM average count.
Speaker 2: So a measure like total sales equals SEM order sales
Speaker 2: only calculates when you actually drag total sales onto a visual,
Speaker 2: and its value adapts instantly to whatever filters are active,
Speaker 2: like year, region, product ah.
Speaker 1: So they're much more lightweight and.
Speaker 2: Flexible immensely so, measures are almost always preferred for aggregations, ratios, percentages,
Speaker 2: and complex business logic because they respond to the user's interaction.
Speaker 2: They appear with a little calculator icon in your field list,
Speaker 2: and you won't see the results in the raw data
Speaker 2: table view only in visuals. It's like asking a specific
Speaker 2: question what were sales for the East region last year
Speaker 2: and getting the answer right then, rather than having every
Speaker 2: possible answer pre calculated and stored.
Speaker 1: For every row that is a huge difference. It explains
Speaker 1: why sometimes reports can feel slow if maybe someone used
Speaker 1: too many calculated columns instead of measures.
Speaker 2: It's often a big factor.
Speaker 1: Yes, okay, DAX sounds powerful, but maybe a bit intimidating.
Speaker 1: Can you give us a quick taste of how DAX
Speaker 1: tackles some common business calculations or problems, maybe some key functions.
Speaker 2: Sure, let's start with aggregations. You have your basic SOM
Speaker 2: average count, but often you need to perform a calculation
Speaker 2: row by row first, then aggregate the result. For that
Speaker 2: you use iterator functions ending in x, like sumx sumx.
Speaker 1: How's that different.
Speaker 2: From s Okay, imagine you want total profit. Profit isn't
Speaker 2: a column? Profit is a quantity unit price cost for
Speaker 2: each order line. Sum can't do that multi step calculation,
Speaker 2: but sums can iterate through your order's table, calculate the
Speaker 2: profit for each row and then sum up those individual
Speaker 2: row profits. It's essential for row level math, followed by aggregation.
Speaker 1: Got it iterate first, then some What about controlling the context?
Speaker 1: That sounds powerful.
Speaker 2: That brings us to arguably the most important function in.
Speaker 1: All of daks, calculate, calculate.
Speaker 2: Calculate is magical Let's evaluate a DAX expression like belie
Speaker 2: or sales, but temporarily change the filter context in which
Speaker 2: it's evaluated. So if your chart is showing sales by region,
Speaker 2: but you want to measure that always shows the total
Speaker 2: sales for the year twenty eighteen, regardless of the region selected.
Speaker 2: Calculate lets you override the existing region filter and apply
Speaker 2: a new filter for just year twenty eighteen. It's the
Speaker 2: key to comparisons, time, intelligence, and so much more. Wow.
Speaker 1: Okay, So let's you rewrite the rules for a specific
Speaker 1: calculation precisely.
Speaker 2: And related to that is all ill often used to
Speaker 2: inside calculate all removes existing filters. So calculate somes all
Speaker 2: regions would give you the grand total sales across all regions.
Speaker 2: Useful for calculating percentages of total.
Speaker 1: Okay, calculate and all work together. What about getting data
Speaker 1: from related tables without merging them.
Speaker 2: For that, you often use functions like related or look
Speaker 2: up value. If you're working in your order's table, which
Speaker 2: has many orders per customer, and you need the customer
Speaker 2: name from the related customer's table, which has one row
Speaker 2: per customer, related customer's customer name can pull that value
Speaker 2: across the many to one relationship. It needs row context,
Speaker 2: so it works great in calculated columns or inside iterators
Speaker 2: like CMMX. Look up value is similar but more flexible,
Speaker 2: like v looakup in Excel.
Speaker 1: That sounds really useful for enriching data without cluttering the model.
Speaker 1: What about basic conditional logic like f statements.
Speaker 2: If DAX has I, just like Excel, I have conditioned
Speaker 2: value true, value false, you can nest them, but it
Speaker 2: gets messy fast.
Speaker 1: Yeah. Nested ives are painful.
Speaker 2: Exactly, which is why the switch function is often much better.
Speaker 2: Switch lets you evaluate an expression once and then list
Speaker 2: pairs of results and values like switch month order date one,
Speaker 2: Jan two, fab three, other, much cleaner than multiple nested
Speaker 2: eyes for creating categories or handling multiple conditions.
Speaker 1: Switchach sounds like a life saver, So pulling it all together.
Speaker 1: What does this mean for someone learning power BI and DAX.
Speaker 2: It means that while DAX definitely has a learning curve,
Speaker 2: getting your head around these core concepts, especially that crucial
Speaker 2: difference between calculated columns and measures, and understanding the power
Speaker 2: of iterators like sumx and context modifiers like calculate will
Speaker 2: fundamentally transform how you can analyze your data. It really
Speaker 2: moves you beyond just basic reporting of what's already there
Speaker 2: into dynamically asking and answering much more complex and valuable
Speaker 2: business questions. Practice is key, but the payoff is huge powerful.
Speaker 1: Indeed, Okay, we've built our data model, We've crafted our
Speaker 1: sophisticated DAX calculations. Now this is where it all comes together,
Speaker 1: visually bringing our data in to focus with visualizations. This
Speaker 1: is what the end user sees, right.
Speaker 2: Absolutely, visualizations are arguably the most important part for the
Speaker 2: consumer because that's how they actually gain insights quickly. But
Speaker 2: it's worth repeating the best, most insightful visuals are only
Speaker 2: as good as the clean, well modeled data and accurate
Speaker 2: DAX calculations that underpin them. Garbage in, pretty garbage out.
Speaker 1: Good point quality foundations matter.
Speaker 2: Now let's clear up something that often confuses people. Reports
Speaker 2: versus dashboards and Powerbi. Are they the same thing? No,
Speaker 2: they're definitely not, and it's a crucial distinction to understand.
Speaker 2: Reports in Powerbi can be multipage. They are created primarily
Speaker 2: in Powerbi desktop, though you can edit or create them
Speaker 2: in the service too. Reports are highly interactive. Think filters slicers,
Speaker 2: cross highlighting, drill downs. They're designed for detailed exploration and
Speaker 2: analysis of a single data.
Speaker 1: Set, multipage interactive, single data set. Okay, how are dashboards different?
Speaker 2: Dashboards, on the other hand, are always single page canvases.
Speaker 2: They're created only in the power Biel service, not desktop.
Speaker 2: And here's the key. They're built by pinning individual visualizations
Speaker 2: or even entire report pages from one or more underlying reports.
Speaker 2: They generally don't have the same interactive filtering capabilities as reports.
Speaker 2: Think of a dashboard as that high level, at a glance,
Speaker 2: executive summary or monitoring view, often combining key visuals from
Speaker 2: different reports and potentially different data sets onto one screen.
Speaker 1: Okay, so reports for exploring, dashboards for monitoring. Got it.
Speaker 1: So when we're actually building these reports in power via desktop,
Speaker 1: what are the main elements we're working with on the screen.
Speaker 2: Right, when you're in the report view, you have your
Speaker 2: main report canvas. That's the big white space where you
Speaker 2: range your visuals. You can have multiple pages. Here to
Speaker 2: the right, you typically have three key pains.
Speaker 1: Three pains, Yeah.
Speaker 2: The visualization's pain. That's where you select your chart type
Speaker 2: like a bar chart or a line chart. Below that
Speaker 2: You can figure the fields for the selected visual dragging
Speaker 2: data into access, legend, values, wells, etc. Then the field's pain,
Speaker 2: which lists all the tables and fields available in your
Speaker 2: data model. You drag things from here onto your visuals
Speaker 2: or the canvas, and finally the filter pain. This is
Speaker 2: essential for controlling what data your visuals display. You can
Speaker 2: apply filters at the visual level, the page level, or
Speaker 2: even across the entire report.
Speaker 1: Filters are key. Can you give us a few examples
Speaker 1: of some core visualization types and what they're best used for?
Speaker 2: Absolutely, For displaying a single critical number like total sales
Speaker 2: YTD or number of active customers, a card visual is perfect, simple, clear,
Speaker 2: high impact, just the big number exactly. For comparing values
Speaker 2: across different categories, like sales by product category or marketing
Speaker 2: spend by channel, a bar chart horizontal bars or a
Speaker 2: column chart vertical bars is your classic go to. You
Speaker 2: can stack them too to show parts of a whole
Speaker 2: within each category.
Speaker 1: Bar and column charts the workhorses. What about trends?
Speaker 2: For showing trends over time, this is crucial, like monthly
Speaker 2: sales perform rmans or website visits per day. A line
Speaker 2: chart is almost always the best choice. It clearly shows
Speaker 2: the progression and fluctuations over a continuous period.
Speaker 1: Okay, lines for time. What about seeing how parts make
Speaker 1: up a hole like market share for.
Speaker 2: Showing how parts contribute to a hole, A donut chart
Speaker 2: or a pie chart works well, especially if you have
Speaker 2: only a few categories, for example showing percentage of total
Speaker 2: profit contributed by each business segment. Though use pie charts
Speaker 2: with caution if you have too many.
Speaker 1: Slices right, they can get messy.
Speaker 2: A tree map is often better for that, especially with
Speaker 2: hierarchical data. It displays data as nested rectangles, where the
Speaker 2: size of each rectangle reflects a measure value like sales
Speaker 2: by subcategory within category. What's great about tree maps and
Speaker 2: many other visuals is how they can cross filter or
Speaker 2: cross highlight other charts on the same page when you
Speaker 2: click on a segment. Creates a very dynamic exploratory.
Speaker 1: Experience interactive filtering. Nice. What about location data.
Speaker 2: If you have geographical data Powerbi's map visuals are fantastic.
Speaker 2: You can show data points as bubbles on a map
Speaker 2: where the size reflects a measure like count of customers
Speaker 2: by city, or use a filled map also called a
Speaker 2: coropleth where entire regions like states or countries are shaded
Speaker 2: based on a value like sales per capita by state,
Speaker 2: really brings geographic patterns to life.
Speaker 1: Maps are always eye catching. What about more dynamic ways
Speaker 1: for users to explore beyond just clicking on predefined visuals.
Speaker 2: Two really powerful features here, First the Q and a
Speaker 2: question and answer visual. This literally lets users type questions
Speaker 2: in plain English like show me top five customers by
Speaker 2: profit in the West Region last quarter, and power Bi
Speaker 2: attempts to understand the question and generate the appropriate visual
Speaker 2: response on the fly.
Speaker 1: Wow, natural language queries.
Speaker 2: That's impressive, it really is. And the second is drill
Speaker 2: through reports. This is super useful. Imagine you have a
Speaker 2: summary page showing total sales by customer. You can set
Speaker 2: up a drill through action so when a user write
Speaker 2: clicks on a specific customer on that summary chart, they
Speaker 2: can jump to a separate hidden detail page that shows
Speaker 2: all the underlying order details just for that selected customer.
Speaker 2: Powerbi even automatically adds a little back button to navigate
Speaker 2: back easily. It allows for that summary review first, then
Speaker 2: details on demand.
Speaker 1: Summary to details. That's very slick. Okay, we've built these
Speaker 1: incredible interactive reports, maybe with drill throughs and natural language
Speaker 1: Q and A. Now it's time to share those insights
Speaker 1: with colleagues or clients. This brings us to the Powerbi service,
Speaker 1: the cloud hub you mentioned earlier exactly.
Speaker 2: The Powerbi service sometimes called Powerbi Online, is where your
Speaker 2: reports generally go to live and be shared. It's a
Speaker 2: cloud based platform, software as a service or sauce that
Speaker 2: acts as the central place to publish, share, collaborate on,
Speaker 2: and consume reports and dashboards across your organization.
Speaker 1: So desktop is for building services for sharing and collaborating.
Speaker 1: What's the typical workflow look like once you're ready to
Speaker 1: move from desktop to the service.
Speaker 2: Yeah, the process is usually quite seamless. You create your
Speaker 2: data model and design your reports primarily a Powerbi desktop.
Speaker 2: Once they're polished and ready, you simply publish them from
Speaker 2: desktop up to the Powerbi service just click button pretty much.
Speaker 2: Once published, users with the right permissions can then access
Speaker 2: and consume those reports through their web browser in the service.
Speaker 2: They might also be able to modify reports there or
Speaker 2: even create new reports based on the published data set
Speaker 2: directly in the service, and critically, the service is where
Speaker 2: you create those single page dashboards by pinning key visualizations
Speaker 2: from your various reports.
Speaker 1: So the dashboards are built in the service using pieces
Speaker 1: from the reports built in desktop.
Speaker 2: That's the most common pattern. Yes, you build the detailed
Speaker 2: reports in desktop, publish them, then curate the high level
Speaker 2: dashboard view in the service by pinning the most important visuals.
Speaker 1: And how easy is it to actually get your work
Speaker 1: from your local desktop application into the cloud service.
Speaker 2: It's incredibly straightforward. Assuming you're signed into your powerbi account.
Speaker 2: Within the desktop application, you just go to the home
Speaker 2: ribbon and click the published button.
Speaker 1: Okay.
Speaker 2: It'll ask you to choose a destination workspace in the service,
Speaker 2: maybe your personal my workspace or a shared team workspace.
Speaker 2: You select it, click publish, and Parabia handles the rest,
Speaker 2: uploading both your report file dot pbx and it's underlying
Speaker 2: data set to the cloud. You then just navigate to
Speaker 2: that workspace in your web browser to see it.
Speaker 1: Sounds simple enough, okay. One final, but truly critical aspect
Speaker 1: for any business using data security. Once reports are published
Speaker 1: in the service, how do we ensure that only the
Speaker 1: right people see the right data, especially sensitive data?
Speaker 2: That is absolutely crucial and the primary mechanism for handling
Speaker 2: this within Powerbi is Row level Security, often abbreviated.
Speaker 1: As ROLS Row level Security RLS. Its core objective is
Speaker 1: pretty simple, restrict data access for specific users based on
Speaker 1: defined roles. So, going back to our sales example, if
Speaker 1: you have a sales manager for the East region and
Speaker 1: another for the West, RLS ensures that when the East
Speaker 1: manager logs in and looks at the company sales report,
Speaker 1: they only see the data rose pertaining to the East region.
Speaker 1: The West manager, looking at the exact same report, would
Speaker 1: only see West region data.
Speaker 2: So the same report shows different data depending on who's looking.
Speaker 2: That sounds powerful, but maybe complicated to set up.
Speaker 1: It sounds complicated, but it's actually surprisingly manageable to implement.
Speaker 1: In Powerbi, the process generally involves a few key steps,
Speaker 1: mostly done back in powerbi Desktop before.
Speaker 2: You publish, okay, what are the steps?
Speaker 1: First, obviously, in need your reports built the visuals that
Speaker 1: will display the data you want to secure. Second, in
Speaker 1: desktop's modeling ribbon, you go to managed roles. Here you
Speaker 1: define your specific security roles. You just give them names
Speaker 1: like salesperson East or salesperson West.
Speaker 2: Define the roles got it. Third, for each role you
Speaker 2: create you are at a simple DAX filter expression on
Speaker 2: the relevant table. For the salesperson East role, you might
Speaker 2: filter the customer's table with the DAX expression like region
Speaker 2: eg East. This tells Powerbi anyone assigned to this role
Speaker 2: can only see rose where the region column is east.
Speaker 2: Need to be careful with spelling and care.
Speaker 1: Here, of course, a dackx filter for each role.
Speaker 2: Okay, fourth and this is really neat. You can test
Speaker 2: these roles right there in desktop using the view as option.
Speaker 2: Also on the modeling ribbon, you can simulate logging in
Speaker 2: as someone in the salesperson East role and instantly see
Speaker 2: if you report visuals filter down correctly. Lets you verify
Speaker 2: before publishing, test it first.
Speaker 1: Smart What happens after publishing good question.
Speaker 2: The roles and their dackx filters are published with the
Speaker 2: data set to the Powerbi service. The final step happens
Speaker 2: in the service. You navigate to the settings for the
Speaker 2: published data set, find the security option, and this is
Speaker 2: where you assign actual users or active directory of security
Speaker 2: groups from your organization to the roles you created in desktop.
Speaker 2: You map the real people to the abstract.
Speaker 1: Roles, assign users to roles in the service.
Speaker 2: Exactly Then when a user logs into powerbi service and
Speaker 2: opens the report, Powerbi implicitly checks which roles they belong
Speaker 2: to using functions like username. Behind the scenes applies the
Speaker 2: corresponding DAX filters, and they only see the slice of
Speaker 2: data they're authorized for. You should test it in the
Speaker 2: service too, just to be sure.
Speaker 1: So wrapping up ROLS, what does this all mean for
Speaker 1: delivering secure insights?
Speaker 2: It means RLS is a fundamental and relatively easy to
Speaker 2: implement feature in powerbi. It empowers organizations to confidently deliver
Speaker 2: highly contextual and secure insights to a broad audience. You
Speaker 2: can share one report but trust that different users will
Speaker 2: see only their relevant part of the picture, ensuring data
Speaker 2: governance without sacrificing the power and flexibility of self service analytics.
Speaker 2: It's really key to making that single version of the
Speaker 2: truth trustworthy and usable across the board.
Speaker 1: Fantastic, What an incredible journey we've taken today, Seriously, from
Speaker 1: understanding the very foundations of business intelligence and powerbis powerful components,
Speaker 1: right through the intricate steps of data modeling and the
Speaker 1: well sometimes mind bending magic of DAX calculations, all the
Speaker 1: way to crafting those dynamic visualizations and crucially securely sharing
Speaker 1: your insights using the powerbi service and ROLS. I truly
Speaker 1: hope this deep dive has given you, our listener, a
Speaker 1: robust framework for understanding and leveraging powerbi. Think of it
Speaker 1: as a shortcut to being genuinely well informed in this
Speaker 1: really critical area.
Speaker 2: Indeed, and maybe a final thought to leave folks with.
Speaker 2: As we've explored how powerbi connects, transforms, visualizes and secure
Speaker 2: as data, consider this. The true power of these tools
Speaker 2: isn't just in presenting the truth as the data shows it,
Speaker 2: but perhaps in democratizing the ability for more people to
Speaker 2: ask sophisticated questions.
Speaker 1: Of their data, democratizing questions.
Speaker 2: I like that? Yeah, So, how might a deeper understanding
Speaker 2: of your own data using tools like this lead you
Speaker 2: to question assumptions you've maybe held for a long time
Speaker 2: about your business, your work, or even the world around you.
Speaker 2: What new questions could you ask?
Speaker 1: I truly thought provoking question to end on, We absolutely
Speaker 1: encourage you to explore powerbi further. If this has piqued
Speaker 1: your interest, perhaps even try out some of the concepts
Speaker 1: we've discussed today, and definitely continue your own learning journey
Speaker 1: into the world of data