Data Analysis Expressions (DAX) - Simply Explained

1
00:00:00,000 --> 00:00:03,440
Today's topic is one that almost everyone working with Power BI has heard of,

2
00:00:03,440 --> 00:00:05,400
but hardly anyone can actually explain.

3
00:00:05,400 --> 00:00:06,680
What exactly is DAX?

4
00:00:06,680 --> 00:00:10,080
Is it just Excel formulas inside Power BI or is it something completely different?

5
00:00:10,080 --> 00:00:13,600
Here's the thing, most people think DAX is basically Excel formulas with a different name,

6
00:00:13,600 --> 00:00:14,520
but it isn't.

7
00:00:14,520 --> 00:00:17,360
DAX is a whole different way of thinking about calculations.

8
00:00:17,360 --> 00:00:20,480
If you try to treat it like Excel, you'll end up frustrated and confused,

9
00:00:20,480 --> 00:00:22,520
wondering why your numbers don't add up.

10
00:00:22,520 --> 00:00:26,000
By the end of this episode, you'll know what DAX actually is, why it matters,

11
00:00:26,000 --> 00:00:28,880
and the core concepts you need to start using it with confidence.

12
00:00:28,880 --> 00:00:31,120
Grab your coffee and let's dive in.

13
00:00:31,120 --> 00:00:32,880
What exactly is DAX?

14
00:00:32,880 --> 00:00:35,240
DAX stands for data analysis expressions,

15
00:00:35,240 --> 00:00:39,280
and it's the formula language used inside Power BI, Power Pivot in Excel,

16
00:00:39,280 --> 00:00:41,360
and SQL Server Analysis Services.

17
00:00:41,360 --> 00:00:44,840
Think of it as the engine that powers your calculations behind the scenes.

18
00:00:44,840 --> 00:00:46,120
DAX isn't new.

19
00:00:46,120 --> 00:00:49,800
It's been around since Excel Power Pivot was introduced over a decade ago,

20
00:00:49,800 --> 00:00:54,280
but with Power BI, it's become the go-to language for anyone serious about data analysis.

21
00:00:54,280 --> 00:00:55,600
And here's what makes it different.

22
00:00:55,600 --> 00:00:58,880
In Excel, you write formulas that work with individual cells.

23
00:00:58,880 --> 00:01:02,400
Point to A1, multiply by B1, and you get a result for one cell.

24
00:01:02,400 --> 00:01:03,640
DAX doesn't work that way.

25
00:01:03,640 --> 00:01:05,840
It works with entire tables and columns at once.

26
00:01:05,840 --> 00:01:07,600
You're not calculating one cell at a time.

27
00:01:07,600 --> 00:01:10,960
Instead, you tell the engine, take this whole column of numbers and sum it up,

28
00:01:10,960 --> 00:01:14,320
this or filter this entire table based on this condition.

29
00:01:14,320 --> 00:01:15,520
So how do you picture it?

30
00:01:15,520 --> 00:01:17,600
Imagine your building and office building.

31
00:01:17,600 --> 00:01:20,520
Power BI gives you the structure, the floors, walls, and rooms.

32
00:01:20,520 --> 00:01:21,680
That's your data model.

33
00:01:21,680 --> 00:01:23,240
DAX is the wiring and the plumbing.

34
00:01:23,240 --> 00:01:24,480
It's what makes everything work.

35
00:01:24,480 --> 00:01:28,560
Without DAX, you have a building that looks nice, but has no electricity or running water.

36
00:01:28,560 --> 00:01:32,600
With it, the lights turn on, the faucets work, and your reports actually respond when someone

37
00:01:32,600 --> 00:01:33,800
clicks a slicer.

38
00:01:33,800 --> 00:01:35,560
That responsiveness is the whole point.

39
00:01:35,560 --> 00:01:40,000
DAX lets you create calculations that automatically react to filters and interactions.

40
00:01:40,000 --> 00:01:43,280
Build one measure, and it shows the right number, whether someone's looking at total

41
00:01:43,280 --> 00:01:45,440
sales by region, by product, or by month.

42
00:01:45,440 --> 00:01:46,520
It just works.

43
00:01:46,520 --> 00:01:49,920
But to use it well, you need to understand the two main ways to work with DAX.

44
00:01:49,920 --> 00:01:51,560
Let's break those down.

45
00:01:51,560 --> 00:01:54,880
The two worlds, calculated columns versus measures.

46
00:01:54,880 --> 00:01:59,000
Today we are talking about two building blocks and DAXs, calculated columns and measures.

47
00:01:59,000 --> 00:02:01,200
Most people think they are the same thing, they aren't.

48
00:02:01,200 --> 00:02:03,880
Mixing them up is one of the most common mistakes I see.

49
00:02:03,880 --> 00:02:05,680
A calculated column is static.

50
00:02:05,680 --> 00:02:07,160
It adds a new column to your table.

51
00:02:07,160 --> 00:02:10,000
It fills up during a refresh, and then it sits there.

52
00:02:10,000 --> 00:02:11,960
It doesn't change until the next refresh.

53
00:02:11,960 --> 00:02:13,880
Here's the simplest way to think about it.

54
00:02:13,880 --> 00:02:15,120
Imagine a filing cabinet.

55
00:02:15,120 --> 00:02:18,640
You put a piece of paper in a folder that paper has the customer's full name on it.

56
00:02:18,640 --> 00:02:21,120
It doesn't matter if you look at the file today or next week.

57
00:02:21,120 --> 00:02:22,120
The name is still there.

58
00:02:22,120 --> 00:02:23,120
That is a calculated column.

59
00:02:23,120 --> 00:02:24,840
A measure is completely different.

60
00:02:24,840 --> 00:02:26,080
It doesn't sit in the table.

61
00:02:26,080 --> 00:02:27,720
It lives in the memory of your report.

62
00:02:27,720 --> 00:02:33,080
Every single time someone filters, slices or clicks a visual, the measure wakes up and recalculates.

63
00:02:33,080 --> 00:02:34,080
Why does this matter?

64
00:02:34,080 --> 00:02:38,120
If a user clicks a slicer for the North East region, a measure recalculates instantly.

65
00:02:38,120 --> 00:02:40,280
A calculated column ignores the slicer.

66
00:02:40,280 --> 00:02:41,280
It has to.

67
00:02:41,280 --> 00:02:43,120
It is static.

68
00:02:43,120 --> 00:02:44,280
Here is the simple rule.

69
00:02:44,280 --> 00:02:48,160
If the number needs to change when someone clicks a button, use a measure.

70
00:02:48,160 --> 00:02:51,800
If it is the same no matter what, a calculated column is fine.

71
00:02:51,800 --> 00:02:53,640
Total sales should always be a measure.

72
00:02:53,640 --> 00:02:57,040
If someone filters by region or by month, the total must update.

73
00:02:57,040 --> 00:02:58,280
Customer full name is a column.

74
00:02:58,280 --> 00:02:59,280
It never changes.

75
00:02:59,280 --> 00:03:00,280
A slicer won't touch it.

76
00:03:00,280 --> 00:03:02,320
Now, here is where most beginners get confused.

77
00:03:02,320 --> 00:03:04,280
They create calculated columns for everything.

78
00:03:04,280 --> 00:03:07,040
It feels natural because that is how Excel works.

79
00:03:07,040 --> 00:03:09,000
But overusing columns bloats your model.

80
00:03:09,000 --> 00:03:10,880
Your reports become slow.

81
00:03:10,880 --> 00:03:14,360
Calculated columns create static numbers that ignore your users.

82
00:03:14,360 --> 00:03:16,120
Measures are almost always the better choice.

83
00:03:16,120 --> 00:03:17,120
They are dynamic.

84
00:03:17,120 --> 00:03:18,560
They respond.

85
00:03:18,560 --> 00:03:22,360
Once you understand this difference, you are ready for the real magic of DAX.

86
00:03:22,360 --> 00:03:24,680
That magic is called filter context.

87
00:03:24,680 --> 00:03:26,360
The big idea, filter context.

88
00:03:26,360 --> 00:03:28,280
OK, we have our two building blocks.

89
00:03:28,280 --> 00:03:30,400
Now let's talk about the engine behind them.

90
00:03:30,400 --> 00:03:34,520
Every number you see in a Power BI report is calculated inside something called filter context.

91
00:03:34,520 --> 00:03:36,760
It sounds complicated, but it is simple.

92
00:03:36,760 --> 00:03:40,400
Filter context is just the set of filters active at that exact moment.

93
00:03:40,400 --> 00:03:41,400
Where do those filters come from?

94
00:03:41,400 --> 00:03:42,920
They come from the slices on your page.

95
00:03:42,920 --> 00:03:44,680
They come from the visual you clicked.

96
00:03:44,680 --> 00:03:46,400
They come from the page itself.

97
00:03:46,400 --> 00:03:49,040
And the row you are looking at creates a filter context.

98
00:03:49,040 --> 00:03:50,120
Here is why you should care.

99
00:03:50,120 --> 00:03:53,560
When you write a measure like total sales, you are not telling it to look at the northeast

100
00:03:53,560 --> 00:03:54,880
region in Q3.

101
00:03:54,880 --> 00:03:56,720
You just tell it to add up the sales column.

102
00:03:56,720 --> 00:03:57,720
That is it.

103
00:03:57,720 --> 00:03:59,120
The measure doesn't know about regions.

104
00:03:59,120 --> 00:04:00,600
It doesn't know about quarters.

105
00:04:00,600 --> 00:04:03,440
It just adds up whatever rows are currently visible.

106
00:04:03,440 --> 00:04:06,360
The filter context decides what is visible.

107
00:04:06,360 --> 00:04:07,520
Imagine a spotlight.

108
00:04:07,520 --> 00:04:08,840
Your whole data set is the room.

109
00:04:08,840 --> 00:04:10,560
The filter context is the spotlight.

110
00:04:10,560 --> 00:04:13,720
The measure is a camera taking a picture of exactly what the light hits.

111
00:04:13,720 --> 00:04:15,600
Move the spotlight to the northeast region.

112
00:04:15,600 --> 00:04:17,400
The measure recalculates, move it to Q3.

113
00:04:17,400 --> 00:04:18,560
It recalculates again.

114
00:04:18,560 --> 00:04:19,720
You didn't change the formula.

115
00:04:19,720 --> 00:04:21,080
The context changed.

116
00:04:21,080 --> 00:04:22,640
This confuses most people.

117
00:04:22,640 --> 00:04:23,640
They write a measure.

118
00:04:23,640 --> 00:04:25,080
It looks right in one visual.

119
00:04:25,080 --> 00:04:28,680
Then they put it in a matrix with different filters and the number changes.

120
00:04:28,680 --> 00:04:30,040
They think something is broken.

121
00:04:30,040 --> 00:04:31,040
It is not broken.

122
00:04:31,040 --> 00:04:32,840
That is exactly what a measure is supposed to do.

123
00:04:32,840 --> 00:04:33,840
It is dynamic.

124
00:04:33,840 --> 00:04:34,840
This is the real power.

125
00:04:34,840 --> 00:04:38,520
You write one formula and it works for every possible filter combination in your entire

126
00:04:38,520 --> 00:04:39,520
report.

127
00:04:39,520 --> 00:04:40,520
One formula.

128
00:04:40,520 --> 00:04:41,520
Infinite answers.

129
00:04:41,520 --> 00:04:42,880
But you must be careful.

130
00:04:42,880 --> 00:04:45,960
If you don't know what filters are active, you won't understand your number.

131
00:04:45,960 --> 00:04:47,400
You need to trace the spotlight.

132
00:04:47,400 --> 00:04:51,280
Get comfortable with filter context and you unlock the real power of DAX.

133
00:04:51,280 --> 00:04:53,160
And that brings us to the star of the show.

134
00:04:53,160 --> 00:04:54,480
The Calculate function.

135
00:04:54,480 --> 00:04:55,800
The most important function.

136
00:04:55,800 --> 00:04:56,800
Calculate.

137
00:04:56,800 --> 00:04:57,800
Welcome back.

138
00:04:57,800 --> 00:05:00,880
Today's knowledge nugget is about the single most important function in DAX.

139
00:05:00,880 --> 00:05:02,080
It's called Calculate.

140
00:05:02,080 --> 00:05:05,240
If you only master one function, make it this one.

141
00:05:05,240 --> 00:05:06,240
Calculate.

142
00:05:06,240 --> 00:05:09,880
Let's you control filter context instead of just accepting whatever filters are active.

143
00:05:09,880 --> 00:05:11,080
Here's how normal measures work.

144
00:05:11,080 --> 00:05:14,680
When you write a measure, it respects the current filter context automatically.

145
00:05:14,680 --> 00:05:19,000
If someone selects 2024 from a slicer, your measure shows 2024 numbers.

146
00:05:19,000 --> 00:05:21,920
If they choose the northeast region, it shows northeast numbers.

147
00:05:21,920 --> 00:05:24,320
The measure responds to whatever filters are in play.

148
00:05:24,320 --> 00:05:25,320
No extra work needed.

149
00:05:25,320 --> 00:05:26,640
That's the default behavior.

150
00:05:26,640 --> 00:05:29,720
But what if you need a number that ignores the current filters?

151
00:05:29,720 --> 00:05:34,360
Maybe you want total sales for all regions right next to sales for the selected region?

152
00:05:34,360 --> 00:05:37,480
Or you need last year's sales while someone is looking at this year.

153
00:05:37,480 --> 00:05:39,720
In those cases, you can't rely on the default context.

154
00:05:39,720 --> 00:05:40,720
You need to modify it.

155
00:05:40,720 --> 00:05:42,200
That's exactly what Calculate does.

156
00:05:42,200 --> 00:05:44,960
Calculate works by temporarily changing the filter context.

157
00:05:44,960 --> 00:05:49,160
You give it an expression to calculate and then you give it one or more filters to apply.

158
00:05:49,160 --> 00:05:52,520
It changes the context, runs the calculation and returns the result.

159
00:05:52,520 --> 00:05:54,520
The original filter context stays untouched.

160
00:05:54,520 --> 00:05:55,520
That's the magic.

161
00:05:55,520 --> 00:05:56,760
The syntax is simple.

162
00:05:56,760 --> 00:06:01,040
You write calculate, expression, filter one, filter two.

163
00:06:01,040 --> 00:06:03,440
The expression is usually a measure you've already created.

164
00:06:03,440 --> 00:06:05,800
The filters are the conditions you want to apply.

165
00:06:05,800 --> 00:06:07,920
You can add as many filters as you need.

166
00:06:07,920 --> 00:06:08,920
Let's see it in action.

167
00:06:08,920 --> 00:06:10,960
Suppose you have a measure called total sales.

168
00:06:10,960 --> 00:06:12,760
It shows sales for the selected period.

169
00:06:12,760 --> 00:06:15,880
To show sales for the same period last year, you write calculate.

170
00:06:15,880 --> 00:06:18,600
Total sales, same period last year calendar date.

171
00:06:18,600 --> 00:06:19,600
That's all it takes.

172
00:06:19,600 --> 00:06:23,400
Calculate takes your existing measure and shifts the date context back one year.

173
00:06:23,400 --> 00:06:25,120
You get the comparison you need in one line.

174
00:06:25,120 --> 00:06:28,840
Without calculate, you're stuck with whatever filter context exists.

175
00:06:28,840 --> 00:06:32,760
With Calculate, you can override filters, add new ones, or remove existing ones.

176
00:06:32,760 --> 00:06:36,600
You can request total sales for all products even when someone filters to a specific category.

177
00:06:36,600 --> 00:06:39,800
You can pull sales from last month when the report shows this month.

178
00:06:39,800 --> 00:06:41,240
The control is in your hands.

179
00:06:41,240 --> 00:06:44,080
About 80% of advanced DAX relies on calculate.

180
00:06:44,080 --> 00:06:48,080
Time intelligence, dynamic comparisons, custom aggregations, running totals, they all

181
00:06:48,080 --> 00:06:49,840
use calculate under the hood.

182
00:06:49,840 --> 00:06:53,800
Getting comfortable with this single function gives you enormous power over your reports.

183
00:06:53,800 --> 00:06:55,880
But calculate works at the filter level.

184
00:06:55,880 --> 00:06:58,000
What about row by row logic inside a measure?

185
00:06:58,000 --> 00:07:00,400
That's where a different set of functions comes in.

186
00:07:00,400 --> 00:07:03,240
Iterator functions when you need to think row by row.

187
00:07:03,240 --> 00:07:05,600
So calculate handles filter level changes beautifully.

188
00:07:05,600 --> 00:07:09,680
Sometimes you need to step inside a table and compute on each row individually inside a

189
00:07:09,680 --> 00:07:10,680
measure.

190
00:07:10,680 --> 00:07:11,720
A simple sum can't do that.

191
00:07:11,720 --> 00:07:14,440
You need something that walks through the table row by row.

192
00:07:14,440 --> 00:07:15,800
Here's a common situation.

193
00:07:15,800 --> 00:07:19,200
You have a sales table with quantity and unit price in separate columns.

194
00:07:19,200 --> 00:07:20,920
You want a measure that shows total revenue.

195
00:07:20,920 --> 00:07:24,080
You can't just multiply some quantity by some unit price.

196
00:07:24,080 --> 00:07:26,800
That multiplies the totals, not the individual rows.

197
00:07:26,800 --> 00:07:31,640
You need to multiply each row's quantity by its unit price, then add up all those results.

198
00:07:31,640 --> 00:07:34,520
That's row by row math.

199
00:07:34,520 --> 00:07:36,200
Operator functions are built for this.

200
00:07:36,200 --> 00:07:41,920
They're the functions that end in x, SUMX, average x, count x, minx, max.

201
00:07:41,920 --> 00:07:43,600
The x stands for expression.

202
00:07:43,600 --> 00:07:47,720
These functions take a table, walk through every single row, perform a calculation on each

203
00:07:47,720 --> 00:07:50,800
row, and then aggregate the results into a single value.

204
00:07:50,800 --> 00:07:56,840
For the revenue example, you write SUMX, sales, sales quantity, sales unit price.

205
00:07:56,840 --> 00:07:59,600
The first argument is the table to iterate over.

206
00:07:59,600 --> 00:08:02,960
The second argument is the expression to evaluate on each row.

207
00:08:02,960 --> 00:08:07,640
SUMX goes row by row, multiplies quantity times price, and sums everything up.

208
00:08:07,640 --> 00:08:11,560
One measure gives you the correct total revenue, simple and direct.

209
00:08:11,560 --> 00:08:16,000
Use iterators whenever your calculation involves multiple columns from the same row.

210
00:08:16,000 --> 00:08:19,520
Profit per line item, discounted price, weighted scores.

211
00:08:19,520 --> 00:08:24,080
Anytime the math requires row by row logic inside a measure, an iterator is your tool, but

212
00:08:24,080 --> 00:08:25,680
iterators have a performance cost.

213
00:08:25,680 --> 00:08:29,920
They walk through every row, so overusing them on tables with millions of rows can slow

214
00:08:29,920 --> 00:08:31,680
your reports to a crawl.

215
00:08:31,680 --> 00:08:35,760
The simple rule only uses an iterator when you actually need row by row logic.

216
00:08:35,760 --> 00:08:39,240
If a simple sum or average gives you the right answer, use that instead.

217
00:08:39,240 --> 00:08:40,480
Your reports will thank you.

218
00:08:40,480 --> 00:08:41,480
One more tip.

219
00:08:41,480 --> 00:08:45,520
Sometimes a calculated column plus a simple sum is faster than SUMX.

220
00:08:45,520 --> 00:08:49,480
If you create a calculated column that does the row level math once at refresh time and

221
00:08:49,480 --> 00:08:53,760
then use a regular sum on that column, you avoid paying the iterator cost every time someone

222
00:08:53,760 --> 00:08:55,320
views the report.

223
00:08:55,320 --> 00:08:57,280
Test both approaches in your own data.

224
00:08:57,280 --> 00:09:01,120
The fastest option depends on your model size and how often the data changes.

225
00:09:01,120 --> 00:09:02,120
Use wisely.

226
00:09:02,120 --> 00:09:03,600
Time intelligence made simple.

227
00:09:03,600 --> 00:09:08,280
Now let's talk about one of the most requested features in Power BI, comparing time periods.

228
00:09:08,280 --> 00:09:10,560
How are sales this month compared to last month?

229
00:09:10,560 --> 00:09:12,720
How's the year tracking against last year?

230
00:09:12,720 --> 00:09:15,280
These questions come up in almost every business report.

231
00:09:15,280 --> 00:09:17,600
And DAX has functions built just for that.

232
00:09:17,600 --> 00:09:22,080
Time intelligence functions handle year-to-day totals, same period last year, rolling averages

233
00:09:22,080 --> 00:09:23,920
and month over month changes.

234
00:09:23,920 --> 00:09:28,080
They're part of DAX, so you don't have to write complex filter logic from scratch, but

235
00:09:28,080 --> 00:09:29,320
here's the catch.

236
00:09:29,320 --> 00:09:32,000
Time intelligence functions need a proper date table.

237
00:09:32,000 --> 00:09:35,600
Not just any date column from your fact table, you need a separate table with one row per

238
00:09:35,600 --> 00:09:40,080
day covering your full date range with no gaps and it has to be marked as a date table

239
00:09:40,080 --> 00:09:41,080
in your model.

240
00:09:41,080 --> 00:09:44,120
Skip that step and the functions simply won't work.

241
00:09:44,120 --> 00:09:48,560
Once you have that date table set up, the most useful functions are totaly TD, SAMHIPERIOD,

242
00:09:48,560 --> 00:09:50,800
last year, data and dates between.

243
00:09:50,800 --> 00:09:51,960
Total ITD is the simplest.

244
00:09:51,960 --> 00:09:54,760
You write total ITD, total sales, calendar date.

245
00:09:54,760 --> 00:09:58,840
And you get a running total from the start of the year through whatever date is in context.

246
00:09:58,840 --> 00:10:02,640
Keep it in a line chart by month and you'll see that cumulative total grow as the year goes

247
00:10:02,640 --> 00:10:03,640
on.

248
00:10:03,640 --> 00:10:05,800
Same period last year does exactly what it sounds like.

249
00:10:05,800 --> 00:10:09,720
It shifts the date context back one year, you pair it with calculate like this.

250
00:10:09,720 --> 00:10:13,520
Calculate, total sales, same period last year calendar date.

251
00:10:13,520 --> 00:10:17,760
Now you have last year's sales sitting right next to this year's numbers for easy comparison.

252
00:10:17,760 --> 00:10:19,000
That add is even more flexible.

253
00:10:19,000 --> 00:10:22,040
It lets you shift by days, months, quarters or years.

254
00:10:22,040 --> 00:10:26,000
You can compare this quarter to the same quarter two years ago or see last month's numbers

255
00:10:26,000 --> 00:10:27,600
by using minus one month.

256
00:10:27,600 --> 00:10:29,760
That's the Swiss Army knife of time shifting.

257
00:10:29,760 --> 00:10:32,720
The most common mistake people make is forgetting to mark their date table.

258
00:10:32,720 --> 00:10:36,720
You can have a perfect date table with every day from 2020 through 2030.

259
00:10:36,720 --> 00:10:40,680
But if you don't mark it as a date table in the model, the functions will return errors

260
00:10:40,680 --> 00:10:41,680
or blanks.

261
00:10:41,680 --> 00:10:42,680
It's a simple setting.

262
00:10:42,680 --> 00:10:45,640
Right click the table, choose Mark as date table and pick the date column.

263
00:10:45,640 --> 00:10:47,520
Do that once and everything starts working.

264
00:10:47,520 --> 00:10:51,760
Another mistake is using time intelligence functions on a date column inside your fact table

265
00:10:51,760 --> 00:10:53,760
instead of a dedicated date table.

266
00:10:53,760 --> 00:10:58,520
The functions expect a continuous gap free date range and fact tables almost never have that.

267
00:10:58,520 --> 00:11:03,240
So build a proper date table, market and your time intelligence will work smoothly.

268
00:11:03,240 --> 00:11:04,680
Common beginner pitfalls.

269
00:11:04,680 --> 00:11:07,720
Even with the right functions, beginners still hit common traps.

270
00:11:07,720 --> 00:11:10,880
Let me walk you through the biggest ones so you can avoid them from day one.

271
00:11:10,880 --> 00:11:15,040
The number one source of bugs and daxes is confusing row context with filter context.

272
00:11:15,040 --> 00:11:19,240
Earlier we talked about how calculated columns create row context, evaluating one row at

273
00:11:19,240 --> 00:11:23,360
a time while measures use filter context based on whatever filters are active.

274
00:11:23,360 --> 00:11:27,400
beginners often write a measure as if it has access to individual rows, then wonder why

275
00:11:27,400 --> 00:11:28,880
the results are wrong.

276
00:11:28,880 --> 00:11:32,800
If your measure needs to see individual rows, use an iterator function.

277
00:11:32,800 --> 00:11:34,280
If not, keep it simple.

278
00:11:34,280 --> 00:11:37,920
The second big mistake is using calculated columns when a measure would work.

279
00:11:37,920 --> 00:11:40,920
Calculated columns feel familiar because you can see the values in the table.

280
00:11:40,920 --> 00:11:45,520
But every calculated column makes your model larger and refreshes slower.

281
00:11:45,520 --> 00:11:49,080
Before adding one, ask yourself, does this value need to change when someone filters the

282
00:11:49,080 --> 00:11:50,080
report?

283
00:11:50,080 --> 00:11:51,640
If yes, it should be a measure.

284
00:11:51,640 --> 00:11:53,880
Another trap is ignoring the data model itself.

285
00:11:53,880 --> 00:11:55,160
Dax doesn't work in isolation.

286
00:11:55,160 --> 00:11:57,920
It depends on the relationships between your tables.

287
00:11:57,920 --> 00:12:02,760
If your model has weak relationships, wrong cardinality or missing join keys, your dax formulas

288
00:12:02,760 --> 00:12:03,760
will fail silently.

289
00:12:03,760 --> 00:12:06,280
You'll get numbers that look plausible but are actually wrong.

290
00:12:06,280 --> 00:12:07,520
The fix isn't better, dax.

291
00:12:07,520 --> 00:12:08,680
Fix your model first.

292
00:12:08,680 --> 00:12:10,560
Then there's misusing calculate.

293
00:12:10,560 --> 00:12:14,280
Beginners add multiple filters inside calculate without realizing they might cancel each

294
00:12:14,280 --> 00:12:15,400
other out.

295
00:12:15,400 --> 00:12:20,040
For example, a filter for sales for 2024 and another for sales from last year conflict,

296
00:12:20,040 --> 00:12:21,840
and the result won't be what you expect.

297
00:12:21,840 --> 00:12:24,480
Each filter inside calculate should have a clear purpose.

298
00:12:24,480 --> 00:12:27,800
If you're not sure what it's doing, test it in isolation first.

299
00:12:27,800 --> 00:12:30,360
One more common mistake is not using variables.

300
00:12:30,360 --> 00:12:34,040
Variables declared with VR are let you store intermediate results and reuse them later

301
00:12:34,040 --> 00:12:35,360
in the same measure.

302
00:12:35,360 --> 00:12:38,920
Without them, you repeat the same calculation multiple times in one formula, making your

303
00:12:38,920 --> 00:12:41,760
code harder to read, harder to debug and slower to run.

304
00:12:41,760 --> 00:12:43,520
Get in the habit of using VR early.

305
00:12:43,520 --> 00:12:47,240
It will save you hours of frustration and finally always validate your measures in different

306
00:12:47,240 --> 00:12:48,240
contexts.

307
00:12:48,240 --> 00:12:52,520
A measure that works perfectly in a card visual might break in a matrix total.

308
00:12:52,520 --> 00:12:56,000
That's because the total row evaluates in a different filter context than the detail

309
00:12:56,000 --> 00:12:57,000
rows.

310
00:12:57,000 --> 00:13:01,000
Test your measures in tables and cards in matrices and with different slicer combinations.

311
00:13:01,000 --> 00:13:05,680
If a measure holds up across all those scenarios, you know it's solid.

312
00:13:05,680 --> 00:13:06,680
Putting it all together.

313
00:13:06,680 --> 00:13:09,840
Alright, let's walk through a real example that pulls everything together.

314
00:13:09,840 --> 00:13:13,600
Imagine you're building a sales report and you need to show three numbers.

315
00:13:13,600 --> 00:13:17,160
Total sales for this period total sales for the same period last year and the growth

316
00:13:17,160 --> 00:13:18,960
percentage between them.

317
00:13:18,960 --> 00:13:20,840
First thing you need is a clean date table.

318
00:13:20,840 --> 00:13:25,520
Go into your model, create a date table that covers your full date range, one row per day,

319
00:13:25,520 --> 00:13:29,680
no gaps, then right click it, choose Marcus date table and pick the date column.

320
00:13:29,680 --> 00:13:33,920
Do that once and every time intelligence function you write from there on will just work.

321
00:13:33,920 --> 00:13:38,560
Next, write your base measure, call it total sales and type total sales, it will sum sales

322
00:13:38,560 --> 00:13:39,560
amount.

323
00:13:39,560 --> 00:13:43,720
That's the foundation and every other calculation will build on this one simple line.

324
00:13:43,720 --> 00:13:47,960
Then you write the prior year measure, use calculate with same period last year.

325
00:13:47,960 --> 00:13:52,880
It looks like this, prior year sales, epitome, calculate total sales, same period last year,

326
00:13:52,880 --> 00:13:54,480
calendar date.

327
00:13:54,480 --> 00:13:58,800
Now you've got current year sitting right next to last year, now the growth percentage.

328
00:13:58,800 --> 00:14:04,440
Use divide to handle cases where last year sales might be zero and they will be.

329
00:14:04,440 --> 00:14:10,920
Write it like this, growth percent is divide, total sales, prior year sales, prior year sales.

330
00:14:10,920 --> 00:14:15,200
It safely returns a blank instead of an error if the denominator is zero.

331
00:14:15,200 --> 00:14:17,040
That's the smart way to avoid crashes.

332
00:14:17,040 --> 00:14:20,880
Now you have three reusable measures that work across any filter you throw at them.

333
00:14:20,880 --> 00:14:24,720
Drop them into a table by product, by region, by month, they'll respond correctly every

334
00:14:24,720 --> 00:14:25,720
single time.

335
00:14:25,720 --> 00:14:26,720
That's the whole point.

336
00:14:26,720 --> 00:14:30,600
Build on a clean model, master a handful of core functions and the rest falls into place.

337
00:14:30,600 --> 00:14:34,240
So don't try to write complex stacks on day one, get your model right first, learn the

338
00:14:34,240 --> 00:14:38,640
basics, measures, filter context, calculate.

339
00:14:38,640 --> 00:14:42,160
And you'll solve most business problems with just a few lines of code.

340
00:14:42,160 --> 00:14:43,160
So here's where we land.

341
00:14:43,160 --> 00:14:46,880
You know what DAX is, why filter context matters and how calculate and iterator functions

342
00:14:46,880 --> 00:14:47,880
do their thing.

343
00:14:47,880 --> 00:14:50,520
You've seen the common traps and how to step around them.

344
00:14:50,520 --> 00:14:52,960
And we walk through a real example that ties it all together.

345
00:14:52,960 --> 00:14:56,400
The real power of DAX isn't about memorizing 100 functions.

346
00:14:56,400 --> 00:14:58,800
It's about clean data modeling and a few core patterns.

347
00:14:58,800 --> 00:15:01,640
Get the model right and the DAX becomes almost obvious.

348
00:15:01,640 --> 00:15:04,480
Here's what I'd suggest you try, open power BI.

349
00:15:04,480 --> 00:15:08,600
Build a simple model with a date table, a sales table and a product table.

350
00:15:08,600 --> 00:15:10,720
Then write one measure each day for a week.

351
00:15:10,720 --> 00:15:14,400
Start with total sales, add prior year sales, add growth percentage, then try a running

352
00:15:14,400 --> 00:15:15,400
total.

353
00:15:15,400 --> 00:15:18,440
By the end of the week, you'll be writing measures without even thinking about it.

354
00:15:18,440 --> 00:15:19,440
That's how it clicks.

355
00:15:19,440 --> 00:15:22,560
If this episode helped, subscribe and keep building that knowledge.

356
00:15:22,560 --> 00:15:25,800
Next time we'll talk about debugging DAX when things go wrong.

357
00:15:25,800 --> 00:15:26,800
Because they will.

358
00:15:26,800 --> 00:15:28,720
And knowing how to fix them is half the battle.