WEBVTT

1
00:00:00.040 --> 00:00:02.879
<v Speaker 1>Hey everyone, and welcome to the deep dive today. We're

2
00:00:02.919 --> 00:00:08.160
<v Speaker 1>diving into POWERBI and dax Ooh, POWERBI and dax it's

3
00:00:08.919 --> 00:00:10.519
<v Speaker 1>pretty popular, right, Yeah, it's.

4
00:00:10.439 --> 00:00:11.599
<v Speaker 2>A really powerful combo.

5
00:00:11.679 --> 00:00:14.320
<v Speaker 1>It's changing the way we look at data and analyze

6
00:00:14.400 --> 00:00:17.320
<v Speaker 1>data definitely. So you gave us some awesome resources to

7
00:00:17.399 --> 00:00:21.519
<v Speaker 1>help guide our deep dive today, including excerpts from this

8
00:00:21.719 --> 00:00:26.239
<v Speaker 1>book called POWERBI, moving beyond the basics of POWERBI and

9
00:00:26.320 --> 00:00:30.320
<v Speaker 1>learning more about decks. Okay, so, obviously, respecting copyright, we're

10
00:00:30.399 --> 00:00:33.679
<v Speaker 1>using all this material for purely educational purposes, of course,

11
00:00:33.799 --> 00:00:36.880
<v Speaker 1>But let's just jump right in. What's the big deal

12
00:00:36.880 --> 00:00:37.600
<v Speaker 1>with POWERBI?

13
00:00:38.159 --> 00:00:42.359
<v Speaker 2>So POWERBI is so popular because it makes data analysis

14
00:00:42.399 --> 00:00:45.759
<v Speaker 2>accessible Okay, So it's user friendly, got it even for

15
00:00:45.880 --> 00:00:49.079
<v Speaker 2>people who aren't, you know, like data scientists or anything.

16
00:00:49.200 --> 00:00:51.280
<v Speaker 1>Right, So like a drag and drop interface I've heard

17
00:00:51.280 --> 00:00:53.840
<v Speaker 1>about that exactly, to make it easy to create visualization.

18
00:00:53.960 --> 00:00:55.840
<v Speaker 1>It's almost like building with digital legos.

19
00:00:56.039 --> 00:00:56.960
<v Speaker 2>That's a good analogy.

20
00:00:57.079 --> 00:00:59.520
<v Speaker 1>But the real magic happens when you combine that with

21
00:00:59.640 --> 00:01:01.679
<v Speaker 1>DAX right, the language behind.

22
00:01:01.320 --> 00:01:03.719
<v Speaker 2>It exactly, that's the secret sauce.

23
00:01:03.520 --> 00:01:06.439
<v Speaker 1>That lets you do those complex calculations and really dig

24
00:01:06.480 --> 00:01:10.079
<v Speaker 1>into the data. Okay, before we get into the decks,

25
00:01:10.280 --> 00:01:14.319
<v Speaker 1>can you give us a quick overview of the powerbi ecosystem.

26
00:01:14.400 --> 00:01:18.120
<v Speaker 2>Sure, the powerbi ecosystem has well. There are a few

27
00:01:18.120 --> 00:01:21.480
<v Speaker 2>different components. You've got the powerbi desktop is where you

28
00:01:21.519 --> 00:01:24.439
<v Speaker 2>build your reports and visualizations at it. And then there's

29
00:01:24.480 --> 00:01:27.840
<v Speaker 2>the Powerbi service, which is the online platform where you

30
00:01:28.280 --> 00:01:31.159
<v Speaker 2>share your work and you can collaborate. And then there's

31
00:01:31.200 --> 00:01:33.920
<v Speaker 2>also mobile apps. Oh cool, So you can access your

32
00:01:34.000 --> 00:01:35.200
<v Speaker 2>data from anywhere.

33
00:01:35.280 --> 00:01:38.879
<v Speaker 1>So it's like this like a whole data analysis studio exactly,

34
00:01:38.959 --> 00:01:42.560
<v Speaker 1>from creation to collaboration and on the go access.

35
00:01:42.640 --> 00:01:42.879
<v Speaker 2>Yep.

36
00:01:43.319 --> 00:01:45.799
<v Speaker 1>All right, and I know that powerbi can connect to

37
00:01:45.840 --> 00:01:49.560
<v Speaker 1>a ton of different data sources. What are some of

38
00:01:49.599 --> 00:01:50.680
<v Speaker 1>the possibilities there.

39
00:01:50.920 --> 00:01:52.519
<v Speaker 2>That's one of the things that's so great about it.

40
00:01:52.640 --> 00:01:57.319
<v Speaker 2>You can connect to data from pretty much anywhere. Oh wow, spreadsheets, databases,

41
00:01:57.480 --> 00:01:58.959
<v Speaker 2>cloud services, you name it.

42
00:01:59.000 --> 00:02:02.319
<v Speaker 1>So like a melting pot. Yeah, from all these sources.

43
00:02:02.439 --> 00:02:03.159
<v Speaker 1>That's amazing.

44
00:02:03.439 --> 00:02:05.439
<v Speaker 2>Yeah. And there's two main ways to connect to data,

45
00:02:06.000 --> 00:02:07.159
<v Speaker 2>import and direct query.

46
00:02:07.280 --> 00:02:10.319
<v Speaker 1>Okay, let's unpack these two different methods. Imagine there's some

47
00:02:10.360 --> 00:02:11.800
<v Speaker 1>trade offs with each one.

48
00:02:12.039 --> 00:02:16.000
<v Speaker 2>So with import, yeah, you're bringing a copy of the

49
00:02:16.080 --> 00:02:18.840
<v Speaker 2>data into power Bi Okay, So you have a lot

50
00:02:18.879 --> 00:02:21.840
<v Speaker 2>more flexibility to shape and manipulate the data.

51
00:02:21.960 --> 00:02:22.400
<v Speaker 1>Got it.

52
00:02:22.439 --> 00:02:24.039
<v Speaker 2>But it's a snapshot in time.

53
00:02:24.400 --> 00:02:24.759
<v Speaker 1>Okay.

54
00:02:24.840 --> 00:02:28.199
<v Speaker 2>So if the original data changes, you need to refresh

55
00:02:28.240 --> 00:02:29.639
<v Speaker 2>your power bi data set.

56
00:02:29.800 --> 00:02:32.439
<v Speaker 1>Okay. So it's like having a local copy of a

57
00:02:32.479 --> 00:02:34.680
<v Speaker 1>file exactly if you can make all the edits you

58
00:02:34.719 --> 00:02:38.280
<v Speaker 1>want without affecting the original uh huh, got it? What

59
00:02:38.360 --> 00:02:39.479
<v Speaker 1>about direct query?

60
00:02:39.879 --> 00:02:43.199
<v Speaker 2>Direct query is more like a live feed to the

61
00:02:43.280 --> 00:02:43.800
<v Speaker 2>data source.

62
00:02:43.879 --> 00:02:44.240
<v Speaker 1>Got it.

63
00:02:44.599 --> 00:02:47.759
<v Speaker 2>So it maintains that constant connection. You're always working with

64
00:02:47.800 --> 00:02:50.080
<v Speaker 2>the most up to date information.

65
00:02:50.360 --> 00:02:51.080
<v Speaker 1>That makes sense.

66
00:02:51.199 --> 00:02:52.199
<v Speaker 2>But there's a trade off.

67
00:02:52.439 --> 00:02:52.719
<v Speaker 1>Okay.

68
00:02:52.840 --> 00:02:57.520
<v Speaker 2>You have more limited ability to manipulate the data within Powerbi,

69
00:02:58.000 --> 00:03:01.039
<v Speaker 2>and it might not be the best for huge data

70
00:03:01.039 --> 00:03:03.560
<v Speaker 2>sets okay, because it can impact performance.

71
00:03:03.680 --> 00:03:06.800
<v Speaker 1>So it's a balancing act between those two. If you

72
00:03:06.879 --> 00:03:12.199
<v Speaker 1>need real time direct query, but if you need the flexibility.

73
00:03:11.680 --> 00:03:13.560
<v Speaker 2>Exactly import okay.

74
00:03:13.560 --> 00:03:17.240
<v Speaker 1>So it all comes down to choosing the right tool

75
00:03:17.360 --> 00:03:21.479
<v Speaker 1>for the job. Before we even start building out our visualizations,

76
00:03:22.000 --> 00:03:25.319
<v Speaker 1>there are a few key concepts you understand.

77
00:03:25.439 --> 00:03:30.319
<v Speaker 2>So the book highlights data sets, okay, visualizations, reports, and dashboards.

78
00:03:30.439 --> 00:03:33.080
<v Speaker 1>Okay, So let's break those down. Okay, what are data

79
00:03:33.120 --> 00:03:34.759
<v Speaker 1>sets in Powerbi.

80
00:03:34.599 --> 00:03:39.919
<v Speaker 2>Data sets are collections of data that Powerbi uses to

81
00:03:40.319 --> 00:03:42.280
<v Speaker 2>create visualizations and reports.

82
00:03:42.360 --> 00:03:45.719
<v Speaker 1>Okay, So like gathering the ingredients before you start cooking,

83
00:03:45.879 --> 00:03:47.759
<v Speaker 1>you've got to have all the data sets prepped and

84
00:03:47.840 --> 00:03:50.080
<v Speaker 1>ready to go, right exactly. And once you have your

85
00:03:50.120 --> 00:03:53.439
<v Speaker 1>data sets, you can start creating visualizations the visual representation

86
00:03:53.560 --> 00:03:54.520
<v Speaker 1>of that data.

87
00:03:54.280 --> 00:03:55.960
<v Speaker 2>And there's tons of different options.

88
00:03:55.960 --> 00:03:58.560
<v Speaker 1>This is where it gets fun, right, Yes, bar charts,

89
00:03:58.639 --> 00:04:03.319
<v Speaker 1>pie charts, all that good stuff, bringing the data to life.

90
00:04:03.719 --> 00:04:07.120
<v Speaker 2>It makes the data much easier to understand when you

91
00:04:07.120 --> 00:04:08.120
<v Speaker 2>can see it visually.

92
00:04:08.280 --> 00:04:12.360
<v Speaker 1>So visualizations are like the individual snapshots of the data, right,

93
00:04:12.919 --> 00:04:16.560
<v Speaker 1>But how do we combine those snapshots into a bigger picture.

94
00:04:16.800 --> 00:04:21.560
<v Speaker 2>That's where reports come in. Reports are collections of visualizations

95
00:04:21.600 --> 00:04:23.959
<v Speaker 2>that tell a data story, got it. It could be

96
00:04:24.000 --> 00:04:26.560
<v Speaker 2>single pages, multiple pages, okay, but they are a way

97
00:04:26.560 --> 00:04:28.040
<v Speaker 2>to arrange your visualizations.

98
00:04:28.120 --> 00:04:30.920
<v Speaker 1>Okay. So if visualizations are the pieces of the puzzle,

99
00:04:31.680 --> 00:04:35.560
<v Speaker 1>a report is the completed puzzle, showing the full picture.

100
00:04:35.519 --> 00:04:39.680
<v Speaker 2>Exactly I like it. And then there's one more piece, yeah, dashboards.

101
00:04:40.000 --> 00:04:43.519
<v Speaker 2>Dashboards they're like the executive summaries you can put your

102
00:04:43.519 --> 00:04:48.160
<v Speaker 2>most important visualizations, okay, or key performance indicators. Got it

103
00:04:48.560 --> 00:04:50.360
<v Speaker 2>designed for high level monitoring.

104
00:04:51.079 --> 00:04:54.600
<v Speaker 1>So if the report is the full story, yeah, this

105
00:04:54.680 --> 00:04:55.600
<v Speaker 1>is the highlight reel.

106
00:04:55.839 --> 00:04:56.439
<v Speaker 2>It's exactly.

107
00:04:56.519 --> 00:05:00.000
<v Speaker 1>This is the key takeaways that you want everyone to see, exactly,

108
00:05:00.000 --> 00:05:03.120
<v Speaker 1>So present the most relevant information in a concise and

109
00:05:03.160 --> 00:05:06.240
<v Speaker 1>appealing way. Yes, it's pretty powerful.

110
00:05:06.560 --> 00:05:06.879
<v Speaker 2>It is.

111
00:05:06.959 --> 00:05:10.399
<v Speaker 1>You can collect data from these various sources, transform it

112
00:05:10.439 --> 00:05:12.759
<v Speaker 1>into these insights, and then present it in a way

113
00:05:12.800 --> 00:05:14.279
<v Speaker 1>that's informative and engaging.

114
00:05:14.480 --> 00:05:15.000
<v Speaker 2>Exactly.

115
00:05:15.120 --> 00:05:17.600
<v Speaker 1>This is awesome. We've covered so much already. Oh yeah,

116
00:05:17.639 --> 00:05:23.399
<v Speaker 1>the components of POWERBI, the different connection methods, visualizations, reports,

117
00:05:23.399 --> 00:05:26.839
<v Speaker 1>and dashboards. I have a feeling we're just getting started.

118
00:05:27.000 --> 00:05:28.959
<v Speaker 2>We are just getting started. Now we're going to get

119
00:05:28.959 --> 00:05:29.959
<v Speaker 2>into the real fun stuff.

120
00:05:30.000 --> 00:05:32.639
<v Speaker 1>Ooh okay, Das all right, daks.

121
00:05:32.399 --> 00:05:35.680
<v Speaker 2>The language that unlocks the true power of POWERBI.

122
00:05:35.920 --> 00:05:38.600
<v Speaker 1>That's what I'm waiting for, Yeah, to unlock the true

123
00:05:38.639 --> 00:05:43.519
<v Speaker 1>power create those custom calculations and visualizations that take data

124
00:05:43.560 --> 00:05:46.199
<v Speaker 1>analysis to the next level. Absolutely, I'm ready to get

125
00:05:46.240 --> 00:05:47.399
<v Speaker 1>my hams dirty with Das.

126
00:05:47.519 --> 00:05:50.319
<v Speaker 2>All right, welcome back to our deep dive into POWERBI

127
00:05:50.480 --> 00:05:51.000
<v Speaker 2>and DAX.

128
00:05:51.439 --> 00:05:55.000
<v Speaker 1>It really feels like we've just explored this vast landscape

129
00:05:55.120 --> 00:05:58.399
<v Speaker 1>of POWERBI and now we're ready to like build something

130
00:05:58.439 --> 00:06:01.040
<v Speaker 1>amazing with DAK as our toolkit.

131
00:06:01.319 --> 00:06:02.079
<v Speaker 2>It is like a tool.

132
00:06:02.199 --> 00:06:03.959
<v Speaker 1>I'm ready to roll up my sleeves and see what

133
00:06:04.079 --> 00:06:05.839
<v Speaker 1>this DAX toolkit can do.

134
00:06:06.000 --> 00:06:06.519
<v Speaker 2>Let's do it.

135
00:06:06.560 --> 00:06:07.639
<v Speaker 1>Where should we start?

136
00:06:07.959 --> 00:06:12.600
<v Speaker 2>So the book dives into some key categories of DAX functions, okay,

137
00:06:12.759 --> 00:06:18.600
<v Speaker 2>like aggregate functions, filter functions, and time intelligence functions.

138
00:06:18.680 --> 00:06:21.319
<v Speaker 1>Okay, so those are kind of like the workhourses of DAKS.

139
00:06:21.639 --> 00:06:23.360
<v Speaker 2>Yeah, there are the workhourses.

140
00:06:22.920 --> 00:06:26.959
<v Speaker 1>That let you perform calculations and filter data and analyze

141
00:06:27.040 --> 00:06:30.279
<v Speaker 1>trends over time exactly. Okay, that sounds like a pretty

142
00:06:30.319 --> 00:06:34.000
<v Speaker 1>logical place to start. Let's start with aggregate functions.

143
00:06:34.240 --> 00:06:34.480
<v Speaker 2>Okay.

144
00:06:34.680 --> 00:06:37.560
<v Speaker 1>Are these similar to the functions that we might use

145
00:06:37.560 --> 00:06:40.560
<v Speaker 1>and Excel like SU and average.

146
00:06:40.879 --> 00:06:43.720
<v Speaker 2>They are, but there's a key difference. In Excel, you're

147
00:06:43.720 --> 00:06:47.600
<v Speaker 2>applying functions to individual cells or ranges of cells, okay,

148
00:06:47.759 --> 00:06:51.279
<v Speaker 2>but in DAKS you apply them to entire columns or

149
00:06:51.319 --> 00:06:52.319
<v Speaker 2>tables of data.

150
00:06:52.399 --> 00:06:54.959
<v Speaker 1>Ah. That makes sense because we're dealing with these much

151
00:06:55.040 --> 00:06:58.759
<v Speaker 1>larger data sets empower BI, so we need these functions

152
00:06:58.759 --> 00:07:01.519
<v Speaker 1>that can handle that kind of scale. Right, Can you

153
00:07:01.519 --> 00:07:04.560
<v Speaker 1>give me an example of how DA's aggregate function works

154
00:07:04.639 --> 00:07:05.560
<v Speaker 1>in practice.

155
00:07:05.680 --> 00:07:09.360
<v Speaker 2>Sure. Let's say you're analyzing sales data, okay, and you

156
00:07:09.399 --> 00:07:11.079
<v Speaker 2>have a column called sales amount.

157
00:07:11.160 --> 00:07:11.519
<v Speaker 1>Got it?

158
00:07:12.079 --> 00:07:16.920
<v Speaker 2>To calculate the total sales for all transactions, okay, you

159
00:07:16.959 --> 00:07:20.480
<v Speaker 2>would use the im function in DAX and you'd apply

160
00:07:20.519 --> 00:07:22.279
<v Speaker 2>it to that entire sales amount column.

161
00:07:22.439 --> 00:07:24.480
<v Speaker 1>So instead of adding up the sales one by one,

162
00:07:24.720 --> 00:07:28.600
<v Speaker 1>we're just telling DAX sem the whole column. That's efficient.

163
00:07:28.920 --> 00:07:31.120
<v Speaker 1>And what if we wanted to calculate like the average

164
00:07:31.160 --> 00:07:32.240
<v Speaker 1>sale instead of the.

165
00:07:32.160 --> 00:07:35.319
<v Speaker 2>Total, then you would use the average function, okay, and

166
00:07:35.360 --> 00:07:37.519
<v Speaker 2>you would apply it to the entire sales amount column.

167
00:07:37.720 --> 00:07:40.720
<v Speaker 1>Got it? So some and average are pretty straightforward. Yeah,

168
00:07:40.959 --> 00:07:44.839
<v Speaker 1>but what about like finding the highest or lowest sales values?

169
00:07:45.319 --> 00:07:47.759
<v Speaker 2>So for that, you would use the max function to

170
00:07:47.800 --> 00:07:51.079
<v Speaker 2>return the largest value or the min function to get

171
00:07:51.079 --> 00:07:52.040
<v Speaker 2>the smallest value.

172
00:07:52.360 --> 00:07:55.480
<v Speaker 1>Okay, so we have max and min. I'm starting to

173
00:07:55.480 --> 00:07:56.319
<v Speaker 1>see a pattern here.

174
00:07:56.560 --> 00:07:57.000
<v Speaker 2>Yeah.

175
00:07:57.120 --> 00:08:01.439
<v Speaker 1>These DAX functions have these really fuitive names. Do that

176
00:08:01.439 --> 00:08:02.920
<v Speaker 1>make them pretty easy to understand?

177
00:08:03.279 --> 00:08:06.040
<v Speaker 2>Yeah? DAX is meant to be accessible, Okay, even for

178
00:08:06.040 --> 00:08:07.360
<v Speaker 2>people who aren't programmers.

179
00:08:07.560 --> 00:08:10.800
<v Speaker 1>I like that. But don't let the simplicity fool you, right,

180
00:08:10.879 --> 00:08:15.079
<v Speaker 1>because DAX can handle incredibly complex calculations. It can, all right,

181
00:08:15.160 --> 00:08:18.000
<v Speaker 1>I'm eager to see how far we can push that. Now,

182
00:08:18.720 --> 00:08:23.879
<v Speaker 1>we've talked about calculating sums, averages, maximums, minimums. Are there

183
00:08:23.920 --> 00:08:26.720
<v Speaker 1>any other aggregate functions that we should be aware of?

184
00:08:27.399 --> 00:08:31.560
<v Speaker 2>Yeah, there are tons. There's functions for counting distinct values,

185
00:08:32.320 --> 00:08:36.600
<v Speaker 2>counting grows in a table, and even calculating statistical measures

186
00:08:37.320 --> 00:08:40.039
<v Speaker 2>like standard deviation and variance.

187
00:08:40.240 --> 00:08:43.799
<v Speaker 1>So we can get really granular with our data analysis. Again,

188
00:08:43.919 --> 00:08:47.519
<v Speaker 1>using these aggregate functions, it's like having a statistical Swiss

189
00:08:47.600 --> 00:08:49.480
<v Speaker 1>Army knife. It is at our disposal.

190
00:08:49.759 --> 00:08:50.080
<v Speaker 2>Yeah.

191
00:08:50.120 --> 00:08:53.039
<v Speaker 1>But sometimes we need to do more than just calculate summaries.

192
00:08:52.639 --> 00:08:56.039
<v Speaker 2>Right, Sometimes we need to filter our data, yes, to

193
00:08:56.080 --> 00:09:00.559
<v Speaker 2>focus on like specific subsets or conditions. It's where filter

194
00:09:00.559 --> 00:09:01.240
<v Speaker 2>functions come in.

195
00:09:01.320 --> 00:09:02.159
<v Speaker 1>That's where they come in.

196
00:09:02.279 --> 00:09:05.120
<v Speaker 2>It's like using a search engine to refine our results. Yeah,

197
00:09:05.200 --> 00:09:07.879
<v Speaker 2>we only want to see data that meets our criteria exactly.

198
00:09:08.000 --> 00:09:09.759
<v Speaker 2>So how do we accomplish that? In DAX.

199
00:09:10.000 --> 00:09:13.759
<v Speaker 1>One of the most powerful filter functions is calculate Okay.

200
00:09:13.840 --> 00:09:17.360
<v Speaker 1>It allows you to modify the filter context of a calculation.

201
00:09:17.639 --> 00:09:21.200
<v Speaker 2>Okay, So essentially we're telling DAX to perform a calculation,

202
00:09:21.559 --> 00:09:24.039
<v Speaker 2>but only on a subset of the data, right that

203
00:09:24.159 --> 00:09:25.159
<v Speaker 2>meets a certain condition.

204
00:09:25.320 --> 00:09:25.720
<v Speaker 1>Exactly.

205
00:09:25.799 --> 00:09:27.039
<v Speaker 2>You give me concrete example.

206
00:09:27.200 --> 00:09:29.600
<v Speaker 1>Sure. So let's say we want to analyze sales for

207
00:09:29.720 --> 00:09:34.679
<v Speaker 1>a specific product category okay, like electronics. Okay, electronics, So

208
00:09:34.799 --> 00:09:38.960
<v Speaker 1>we would use calculate to sum the sales amount column,

209
00:09:39.360 --> 00:09:43.759
<v Speaker 1>but only for transactions where the product category column equals electronics.

210
00:09:43.919 --> 00:09:46.679
<v Speaker 2>Ah. Okay, So we're using calculate to kind of zoom

211
00:09:46.720 --> 00:09:50.200
<v Speaker 2>in a particular segment of our data.

212
00:09:50.279 --> 00:09:53.159
<v Speaker 1>Right, that's super helpful. What other filter functions should we

213
00:09:53.240 --> 00:09:54.639
<v Speaker 1>have in our DAS toolbox?

214
00:09:54.879 --> 00:09:56.200
<v Speaker 2>Another useful one is filter.

215
00:09:56.879 --> 00:09:57.279
<v Speaker 1>Okay.

216
00:09:57.519 --> 00:10:01.159
<v Speaker 2>That function creates a new table that only includes rows

217
00:10:01.720 --> 00:10:02.960
<v Speaker 2>that meet a specific condition.

218
00:10:03.480 --> 00:10:06.360
<v Speaker 1>So it's like creating a custom view of your data.

219
00:10:06.480 --> 00:10:08.639
<v Speaker 1>So if we wanted to see all the customers who

220
00:10:08.720 --> 00:10:11.720
<v Speaker 1>made a purchase over one hundred dollars, we could use

221
00:10:11.799 --> 00:10:16.240
<v Speaker 1>filter to create a table with just those customers exactly.

222
00:10:16.399 --> 00:10:19.240
<v Speaker 1>And there's alexcept, which is a little.

223
00:10:19.000 --> 00:10:20.840
<v Speaker 2>More advanced, Yeah, a little more advance.

224
00:10:20.919 --> 00:10:24.240
<v Speaker 1>It removes all filters from a table except for the

225
00:10:24.279 --> 00:10:27.080
<v Speaker 1>filters on the specific columns that you choose. Yeah, okay,

226
00:10:27.159 --> 00:10:30.600
<v Speaker 1>alexcept sounds a little bit complex. It is can you

227
00:10:30.639 --> 00:10:32.799
<v Speaker 1>walk me through like a scenario where we might use this.

228
00:10:32.960 --> 00:10:36.320
<v Speaker 2>Sure, Let's say you're analyzing sales by region, okay, but

229
00:10:36.399 --> 00:10:39.759
<v Speaker 2>you also want to see the overall company sales as

230
00:10:39.799 --> 00:10:40.440
<v Speaker 2>a benchmark.

231
00:10:40.559 --> 00:10:40.919
<v Speaker 1>Okay.

232
00:10:41.360 --> 00:10:43.559
<v Speaker 2>You can use alex scept to calculate the total company

233
00:10:43.639 --> 00:10:46.919
<v Speaker 2>sales while still keeping those regional filters in place for

234
00:10:47.000 --> 00:10:48.080
<v Speaker 2>your other calculations.

235
00:10:48.799 --> 00:10:51.360
<v Speaker 1>So you're basically saying, show me the total sales huh,

236
00:10:51.480 --> 00:10:53.799
<v Speaker 1>regardless of region, but keep.

237
00:10:53.639 --> 00:10:57.200
<v Speaker 2>My regional filters active for other comparisons. Yeah, that's a

238
00:10:57.240 --> 00:10:59.759
<v Speaker 2>great example. Yeah, it seems like alex scept gives you

239
00:10:59.759 --> 00:11:02.600
<v Speaker 2>a lot of control over how those filters are applied.

240
00:11:02.720 --> 00:11:03.759
<v Speaker 1>It does, all right.

241
00:11:03.840 --> 00:11:08.200
<v Speaker 2>So we've explored aggregate functions for calculations, Yeah, filter functions

242
00:11:08.240 --> 00:11:10.799
<v Speaker 2>for narrowing down our data. But what about when we

243
00:11:10.840 --> 00:11:12.919
<v Speaker 2>want to analyze trends over time?

244
00:11:13.159 --> 00:11:15.799
<v Speaker 1>Yeah, that's where time intelligence functions come in.

245
00:11:15.960 --> 00:11:20.279
<v Speaker 2>Yeah. Time is such a crucial element in most date analyzes.

246
00:11:20.480 --> 00:11:22.440
<v Speaker 2>It is we need to be able to track how

247
00:11:22.480 --> 00:11:27.840
<v Speaker 2>things change over days, weeks, months, years. What kind of

248
00:11:27.879 --> 00:11:30.759
<v Speaker 2>time bending magic can DAX perform a lot?

249
00:11:31.279 --> 00:11:34.360
<v Speaker 1>There's a whole set of functions for handling dates and

250
00:11:34.399 --> 00:11:38.759
<v Speaker 1>time periods. You can calculate year to date totals, rolling averages.

251
00:11:39.320 --> 00:11:41.679
<v Speaker 1>You can do comparisons to previous periods.

252
00:11:41.799 --> 00:11:43.919
<v Speaker 2>It's like having a time machine for our data. It

253
00:11:44.000 --> 00:11:46.000
<v Speaker 2>is so you can travel back and forth through time,

254
00:11:46.559 --> 00:11:49.919
<v Speaker 2>analyzing trends and patterns. Give me some examples of how

255
00:11:49.960 --> 00:11:53.360
<v Speaker 2>these time intelligence functions work in practice. Sure, let's say

256
00:11:53.360 --> 00:11:57.240
<v Speaker 2>you want to track your company's sales performance month by month. Okay,

257
00:11:57.559 --> 00:12:01.279
<v Speaker 2>you could use the total MTD function to calculate the

258
00:12:01.360 --> 00:12:03.720
<v Speaker 2>total sales for each month in the year.

259
00:12:04.120 --> 00:12:08.360
<v Speaker 1>So total MTD is pretty self explanatory. Yeah, total sales

260
00:12:08.399 --> 00:12:10.840
<v Speaker 1>for the month to date, right, But what if I

261
00:12:10.879 --> 00:12:13.919
<v Speaker 1>want to see how my sales this month compared to

262
00:12:13.960 --> 00:12:15.279
<v Speaker 1>the same month last year.

263
00:12:15.480 --> 00:12:17.759
<v Speaker 2>There's a function for that too. Oh perfect, It's called

264
00:12:17.799 --> 00:12:18.879
<v Speaker 2>same period last year.

265
00:12:19.120 --> 00:12:19.440
<v Speaker 1>Okay.

266
00:12:19.519 --> 00:12:21.799
<v Speaker 2>It lets you compare values for the same period in

267
00:12:21.840 --> 00:12:22.519
<v Speaker 2>the previous year.

268
00:12:22.600 --> 00:12:26.240
<v Speaker 1>Okay. That's incredibly useful for spotting seasonal trends. Yeah, or

269
00:12:26.279 --> 00:12:27.960
<v Speaker 1>measuring year over year growth.

270
00:12:28.200 --> 00:12:28.360
<v Speaker 2>Huh.

271
00:12:28.679 --> 00:12:31.440
<v Speaker 1>Are there other time intelligence functions that we should be

272
00:12:31.440 --> 00:12:31.879
<v Speaker 1>aware of?

273
00:12:32.320 --> 00:12:33.519
<v Speaker 2>Yes, there are a lot.

274
00:12:33.720 --> 00:12:33.960
<v Speaker 1>Okay.

275
00:12:33.960 --> 00:12:38.120
<v Speaker 2>There's functions for calculating moving averages, identifying beginning and end

276
00:12:38.200 --> 00:12:41.240
<v Speaker 2>dates of periods. You can shift dates forward or backward

277
00:12:41.320 --> 00:12:42.159
<v Speaker 2>in time, so you.

278
00:12:42.080 --> 00:12:45.600
<v Speaker 1>Can get really granular with your time based analysis.

279
00:12:45.759 --> 00:12:46.159
<v Speaker 2>You can.

280
00:12:46.559 --> 00:12:49.720
<v Speaker 1>This is fascinating, it is. It feels like we've only

281
00:12:49.759 --> 00:12:52.720
<v Speaker 1>just scratched the surface of what DACKS can do. It's

282
00:12:52.759 --> 00:12:56.279
<v Speaker 1>an incredibly versatile and powerful language, it is, and as

283
00:12:56.279 --> 00:12:59.799
<v Speaker 1>we become more familiar with it, we discover endless possibilities

284
00:13:00.399 --> 00:13:04.120
<v Speaker 1>for manipulating and analyzing our data. Absolutely, this is definitely

285
00:13:04.120 --> 00:13:07.000
<v Speaker 1>making me want to dive deeper into dacks and see

286
00:13:07.000 --> 00:13:09.919
<v Speaker 1>what kind of insights I can uncover? What else should

287
00:13:09.919 --> 00:13:10.440
<v Speaker 1>we explore?

288
00:13:10.799 --> 00:13:13.559
<v Speaker 2>So we've been talking about how these functions work in isolation,

289
00:13:14.360 --> 00:13:17.840
<v Speaker 2>but the real power comes when you start combining them, okay,

290
00:13:17.879 --> 00:13:19.600
<v Speaker 2>to create more complex formulas.

291
00:13:19.639 --> 00:13:23.200
<v Speaker 1>Ooh, I like where this is going. Yeah, let's start

292
00:13:23.240 --> 00:13:25.759
<v Speaker 1>combining these DAX functions and see what kind of data

293
00:13:25.799 --> 00:13:27.080
<v Speaker 1>magic we can conjure up.

294
00:13:27.159 --> 00:13:27.759
<v Speaker 2>Let's do it.

295
00:13:27.919 --> 00:13:31.799
<v Speaker 1>Welcome back to the deep dive. I'm feeling energized after

296
00:13:31.840 --> 00:13:35.159
<v Speaker 1>going through those DAX functions. Yeah, it's like we've unlocked

297
00:13:35.200 --> 00:13:37.519
<v Speaker 1>this secret code to the data universe.

298
00:13:37.679 --> 00:13:38.080
<v Speaker 2>Yeah.

299
00:13:38.200 --> 00:13:40.480
<v Speaker 1>Now we're going to take things up a notch okay

300
00:13:40.600 --> 00:13:43.639
<v Speaker 1>and see how these functions can be combined to create

301
00:13:43.720 --> 00:13:48.679
<v Speaker 1>even more sophisticated analysis exactly. Ooh, I like it all right,

302
00:13:48.759 --> 00:13:53.399
<v Speaker 1>let's dive into this world of DAX formula combinations and

303
00:13:53.519 --> 00:13:54.559
<v Speaker 1>see what we can create.

304
00:13:54.759 --> 00:13:58.000
<v Speaker 2>So remember we were talking about using the calculate function

305
00:13:58.120 --> 00:14:01.840
<v Speaker 2>to filter data. Yeah, imagine you're a sales manager and

306
00:14:01.879 --> 00:14:04.320
<v Speaker 2>you want to compare the performance of your sales team

307
00:14:04.879 --> 00:14:09.559
<v Speaker 2>across different regions. Okay, you could use calculate in conjunction

308
00:14:09.639 --> 00:14:13.799
<v Speaker 2>with an aggregate function. Uh huh, like sem to see

309
00:14:13.840 --> 00:14:15.360
<v Speaker 2>the total sales for each region.

310
00:14:15.519 --> 00:14:19.600
<v Speaker 1>So we're using calculate to slice the data by region, yeah,

311
00:14:19.639 --> 00:14:22.559
<v Speaker 1>and then sum to add up the sales within each

312
00:14:22.559 --> 00:14:23.960
<v Speaker 1>of those slices exactly.

313
00:14:24.399 --> 00:14:25.919
<v Speaker 2>But what if you wanted to take it a step

314
00:14:25.919 --> 00:14:29.679
<v Speaker 2>further and see how each region performed compared to the

315
00:14:29.679 --> 00:14:30.720
<v Speaker 2>same period last year.

316
00:14:30.840 --> 00:14:34.519
<v Speaker 1>Ooh, that's where our time intelligence functions come in, right right.

317
00:14:34.679 --> 00:14:38.840
<v Speaker 1>Could we somehow combine calculate with like same period last year.

318
00:14:39.159 --> 00:14:41.799
<v Speaker 2>Yes, you can. That's the beauty of DAS. Okay, you

319
00:14:41.799 --> 00:14:46.240
<v Speaker 2>can nest functions to create these multi layered calculations, got it.

320
00:14:46.639 --> 00:14:49.440
<v Speaker 2>So you can use calculate to define the outer filter

321
00:14:49.840 --> 00:14:54.159
<v Speaker 2>context okay, like sales by region, and then nest same

322
00:14:54.200 --> 00:14:57.879
<v Speaker 2>period last year within that to calculate last year's sales

323
00:14:58.159 --> 00:14:59.279
<v Speaker 2>for the corresponding period.

324
00:14:59.399 --> 00:15:02.559
<v Speaker 1>Okay, my mind is slightly blown right now. So we're

325
00:15:02.559 --> 00:15:06.639
<v Speaker 1>doing a year over year comparison within each regional slice

326
00:15:06.759 --> 00:15:10.639
<v Speaker 1>of our data. Yeah, that's some serious analytical power, it is.

327
00:15:10.679 --> 00:15:13.200
<v Speaker 1>And that's just one example. Right, you can combine DAX

328
00:15:13.240 --> 00:15:17.279
<v Speaker 1>functions in countless ways to answer all sorts of complex

329
00:15:17.440 --> 00:15:21.440
<v Speaker 1>business questions. I'm starting to see the possibilities here. Yeah,

330
00:15:21.600 --> 00:15:25.120
<v Speaker 1>but I gotta admit building these nested formulas, uh huh

331
00:15:25.200 --> 00:15:27.039
<v Speaker 1>can seem a little daunting.

332
00:15:27.720 --> 00:15:28.240
<v Speaker 2>It can be.

333
00:15:28.360 --> 00:15:31.080
<v Speaker 1>What are some tips for making it easier to write

334
00:15:31.159 --> 00:15:33.600
<v Speaker 1>these more complex DAX expressions.

335
00:15:33.679 --> 00:15:35.960
<v Speaker 2>Well, one tip is to break down your problem into

336
00:15:35.960 --> 00:15:39.200
<v Speaker 2>smaller steps. Okay, don't try to write the entire formula

337
00:15:39.360 --> 00:15:42.320
<v Speaker 2>once got it. Think about the individual components you need

338
00:15:42.879 --> 00:15:44.960
<v Speaker 2>and the functions that can achieve each step.

339
00:15:45.120 --> 00:15:47.320
<v Speaker 1>So it's like building a house. You wouldn't try to

340
00:15:47.360 --> 00:15:50.039
<v Speaker 1>construct the entire thing in one go, exactly. You'd start

341
00:15:50.039 --> 00:15:52.840
<v Speaker 1>with the foundation, and then the walls and then the roof. Right,

342
00:15:53.039 --> 00:15:55.559
<v Speaker 1>it's crucial to have a clear plan before you start.

343
00:15:55.679 --> 00:15:59.039
<v Speaker 1>It is writing your Dack's formula, map out the logic

344
00:15:59.440 --> 00:16:02.639
<v Speaker 1>and the sek quints of functions. Any other advice for

345
00:16:02.799 --> 00:16:04.639
<v Speaker 1>aspiring DAX wizards out there.

346
00:16:05.120 --> 00:16:09.799
<v Speaker 2>Another helpful tip is to use the tools that powerbi provides. Okay,

347
00:16:09.960 --> 00:16:13.320
<v Speaker 2>So the formula bar has intell a sense okay, which

348
00:16:13.480 --> 00:16:16.039
<v Speaker 2>suggests functions and arguments as you type.

349
00:16:16.120 --> 00:16:16.879
<v Speaker 1>Oh, that's nice.

350
00:16:16.919 --> 00:16:20.000
<v Speaker 2>So it makes it much easier to find the right components.

351
00:16:20.120 --> 00:16:22.720
<v Speaker 1>That's like having a copilot, Yeah, guiding you through the

352
00:16:22.759 --> 00:16:25.080
<v Speaker 1>formula writing process. It is takes a lot of the

353
00:16:25.080 --> 00:16:25.919
<v Speaker 1>guesswork out of it.

354
00:16:26.120 --> 00:16:28.559
<v Speaker 2>Yeah, and don't forget about all the online resources.

355
00:16:28.639 --> 00:16:29.120
<v Speaker 1>It's true.

356
00:16:29.159 --> 00:16:31.399
<v Speaker 2>There's forums and blogs and tutorials.

357
00:16:31.519 --> 00:16:34.399
<v Speaker 1>It's great to know there's a supportive community out there

358
00:16:34.440 --> 00:16:35.360
<v Speaker 1>for DAX learners.

359
00:16:35.559 --> 00:16:36.000
<v Speaker 2>Yeah.

360
00:16:36.039 --> 00:16:40.159
<v Speaker 1>Are there any common pitfalls that people tend to fall

361
00:16:40.200 --> 00:16:43.960
<v Speaker 1>into one writing DAX formulas? Things we should watch out for.

362
00:16:44.279 --> 00:16:47.320
<v Speaker 2>One common mistake is forgetting about filter context.

363
00:16:47.519 --> 00:16:47.879
<v Speaker 1>Okay.

364
00:16:48.320 --> 00:16:53.240
<v Speaker 2>DAX formulas operate within a specific filter context, which determines

365
00:16:53.320 --> 00:16:55.279
<v Speaker 2>what data is being used for the calculation.

366
00:16:55.679 --> 00:16:56.240
<v Speaker 1>Yeah.

367
00:16:56.600 --> 00:16:59.360
<v Speaker 2>If you're not mindful of the filter context, Yeah, you

368
00:16:59.440 --> 00:17:00.960
<v Speaker 2>might get some unexpected results.

369
00:17:01.120 --> 00:17:04.440
<v Speaker 1>It's like this invisible force it is that's shaping our

370
00:17:04.480 --> 00:17:05.880
<v Speaker 1>calculations behind the scenes.

371
00:17:06.039 --> 00:17:09.319
<v Speaker 2>Yeah. Another common pitfall using the wrong data type.

372
00:17:09.480 --> 00:17:09.920
<v Speaker 1>Okay.

373
00:17:10.160 --> 00:17:13.480
<v Speaker 2>DAX is really strict about data types, got it. So

374
00:17:13.519 --> 00:17:16.839
<v Speaker 2>if you try to perform a calculation on incompatible data types,

375
00:17:17.440 --> 00:17:18.400
<v Speaker 2>you're going to get an error.

376
00:17:18.440 --> 00:17:20.920
<v Speaker 1>Okay, So paying attention to data types is crucial.

377
00:17:21.039 --> 00:17:24.160
<v Speaker 2>Yeah, it's like using the right ingredients in a recipe.

378
00:17:23.799 --> 00:17:26.519
<v Speaker 1>Right, Okay. Any other DAX traps to watch out for.

379
00:17:27.079 --> 00:17:30.960
<v Speaker 2>Over using variables. Variables can be helpful, yeah, but using

380
00:17:31.000 --> 00:17:34.720
<v Speaker 2>too many can make your code harder to read, okay

381
00:17:34.759 --> 00:17:35.400
<v Speaker 2>and debug.

382
00:17:35.519 --> 00:17:39.279
<v Speaker 1>So it's all about finding that balance, using them strategically

383
00:17:39.640 --> 00:17:41.880
<v Speaker 1>without overcomplicating things exactly.

384
00:17:41.920 --> 00:17:46.720
<v Speaker 2>And lastly, yeah, don't be afraid to experiment. Okay, that's

385
00:17:46.759 --> 00:17:50.039
<v Speaker 2>the best way to learn DAX. Yeah, try different functions,

386
00:17:50.480 --> 00:17:52.119
<v Speaker 2>combine them, see what happens.

387
00:17:52.200 --> 00:17:55.559
<v Speaker 1>I love that advice. It's all about embracing the spirit

388
00:17:55.640 --> 00:17:58.319
<v Speaker 1>of exploration it is, and not being afraid to make

389
00:17:58.359 --> 00:17:59.359
<v Speaker 1>mistakes along the way.

390
00:17:59.640 --> 00:17:59.960
<v Speaker 2>Exactly.

391
00:18:00.240 --> 00:18:03.559
<v Speaker 1>That's how we learn. Yeah, this has been an incredible journey.

392
00:18:03.640 --> 00:18:06.759
<v Speaker 1>It has we explored the key components of power BI,

393
00:18:07.279 --> 00:18:10.559
<v Speaker 1>the DAX functions, and even how to combine them. I'm

394
00:18:10.559 --> 00:18:14.559
<v Speaker 1>feeling inspired and empowered to tackle my own data challenges

395
00:18:14.559 --> 00:18:16.640
<v Speaker 1>with DAX as my trustee. Sidekick.

396
00:18:16.759 --> 00:18:17.359
<v Speaker 2>That's great.

397
00:18:17.480 --> 00:18:20.880
<v Speaker 1>You've certainly succeeded in sparking that enthusiasm in me. I'm

398
00:18:20.880 --> 00:18:23.279
<v Speaker 1>glad this has been a fantastic exploration.

399
00:18:23.559 --> 00:18:24.200
<v Speaker 2>It has been.

400
00:18:24.319 --> 00:18:26.440
<v Speaker 1>I can't wait to put all this knowledge into practice.

401
00:18:26.480 --> 00:18:28.319
<v Speaker 1>Me too, awesome. This has been fun.

402
00:18:28.480 --> 00:18:30.599
<v Speaker 2>Yeah, it has. Thanks for having me, and.

403
00:18:30.480 --> 00:18:33.039
<v Speaker 1>To all of our listeners out there. Until next time,

404
00:18:33.400 --> 00:18:34.519
<v Speaker 1>happy analyzing.