YouTube2Text

The 8 Biggest Mistakes Behind 80% of Report Problems — Transcript

by Goodly · 2,889 words · 387 segments · language en · Watch on YouTube

Full transcript

  1. 0:00In this video, I'm going to talk about
  2. 0:01some of the stupidest mistakes that I
  3. 0:03make in PowerBI over and over and over
  4. 0:06again. Every single time I make these
  5. 0:08mistakes, I want to kick myself on the
  6. 0:10butt. After watching the video, if you
  7. 0:12share the same feeling, let me save you
  8. 0:14some butt kicks. Let's start.
  9. 0:17[Music]
  10. 0:18Stupid mistake number one. Let's just
  11. 0:20say that I build this visual year and
  12. 0:22the quarter in the slicer and then
  13. 0:24months over year in the rows right here.
  14. 0:25And against that I drag my total sales
  15. 0:27and I see that every single row is the
  16. 0:30same value. Now you could scratch your
  17. 0:31head and figure out what's going wrong
  18. 0:33in 5 hours or you can just check the
  19. 0:36relationships. Whenever you see the
  20. 0:38numbers repeating and they are just not
  21. 0:40changing and they are the same value. It
  22. 0:42most likely that you have forgotten to
  23. 0:44build the relationships. Otherwise the
  24. 0:46numbers are going to be blank. They're
  25. 0:48not going to be appearing. They're going
  26. 0:49to be incorrect perhaps but not the
  27. 0:51same. This simply means the data is not
  28. 0:54being filtered and the numbers therefore
  29. 0:56are the same. So now if I just go back
  30. 0:58to the model view, you can see that we
  31. 0:59have the calendar table and we have the
  32. 1:01sales table. And clearly I have missed
  33. 1:02the relationship creation between the
  34. 1:04two tables. And of course I can just go
  35. 1:06in here and I can take a look at my
  36. 1:07date. Drag the date over to the date
  37. 1:09column right here. The relationship is
  38. 1:10created many to one. Good to go. That's
  39. 1:13good. And now if I go back and take a
  40. 1:14look at my numbers, they are all good.
  41. 1:17Stupid mistake number two. And in my
  42. 1:19early days, I cannot even imagine how
  43. 1:21many times have I fallen for this.
  44. 1:23Remember, measures return single value.
  45. 1:26But if you try to commit them as a
  46. 1:27table, they are obviously going to give
  47. 1:28you errors. Let me show you what do I
  48. 1:30mean. So let's just say that you're
  49. 1:31trying to create a measure. And in the
  50. 1:32measure, you just go write something. So
  51. 1:34I'm just going to write maybe high value
  52. 1:36sales, right? And now if you try to
  53. 1:39think about the logic, you say something
  54. 1:40like, "Hey, I'm just trying to filter my
  55. 1:41sales table and I'm just trying to go in
  56. 1:43the sales table and just trying to pick
  57. 1:45up the unit price and pick up all the
  58. 1:46prices of the products which are above
  59. 1:48$1,000." That's what you're trying to
  60. 1:49do. As soon as you do that, this looks
  61. 1:51good and it is correct. And if you press
  62. 1:52enter, this is going to give you an
  63. 1:54error. Now, it doesn't even want it to
  64. 1:55be dragged to the visual. It gives you
  65. 1:57the error right here itself because what
  66. 2:00you have done is you have created a
  67. 2:02table inside of a measure and the
  68. 2:05measure necessarily needs a scalar or a
  69. 2:08single value. It's a table and a table
  70. 2:10cannot be fit into a measure. So you
  71. 2:11need to do something with the table all
  72. 2:13of the rows of the table which are
  73. 2:14beyond $1,000 and you need to wrap them
  74. 2:16up aggregate them up. You can do a count
  75. 2:18rows you can do a sum of the table you
  76. 2:20can do whatever of the table but don't
  77. 2:22leave the table just like that. Now if I
  78. 2:24happen to just maybe write the count
  79. 2:26rows function just want to find out how
  80. 2:27many rows are there. This measure is
  81. 2:28just going to work fine. But do not
  82. 2:30leave your measures with table
  83. 2:33expressions measures are scalar values.
  84. 2:36Quick introduction in the video in case
  85. 2:37you're liking the video so far and
  86. 2:39you're wondering that how can I learn
  87. 2:40these skills of writing good DAX good
  88. 2:43data modeling the M language behind
  89. 2:44Power Query and all of the nuances of
  90. 2:47PowerBI that makes it work. I have
  91. 2:49brilliant courses on PowerBI especially
  92. 2:51the Power Query part, the DAX part, data
  93. 2:53modeling part and the M language part.
  94. 2:55In the courses, I take you from a
  95. 2:57beginner level and take you to more
  96. 2:58advanced concepts. Try to explain you
  97. 3:00the logic of how things are working so
  98. 3:02that you feel confident in applying the
  99. 3:04learnings to even on your own data sets.
  100. 3:06Hundreds and hundreds of students have
  101. 3:08joined my courses and they have
  102. 3:09benefited a lot. In case you're
  103. 3:10interested, the link for the courses is
  104. 3:12going to be down in the description of
  105. 3:14the video. Let's get back to the video.
  106. 3:15Here is another one that has caused me a
  107. 3:18lot of pain and agony while debugging my
  108. 3:20tax problems just to find out that I
  109. 3:23just did not plug the instrument. I mean
  110. 3:25is as simple as that. Please take a
  111. 3:27look. So I've got this simple
  112. 3:28visualization which is I have categories
  113. 3:30and I have sales of those categories and
  114. 3:32I'm trying to find the category mix.
  115. 3:33That means what is this particular value
  116. 3:35divided by the total gives me as a
  117. 3:36percentage which is a very standard
  118. 3:37calculation. And I go ahead and write a
  119. 3:39measure for that. My measure is
  120. 3:40something like this. I take my total
  121. 3:42sales. Okay. I divide the sales by the
  122. 3:45total of the category which is total
  123. 3:46sales by remove filters of the product
  124. 3:48and the category. And this is by the way
  125. 3:50just the right measure. It's been
  126. 3:51working fine. I have learned it. It's
  127. 3:53working fine. But at the moment when I
  128. 3:54drag this off to my visual it gives me
  129. 3:56100% 100% 100% all the way through till
  130. 3:59the end. Now what's going wrong? A lot
  131. 4:01of times you would not realize that
  132. 4:03while in the development process you
  133. 4:04could tend to drag the wrong column in
  134. 4:07the visual and through that the filter
  135. 4:09that you're removing off in the DAX
  136. 4:11calculation does not get removed. Let me
  137. 4:13show you what do I mean. If you actually
  138. 4:15take a look at the visual and in the
  139. 4:16visual if you click on the thing right
  140. 4:17here you're going to see that the
  141. 4:19category that I have is not coming from
  142. 4:21the products table like it should as my
  143. 4:23measure also suggests. So if I just
  144. 4:25maybe take a look right here, this is
  145. 4:26actually coming off from my sales table
  146. 4:28as a category. For some reason, the
  147. 4:30category is also mentioned in the sales
  148. 4:32table and that is where it's being
  149. 4:33dragged from. Now this could be a
  150. 4:35genuine mistake. I did not intend to do
  151. 4:36it. I just saw the category and I
  152. 4:38dragged it. Now my calculation is not
  153. 4:39working. So before you start going down
  154. 4:42in the rabbit hole to fix your DAX,
  155. 4:43please just check where the columns are
  156. 4:46coming from. Are they coming from the
  157. 4:47right tables or not? Now if I happen to
  158. 4:49replace that and if I say, hey, this is
  159. 4:51not the correct category column that I
  160. 4:52would want. I would want it from the
  161. 4:53products table. So if I just made a
  162. 4:55click right here, as soon as I do that,
  163. 4:56you can see that it just get fixed. It's
  164. 4:57all good now. That's it. Here is another
  165. 4:59one of the innocuous mistake, but it is
  166. 5:02deadly. Take a look at this particular
  167. 5:04visual that I've made. I've got the
  168. 5:05colors and I've got the total sales.
  169. 5:06Just a while ago on the previous page, I
  170. 5:08saw that my sales of the all categories
  171. 5:11was about 11 million. And if I'm just
  172. 5:13trying to find out sales against the
  173. 5:14colors, of course, the colors are going
  174. 5:16to be different, but the total sales
  175. 5:17should match 11 million. But it's not.
  176. 5:19So I wonder what's going on. A lot of
  177. 5:20times you're going to find that you have
  178. 5:22unnecessary filters applied that are
  179. 5:24hidden behind the bookmarks or somewhere
  180. 5:26on the page which are not visible to
  181. 5:28you. So if I maybe take a look at this
  182. 5:29particular thing, there is a icon up on
  183. 5:32the top which is the filters icon. It
  184. 5:33literally was the filters icon. It
  185. 5:35changed it now to this. It doesn't look
  186. 5:37like a filter icon, but please hover
  187. 5:39your mouse on top of this icon. And you
  188. 5:41would be surprised what filters are
  189. 5:42applied to your visual through which the
  190. 5:44numbers are seeming incorrect. So if I
  191. 5:45just maybe hover my mouse on top of
  192. 5:47that, you're going to see that a few
  193. 5:48subcategories are excluded. I don't know
  194. 5:51where the filter is. It's all blank
  195. 5:53right here in the page, but I can see
  196. 5:54that few subcategories are excluded. A
  197. 5:56couple of things that you can do is you
  198. 5:57can build a new visual on a new page
  199. 5:59just to negate any kind of filters that
  200. 6:02might be there. You can look for hidden
  201. 6:03filters. Somebody must have created a
  202. 6:05bookmark or hidden the filter. Or you
  203. 6:07can also take a look at in the filter
  204. 6:09pane. So I have turned that off. Let me
  205. 6:10just turn that on. So I can go over to
  206. 6:12the view. In the view, I can just maybe
  207. 6:13turn on the filters pane. And if I just
  208. 6:15take a look inside of the filters pane,
  209. 6:16you can see that the subcategory are
  210. 6:18applied right here, which I do not
  211. 6:19really want. If I just cancel them out,
  212. 6:21the sales is back to the value that I
  213. 6:22needed. So look for the hidden filters.
  214. 6:24And the best way to look for those
  215. 6:26filters are this particular hover where
  216. 6:28you can take a look at are any filters
  217. 6:29applied to this visual which may be
  218. 6:31wanted sometimes are not wanted as well.
  219. 6:33Another very common mistake that I have
  220. 6:35found myself making is that when you are
  221. 6:37creating some calculation and the
  222. 6:38calculation is not giving you the
  223. 6:39expected answer, please by all means
  224. 6:42apply those filters physically on the
  225. 6:44data inside of PowerBI and please take a
  226. 6:47look that the data exists or not. If the
  227. 6:50data does not exist, it clearly gives
  228. 6:52you an indication that the data was lost
  229. 6:54in the ETL layer. The next logical step
  230. 6:56is that you go back to the data perhaps
  231. 6:58in the CSV or the Excel file and apply a
  232. 7:00filter over there. Is the data existing
  233. 7:02over there or not? If the data exists in
  234. 7:04the Excel file and does not exist in the
  235. 7:06PowerBI, there must be something that
  236. 7:09you might have done in Power Query so
  237. 7:11that the data was filtered out. And
  238. 7:13please look for such errors where you
  239. 7:15just check the validation or the
  240. 7:17existence of the data itself. And you'll
  241. 7:20be surprised how many times you will
  242. 7:22save yourself from nasty debugging.
  243. 7:24Another very common problem that a lot
  244. 7:26of people get stuck with and the PowerBI
  245. 7:28community is filled with questions as
  246. 7:31such which is incorrect totals of the
  247. 7:33matrix or the table visualization. The
  248. 7:35thing is that every single value
  249. 7:42in the table or the matrix is evaluated
  250. 7:44as per the context and the total
  251. 7:45although it looks like a total but it's
  252. 7:47not the sum of the rows. So whenever you
  253. 7:50are in the developer mode and trying to
  254. 7:51create calculations, it's imperative
  255. 7:53that you please take a look at every
  256. 7:55single value including the totals which
  257. 7:57is typically what your bosses are going
  258. 7:59to see first. Take a look at the totals
  259. 8:00and match it to the totals row or not.
  260. 8:02Now I've done videos in the past where I
  261. 8:04have spoken about this particular
  262. 8:06problem, why does it go wrong and also
  263. 8:08giving you some fixes around it. Please
  264. 8:10go take a look at that video in case you
  265. 8:12have not been able to do work around
  266. 8:14with totals in the past and you've
  267. 8:15struggled with that. The next most
  268. 8:16common mistake are the stupid blank
  269. 8:18values that you get in PowerBI. Now it
  270. 8:21might not be your problem as a developer
  271. 8:23of the report but it could be just the
  272. 8:26data inconsistencies and this is called
  273. 8:28referential integrity where something is
  274. 8:30there in the sales fact table or just
  275. 8:32the fact table and is not there in the
  276. 8:34products table. Now take a look right
  277. 8:35here I've got the simple visual and
  278. 8:37somehow there's a blank right here
  279. 8:38against which the 10 is appearing and I
  280. 8:40do not understand that where does this
  281. 8:4210 come from? What the heck is this
  282. 8:43blank? it is very likely that your sales
  283. 8:46table contains a few values which are
  284. 8:49missing from your products table. So
  285. 8:51what I can do is I can just go over one
  286. 8:53of the easiest ways to check it. I can
  287. 8:54just go over to the sales table. In the
  288. 8:56sales table I can just right click and I
  289. 8:58can just make a temporary column for the
  290. 9:00just a quick second and I'm just going
  291. 9:01to go inside of this column. I'm going
  292. 9:03to write a VLOOKUP and I'm going to get
  293. 9:05the product key from the products table
  294. 9:07and find out if there are any
  295. 9:08inconsistencies or not. Close the
  296. 9:10bracket and I'm just going to go and
  297. 9:12filter in the blank value in right here.
  298. 9:13So click on this click on okay and there
  299. 9:15is one such row where the product key is
  300. 9:18one no order number no date the quantity
  301. 9:20is two the unit price is five and we
  302. 9:22have nothing against it and that's the
  303. 9:24reason why 2 into 5 is 10 is the value
  304. 9:26that I was getting and that's the reason
  305. 9:28why we were getting that blank value
  306. 9:29please check for such values if they are
  307. 9:32just maybe notify of the data team or
  308. 9:35just correct the data and now you know
  309. 9:36that why those blanks appear in your
  310. 9:38PowerBI reports my last stupid mistake
  311. 9:40that I have committed a lot of times is
  312. 9:42not applying ing proper data types in
  313. 9:45Power Query which has caused my
  314. 9:46calculations to break. Let me show you
  315. 9:48what do I mean. At the moment against
  316. 9:49the month I'm seeing total sales but I
  317. 9:51also want to see sales delivered. That
  318. 9:53means okay this is the value of the
  319. 9:55sales that was sold in the month of
  320. 9:56January. How much of that sales was
  321. 9:58delivered? So I'm going to find some
  322. 10:00value right here. So for which I'm just
  323. 10:02going to go maybe build a relationship
  324. 10:03between the delivery date and the date
  325. 10:05right here which is going to be an
  326. 10:06inactive relationship many to one. Good
  327. 10:08to go. Uh that is done. And now I will
  328. 10:10use the calculate function and activate
  329. 10:12this particular relationship. So I'm
  330. 10:13going to come in right here. I'm going
  331. 10:14to make a new measure. I'm going to call
  332. 10:16this as delivered sales. And I will
  333. 10:18write a simple calculate function and
  334. 10:19I'll say hey I'm still trying to
  335. 10:21calculate my total sales. So that but
  336. 10:23the relationship that you should run is
  337. 10:25the relationship between the calendar
  338. 10:28table and the delivery date which is
  339. 10:30right here. Close the bracket and close
  340. 10:31the bracket and press enter. If I drag
  341. 10:33that off to my visual, it just gives me
  342. 10:35blank. Now I'm wondering what the heck
  343. 10:37happened. Now if I just go take a look
  344. 10:38at my delivery date which is right here,
  345. 10:40it seems fine. There is no problem at
  346. 10:42all. The only problem is that if you
  347. 10:43take a look, this one is left aligned
  348. 10:45and this one is actually a right aligned
  349. 10:48date. Now, if you just even click on the
  350. 10:49date column right here, it's going to
  351. 10:51show you that this column has been
  352. 10:52marked as a date. And if you click right
  353. 10:53here, this column has been marked as a
  354. 10:56text. And this is absolutely not right.
  355. 10:58Now, once the relationship which is
  356. 10:59between the calendar table and this
  357. 11:01particular column, the date table is
  358. 11:02coming through. It's not able to find
  359. 11:04any single value because these are texts
  360. 11:06and in the calendar table what you have
  361. 11:07is a date. So, the VLOOKUP is not
  362. 11:09working. the relationships are not
  363. 11:11working and if they're not working
  364. 11:12they're just giving you blank. That's
  365. 11:13it. So what do we do about this? We just
  366. 11:15actually go back to Power Query and take
  367. 11:16a look at the data type. You can also
  368. 11:17mark the data type right here in PowerBI
  369. 11:19but let's just go back to the source and
  370. 11:21correct the data type. So this is where
  371. 11:23I have calculated my delivery date and
  372. 11:25within the formula only I will declare
  373. 11:27my data type as a type and I will call
  374. 11:29this as date. Press enter. Now this
  375. 11:31actually becomes a date. If I now click
  376. 11:32on close and apply come back and this
  377. 11:34just takes a while to load the data.
  378. 11:35Good. This seems like a date. Date is
  379. 11:37good. I come back and here is my
  380. 11:38delivered sales. So, please check for
  381. 11:41data types. I've got another very
  382. 11:42interesting video on excellent pivot
  383. 11:44table tricks that you might not have
  384. 11:46seen before. I would love for you to
  385. 11:47watch that next. I'll see you in that
  386. 11:49one. Cheers.
  387. 12:00[Music]

About this transcript

This page contains the full transcript of The 8 Biggest Mistakes Behind 80% of Report Problems by Goodly, generated from the public captions YouTube serves with the video. The transcript has 2,889 words across 387 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.