Speaker 1: Hey everyone, and welcome to the deep dive today. We're
Speaker 1: diving into POWERBI and dax Ooh, POWERBI and dax it's
Speaker 1: pretty popular, right, Yeah, it's.
Speaker 2: A really powerful combo.
Speaker 1: It's changing the way we look at data and analyze
Speaker 1: data definitely. So you gave us some awesome resources to
Speaker 1: help guide our deep dive today, including excerpts from this
Speaker 1: book called POWERBI, moving beyond the basics of POWERBI and
Speaker 1: learning more about decks. Okay, so, obviously, respecting copyright, we're
Speaker 1: using all this material for purely educational purposes, of course,
Speaker 1: But let's just jump right in. What's the big deal
Speaker 1: with POWERBI?
Speaker 2: So POWERBI is so popular because it makes data analysis
Speaker 2: accessible Okay, So it's user friendly, got it even for
Speaker 2: people who aren't, you know, like data scientists or anything.
Speaker 1: Right, So like a drag and drop interface I've heard
Speaker 1: about that exactly, to make it easy to create visualization.
Speaker 1: It's almost like building with digital legos.
Speaker 2: That's a good analogy.
Speaker 1: But the real magic happens when you combine that with
Speaker 1: DAX right, the language behind.
Speaker 2: It exactly, that's the secret sauce.
Speaker 1: That lets you do those complex calculations and really dig
Speaker 1: into the data. Okay, before we get into the decks,
Speaker 1: can you give us a quick overview of the powerbi ecosystem.
Speaker 2: Sure, the powerbi ecosystem has well. There are a few
Speaker 2: different components. You've got the powerbi desktop is where you
Speaker 2: build your reports and visualizations at it. And then there's
Speaker 2: the Powerbi service, which is the online platform where you
Speaker 2: share your work and you can collaborate. And then there's
Speaker 2: also mobile apps. Oh cool, So you can access your
Speaker 2: data from anywhere.
Speaker 1: So it's like this like a whole data analysis studio exactly,
Speaker 1: from creation to collaboration and on the go access.
Speaker 2: Yep.
Speaker 1: All right, and I know that powerbi can connect to
Speaker 1: a ton of different data sources. What are some of
Speaker 1: the possibilities there.
Speaker 2: That's one of the things that's so great about it.
Speaker 2: You can connect to data from pretty much anywhere. Oh wow, spreadsheets, databases,
Speaker 2: cloud services, you name it.
Speaker 1: So like a melting pot. Yeah, from all these sources.
Speaker 1: That's amazing.
Speaker 2: Yeah. And there's two main ways to connect to data,
Speaker 2: import and direct query.
Speaker 1: Okay, let's unpack these two different methods. Imagine there's some
Speaker 1: trade offs with each one.
Speaker 2: So with import, yeah, you're bringing a copy of the
Speaker 2: data into power Bi Okay, So you have a lot
Speaker 2: more flexibility to shape and manipulate the data.
Speaker 1: Got it.
Speaker 2: But it's a snapshot in time.
Speaker 1: Okay.
Speaker 2: So if the original data changes, you need to refresh
Speaker 2: your power bi data set.
Speaker 1: Okay. So it's like having a local copy of a
Speaker 1: file exactly if you can make all the edits you
Speaker 1: want without affecting the original uh huh, got it? What
Speaker 1: about direct query?
Speaker 2: Direct query is more like a live feed to the
Speaker 2: data source.
Speaker 1: Got it.
Speaker 2: So it maintains that constant connection. You're always working with
Speaker 2: the most up to date information.
Speaker 1: That makes sense.
Speaker 2: But there's a trade off.
Speaker 1: Okay.
Speaker 2: You have more limited ability to manipulate the data within Powerbi,
Speaker 2: and it might not be the best for huge data
Speaker 2: sets okay, because it can impact performance.
Speaker 1: So it's a balancing act between those two. If you
Speaker 1: need real time direct query, but if you need the flexibility.
Speaker 2: Exactly import okay.
Speaker 1: So it all comes down to choosing the right tool
Speaker 1: for the job. Before we even start building out our visualizations,
Speaker 1: there are a few key concepts you understand.
Speaker 2: So the book highlights data sets, okay, visualizations, reports, and dashboards.
Speaker 1: Okay, So let's break those down. Okay, what are data
Speaker 1: sets in Powerbi.
Speaker 2: Data sets are collections of data that Powerbi uses to
Speaker 2: create visualizations and reports.
Speaker 1: Okay, So like gathering the ingredients before you start cooking,
Speaker 1: you've got to have all the data sets prepped and
Speaker 1: ready to go, right exactly. And once you have your
Speaker 1: data sets, you can start creating visualizations the visual representation
Speaker 1: of that data.
Speaker 2: And there's tons of different options.
Speaker 1: This is where it gets fun, right, Yes, bar charts,
Speaker 1: pie charts, all that good stuff, bringing the data to life.
Speaker 2: It makes the data much easier to understand when you
Speaker 2: can see it visually.
Speaker 1: So visualizations are like the individual snapshots of the data, right,
Speaker 1: But how do we combine those snapshots into a bigger picture.
Speaker 2: That's where reports come in. Reports are collections of visualizations
Speaker 2: that tell a data story, got it. It could be
Speaker 2: single pages, multiple pages, okay, but they are a way
Speaker 2: to arrange your visualizations.
Speaker 1: Okay. So if visualizations are the pieces of the puzzle,
Speaker 1: a report is the completed puzzle, showing the full picture.
Speaker 2: Exactly I like it. And then there's one more piece, yeah, dashboards.
Speaker 2: Dashboards they're like the executive summaries you can put your
Speaker 2: most important visualizations, okay, or key performance indicators. Got it
Speaker 2: designed for high level monitoring.
Speaker 1: So if the report is the full story, yeah, this
Speaker 1: is the highlight reel.
Speaker 2: It's exactly.
Speaker 1: This is the key takeaways that you want everyone to see, exactly,
Speaker 1: So present the most relevant information in a concise and
Speaker 1: appealing way. Yes, it's pretty powerful.
Speaker 2: It is.
Speaker 1: You can collect data from these various sources, transform it
Speaker 1: into these insights, and then present it in a way
Speaker 1: that's informative and engaging.
Speaker 2: Exactly.
Speaker 1: This is awesome. We've covered so much already. Oh yeah,
Speaker 1: the components of POWERBI, the different connection methods, visualizations, reports,
Speaker 1: and dashboards. I have a feeling we're just getting started.
Speaker 2: We are just getting started. Now we're going to get
Speaker 2: into the real fun stuff.
Speaker 1: Ooh okay, Das all right, daks.
Speaker 2: The language that unlocks the true power of POWERBI.
Speaker 1: That's what I'm waiting for, Yeah, to unlock the true
Speaker 1: power create those custom calculations and visualizations that take data
Speaker 1: analysis to the next level. Absolutely, I'm ready to get
Speaker 1: my hams dirty with Das.
Speaker 2: All right, welcome back to our deep dive into POWERBI
Speaker 2: and DAX.
Speaker 1: It really feels like we've just explored this vast landscape
Speaker 1: of POWERBI and now we're ready to like build something
Speaker 1: amazing with DAK as our toolkit.
Speaker 2: It is like a tool.
Speaker 1: I'm ready to roll up my sleeves and see what
Speaker 1: this DAX toolkit can do.
Speaker 2: Let's do it.
Speaker 1: Where should we start?
Speaker 2: So the book dives into some key categories of DAX functions, okay,
Speaker 2: like aggregate functions, filter functions, and time intelligence functions.
Speaker 1: Okay, so those are kind of like the workhourses of DAKS.
Speaker 2: Yeah, there are the workhourses.
Speaker 1: That let you perform calculations and filter data and analyze
Speaker 1: trends over time exactly. Okay, that sounds like a pretty
Speaker 1: logical place to start. Let's start with aggregate functions.
Speaker 2: Okay.
Speaker 1: Are these similar to the functions that we might use
Speaker 1: and Excel like SU and average.
Speaker 2: They are, but there's a key difference. In Excel, you're
Speaker 2: applying functions to individual cells or ranges of cells, okay,
Speaker 2: but in DAKS you apply them to entire columns or
Speaker 2: tables of data.
Speaker 1: Ah. That makes sense because we're dealing with these much
Speaker 1: larger data sets empower BI, so we need these functions
Speaker 1: that can handle that kind of scale. Right, Can you
Speaker 1: give me an example of how DA's aggregate function works
Speaker 1: in practice.
Speaker 2: Sure. Let's say you're analyzing sales data, okay, and you
Speaker 2: have a column called sales amount.
Speaker 1: Got it?
Speaker 2: To calculate the total sales for all transactions, okay, you
Speaker 2: would use the im function in DAX and you'd apply
Speaker 2: it to that entire sales amount column.
Speaker 1: So instead of adding up the sales one by one,
Speaker 1: we're just telling DAX sem the whole column. That's efficient.
Speaker 1: And what if we wanted to calculate like the average
Speaker 1: sale instead of the.
Speaker 2: Total, then you would use the average function, okay, and
Speaker 2: you would apply it to the entire sales amount column.
Speaker 1: Got it? So some and average are pretty straightforward. Yeah,
Speaker 1: but what about like finding the highest or lowest sales values?
Speaker 2: So for that, you would use the max function to
Speaker 2: return the largest value or the min function to get
Speaker 2: the smallest value.
Speaker 1: Okay, so we have max and min. I'm starting to
Speaker 1: see a pattern here.
Speaker 2: Yeah.
Speaker 1: These DAX functions have these really fuitive names. Do that
Speaker 1: make them pretty easy to understand?
Speaker 2: Yeah? DAX is meant to be accessible, Okay, even for
Speaker 2: people who aren't programmers.
Speaker 1: I like that. But don't let the simplicity fool you, right,
Speaker 1: because DAX can handle incredibly complex calculations. It can, all right,
Speaker 1: I'm eager to see how far we can push that. Now,
Speaker 1: we've talked about calculating sums, averages, maximums, minimums. Are there
Speaker 1: any other aggregate functions that we should be aware of?
Speaker 2: Yeah, there are tons. There's functions for counting distinct values,
Speaker 2: counting grows in a table, and even calculating statistical measures
Speaker 2: like standard deviation and variance.
Speaker 1: So we can get really granular with our data analysis. Again,
Speaker 1: using these aggregate functions, it's like having a statistical Swiss
Speaker 1: Army knife. It is at our disposal.
Speaker 2: Yeah.
Speaker 1: But sometimes we need to do more than just calculate summaries.
Speaker 2: Right, Sometimes we need to filter our data, yes, to
Speaker 2: focus on like specific subsets or conditions. It's where filter
Speaker 2: functions come in.
Speaker 1: That's where they come in.
Speaker 2: It's like using a search engine to refine our results. Yeah,
Speaker 2: we only want to see data that meets our criteria exactly.
Speaker 2: So how do we accomplish that? In DAX.
Speaker 1: One of the most powerful filter functions is calculate Okay.
Speaker 1: It allows you to modify the filter context of a calculation.
Speaker 2: Okay, So essentially we're telling DAX to perform a calculation,
Speaker 2: but only on a subset of the data, right that
Speaker 2: meets a certain condition.
Speaker 1: Exactly.
Speaker 2: You give me concrete example.
Speaker 1: Sure. So let's say we want to analyze sales for
Speaker 1: a specific product category okay, like electronics. Okay, electronics, So
Speaker 1: we would use calculate to sum the sales amount column,
Speaker 1: but only for transactions where the product category column equals electronics.
Speaker 2: Ah. Okay, So we're using calculate to kind of zoom
Speaker 2: in a particular segment of our data.
Speaker 1: Right, that's super helpful. What other filter functions should we
Speaker 1: have in our DAS toolbox?
Speaker 2: Another useful one is filter.
Speaker 1: Okay.
Speaker 2: That function creates a new table that only includes rows
Speaker 2: that meet a specific condition.
Speaker 1: So it's like creating a custom view of your data.
Speaker 1: So if we wanted to see all the customers who
Speaker 1: made a purchase over one hundred dollars, we could use
Speaker 1: filter to create a table with just those customers exactly.
Speaker 1: And there's alexcept, which is a little.
Speaker 2: More advanced, Yeah, a little more advance.
Speaker 1: It removes all filters from a table except for the
Speaker 1: filters on the specific columns that you choose. Yeah, okay,
Speaker 1: alexcept sounds a little bit complex. It is can you
Speaker 1: walk me through like a scenario where we might use this.
Speaker 2: Sure, Let's say you're analyzing sales by region, okay, but
Speaker 2: you also want to see the overall company sales as
Speaker 2: a benchmark.
Speaker 1: Okay.
Speaker 2: You can use alex scept to calculate the total company
Speaker 2: sales while still keeping those regional filters in place for
Speaker 2: your other calculations.
Speaker 1: So you're basically saying, show me the total sales huh,
Speaker 1: regardless of region, but keep.
Speaker 2: My regional filters active for other comparisons. Yeah, that's a
Speaker 2: great example. Yeah, it seems like alex scept gives you
Speaker 2: a lot of control over how those filters are applied.
Speaker 1: It does, all right.
Speaker 2: So we've explored aggregate functions for calculations, Yeah, filter functions
Speaker 2: for narrowing down our data. But what about when we
Speaker 2: want to analyze trends over time?
Speaker 1: Yeah, that's where time intelligence functions come in.
Speaker 2: Yeah. Time is such a crucial element in most date analyzes.
Speaker 2: It is we need to be able to track how
Speaker 2: things change over days, weeks, months, years. What kind of
Speaker 2: time bending magic can DAX perform a lot?
Speaker 1: There's a whole set of functions for handling dates and
Speaker 1: time periods. You can calculate year to date totals, rolling averages.
Speaker 1: You can do comparisons to previous periods.
Speaker 2: It's like having a time machine for our data. It
Speaker 2: is so you can travel back and forth through time,
Speaker 2: analyzing trends and patterns. Give me some examples of how
Speaker 2: these time intelligence functions work in practice. Sure, let's say
Speaker 2: you want to track your company's sales performance month by month. Okay,
Speaker 2: you could use the total MTD function to calculate the
Speaker 2: total sales for each month in the year.
Speaker 1: So total MTD is pretty self explanatory. Yeah, total sales
Speaker 1: for the month to date, right, But what if I
Speaker 1: want to see how my sales this month compared to
Speaker 1: the same month last year.
Speaker 2: There's a function for that too. Oh perfect, It's called
Speaker 2: same period last year.
Speaker 1: Okay.
Speaker 2: It lets you compare values for the same period in
Speaker 2: the previous year.
Speaker 1: Okay. That's incredibly useful for spotting seasonal trends. Yeah, or
Speaker 1: measuring year over year growth.
Speaker 2: Huh.
Speaker 1: Are there other time intelligence functions that we should be
Speaker 1: aware of?
Speaker 2: Yes, there are a lot.
Speaker 1: Okay.
Speaker 2: There's functions for calculating moving averages, identifying beginning and end
Speaker 2: dates of periods. You can shift dates forward or backward
Speaker 2: in time, so you.
Speaker 1: Can get really granular with your time based analysis.
Speaker 2: You can.
Speaker 1: This is fascinating, it is. It feels like we've only
Speaker 1: just scratched the surface of what DACKS can do. It's
Speaker 1: an incredibly versatile and powerful language, it is, and as
Speaker 1: we become more familiar with it, we discover endless possibilities
Speaker 1: for manipulating and analyzing our data. Absolutely, this is definitely
Speaker 1: making me want to dive deeper into dacks and see
Speaker 1: what kind of insights I can uncover? What else should
Speaker 1: we explore?
Speaker 2: So we've been talking about how these functions work in isolation,
Speaker 2: but the real power comes when you start combining them, okay,
Speaker 2: to create more complex formulas.
Speaker 1: Ooh, I like where this is going. Yeah, let's start
Speaker 1: combining these DAX functions and see what kind of data
Speaker 1: magic we can conjure up.
Speaker 2: Let's do it.
Speaker 1: Welcome back to the deep dive. I'm feeling energized after
Speaker 1: going through those DAX functions. Yeah, it's like we've unlocked
Speaker 1: this secret code to the data universe.
Speaker 2: Yeah.
Speaker 1: Now we're going to take things up a notch okay
Speaker 1: and see how these functions can be combined to create
Speaker 1: even more sophisticated analysis exactly. Ooh, I like it all right,
Speaker 1: let's dive into this world of DAX formula combinations and
Speaker 1: see what we can create.
Speaker 2: So remember we were talking about using the calculate function
Speaker 2: to filter data. Yeah, imagine you're a sales manager and
Speaker 2: you want to compare the performance of your sales team
Speaker 2: across different regions. Okay, you could use calculate in conjunction
Speaker 2: with an aggregate function. Uh huh, like sem to see
Speaker 2: the total sales for each region.
Speaker 1: So we're using calculate to slice the data by region, yeah,
Speaker 1: and then sum to add up the sales within each
Speaker 1: of those slices exactly.
Speaker 2: But what if you wanted to take it a step
Speaker 2: further and see how each region performed compared to the
Speaker 2: same period last year.
Speaker 1: Ooh, that's where our time intelligence functions come in, right right.
Speaker 1: Could we somehow combine calculate with like same period last year.
Speaker 2: Yes, you can. That's the beauty of DAS. Okay, you
Speaker 2: can nest functions to create these multi layered calculations, got it.
Speaker 2: So you can use calculate to define the outer filter
Speaker 2: context okay, like sales by region, and then nest same
Speaker 2: period last year within that to calculate last year's sales
Speaker 2: for the corresponding period.
Speaker 1: Okay, my mind is slightly blown right now. So we're
Speaker 1: doing a year over year comparison within each regional slice
Speaker 1: of our data. Yeah, that's some serious analytical power, it is.
Speaker 1: And that's just one example. Right, you can combine DAX
Speaker 1: functions in countless ways to answer all sorts of complex
Speaker 1: business questions. I'm starting to see the possibilities here. Yeah,
Speaker 1: but I gotta admit building these nested formulas, uh huh
Speaker 1: can seem a little daunting.
Speaker 2: It can be.
Speaker 1: What are some tips for making it easier to write
Speaker 1: these more complex DAX expressions.
Speaker 2: Well, one tip is to break down your problem into
Speaker 2: smaller steps. Okay, don't try to write the entire formula
Speaker 2: once got it. Think about the individual components you need
Speaker 2: and the functions that can achieve each step.
Speaker 1: So it's like building a house. You wouldn't try to
Speaker 1: construct the entire thing in one go, exactly. You'd start
Speaker 1: with the foundation, and then the walls and then the roof. Right,
Speaker 1: it's crucial to have a clear plan before you start.
Speaker 1: It is writing your Dack's formula, map out the logic
Speaker 1: and the sek quints of functions. Any other advice for
Speaker 1: aspiring DAX wizards out there.
Speaker 2: Another helpful tip is to use the tools that powerbi provides. Okay,
Speaker 2: So the formula bar has intell a sense okay, which
Speaker 2: suggests functions and arguments as you type.
Speaker 1: Oh, that's nice.
Speaker 2: So it makes it much easier to find the right components.
Speaker 1: That's like having a copilot, Yeah, guiding you through the
Speaker 1: formula writing process. It is takes a lot of the
Speaker 1: guesswork out of it.
Speaker 2: Yeah, and don't forget about all the online resources.
Speaker 1: It's true.
Speaker 2: There's forums and blogs and tutorials.
Speaker 1: It's great to know there's a supportive community out there
Speaker 1: for DAX learners.
Speaker 2: Yeah.
Speaker 1: Are there any common pitfalls that people tend to fall
Speaker 1: into one writing DAX formulas? Things we should watch out for.
Speaker 2: One common mistake is forgetting about filter context.
Speaker 1: Okay.
Speaker 2: DAX formulas operate within a specific filter context, which determines
Speaker 2: what data is being used for the calculation.
Speaker 1: Yeah.
Speaker 2: If you're not mindful of the filter context, Yeah, you
Speaker 2: might get some unexpected results.
Speaker 1: It's like this invisible force it is that's shaping our
Speaker 1: calculations behind the scenes.
Speaker 2: Yeah. Another common pitfall using the wrong data type.
Speaker 1: Okay.
Speaker 2: DAX is really strict about data types, got it. So
Speaker 2: if you try to perform a calculation on incompatible data types,
Speaker 2: you're going to get an error.
Speaker 1: Okay, So paying attention to data types is crucial.
Speaker 2: Yeah, it's like using the right ingredients in a recipe.
Speaker 1: Right, Okay. Any other DAX traps to watch out for.
Speaker 2: Over using variables. Variables can be helpful, yeah, but using
Speaker 2: too many can make your code harder to read, okay
Speaker 2: and debug.
Speaker 1: So it's all about finding that balance, using them strategically
Speaker 1: without overcomplicating things exactly.
Speaker 2: And lastly, yeah, don't be afraid to experiment. Okay, that's
Speaker 2: the best way to learn DAX. Yeah, try different functions,
Speaker 2: combine them, see what happens.
Speaker 1: I love that advice. It's all about embracing the spirit
Speaker 1: of exploration it is, and not being afraid to make
Speaker 1: mistakes along the way.
Speaker 2: Exactly.
Speaker 1: That's how we learn. Yeah, this has been an incredible journey.
Speaker 1: It has we explored the key components of power BI,
Speaker 1: the DAX functions, and even how to combine them. I'm
Speaker 1: feeling inspired and empowered to tackle my own data challenges
Speaker 1: with DAX as my trustee. Sidekick.
Speaker 2: That's great.
Speaker 1: You've certainly succeeded in sparking that enthusiasm in me. I'm
Speaker 1: glad this has been a fantastic exploration.
Speaker 2: It has been.
Speaker 1: I can't wait to put all this knowledge into practice.
Speaker 1: Me too, awesome. This has been fun.
Speaker 2: Yeah, it has. Thanks for having me, and.
Speaker 1: To all of our listeners out there. Until next time,
Speaker 1: happy analyzing.