The 8 Biggest Mistakes Behind 80% of Report Problems — Transcript
Full transcript
- 0:00In this video, I'm going to talk about
- 0:01some of the stupidest mistakes that I
- 0:03make in PowerBI over and over and over
- 0:06again. Every single time I make these
- 0:08mistakes, I want to kick myself on the
- 0:10butt. After watching the video, if you
- 0:12share the same feeling, let me save you
- 0:14some butt kicks. Let's start.
- 0:17[Music]
- 0:18Stupid mistake number one. Let's just
- 0:20say that I build this visual year and
- 0:22the quarter in the slicer and then
- 0:24months over year in the rows right here.
- 0:25And against that I drag my total sales
- 0:27and I see that every single row is the
- 0:30same value. Now you could scratch your
- 0:31head and figure out what's going wrong
- 0:33in 5 hours or you can just check the
- 0:36relationships. Whenever you see the
- 0:38numbers repeating and they are just not
- 0:40changing and they are the same value. It
- 0:42most likely that you have forgotten to
- 0:44build the relationships. Otherwise the
- 0:46numbers are going to be blank. They're
- 0:48not going to be appearing. They're going
- 0:49to be incorrect perhaps but not the
- 0:51same. This simply means the data is not
- 0:54being filtered and the numbers therefore
- 0:56are the same. So now if I just go back
- 0:58to the model view, you can see that we
- 0:59have the calendar table and we have the
- 1:01sales table. And clearly I have missed
- 1:02the relationship creation between the
- 1:04two tables. And of course I can just go
- 1:06in here and I can take a look at my
- 1:07date. Drag the date over to the date
- 1:09column right here. The relationship is
- 1:10created many to one. Good to go. That's
- 1:13good. And now if I go back and take a
- 1:14look at my numbers, they are all good.
- 1:17Stupid mistake number two. And in my
- 1:19early days, I cannot even imagine how
- 1:21many times have I fallen for this.
- 1:23Remember, measures return single value.
- 1:26But if you try to commit them as a
- 1:27table, they are obviously going to give
- 1:28you errors. Let me show you what do I
- 1:30mean. So let's just say that you're
- 1:31trying to create a measure. And in the
- 1:32measure, you just go write something. So
- 1:34I'm just going to write maybe high value
- 1:36sales, right? And now if you try to
- 1:39think about the logic, you say something
- 1:40like, "Hey, I'm just trying to filter my
- 1:41sales table and I'm just trying to go in
- 1:43the sales table and just trying to pick
- 1:45up the unit price and pick up all the
- 1:46prices of the products which are above
- 1:48$1,000." That's what you're trying to
- 1:49do. As soon as you do that, this looks
- 1:51good and it is correct. And if you press
- 1:52enter, this is going to give you an
- 1:54error. Now, it doesn't even want it to
- 1:55be dragged to the visual. It gives you
- 1:57the error right here itself because what
- 2:00you have done is you have created a
- 2:02table inside of a measure and the
- 2:05measure necessarily needs a scalar or a
- 2:08single value. It's a table and a table
- 2:10cannot be fit into a measure. So you
- 2:11need to do something with the table all
- 2:13of the rows of the table which are
- 2:14beyond $1,000 and you need to wrap them
- 2:16up aggregate them up. You can do a count
- 2:18rows you can do a sum of the table you
- 2:20can do whatever of the table but don't
- 2:22leave the table just like that. Now if I
- 2:24happen to just maybe write the count
- 2:26rows function just want to find out how
- 2:27many rows are there. This measure is
- 2:28just going to work fine. But do not
- 2:30leave your measures with table
- 2:33expressions measures are scalar values.
- 2:36Quick introduction in the video in case
- 2:37you're liking the video so far and
- 2:39you're wondering that how can I learn
- 2:40these skills of writing good DAX good
- 2:43data modeling the M language behind
- 2:44Power Query and all of the nuances of
- 2:47PowerBI that makes it work. I have
- 2:49brilliant courses on PowerBI especially
- 2:51the Power Query part, the DAX part, data
- 2:53modeling part and the M language part.
- 2:55In the courses, I take you from a
- 2:57beginner level and take you to more
- 2:58advanced concepts. Try to explain you
- 3:00the logic of how things are working so
- 3:02that you feel confident in applying the
- 3:04learnings to even on your own data sets.
- 3:06Hundreds and hundreds of students have
- 3:08joined my courses and they have
- 3:09benefited a lot. In case you're
- 3:10interested, the link for the courses is
- 3:12going to be down in the description of
- 3:14the video. Let's get back to the video.
- 3:15Here is another one that has caused me a
- 3:18lot of pain and agony while debugging my
- 3:20tax problems just to find out that I
- 3:23just did not plug the instrument. I mean
- 3:25is as simple as that. Please take a
- 3:27look. So I've got this simple
- 3:28visualization which is I have categories
- 3:30and I have sales of those categories and
- 3:32I'm trying to find the category mix.
- 3:33That means what is this particular value
- 3:35divided by the total gives me as a
- 3:36percentage which is a very standard
- 3:37calculation. And I go ahead and write a
- 3:39measure for that. My measure is
- 3:40something like this. I take my total
- 3:42sales. Okay. I divide the sales by the
- 3:45total of the category which is total
- 3:46sales by remove filters of the product
- 3:48and the category. And this is by the way
- 3:50just the right measure. It's been
- 3:51working fine. I have learned it. It's
- 3:53working fine. But at the moment when I
- 3:54drag this off to my visual it gives me
- 3:56100% 100% 100% all the way through till
- 3:59the end. Now what's going wrong? A lot
- 4:01of times you would not realize that
- 4:03while in the development process you
- 4:04could tend to drag the wrong column in
- 4:07the visual and through that the filter
- 4:09that you're removing off in the DAX
- 4:11calculation does not get removed. Let me
- 4:13show you what do I mean. If you actually
- 4:15take a look at the visual and in the
- 4:16visual if you click on the thing right
- 4:17here you're going to see that the
- 4:19category that I have is not coming from
- 4:21the products table like it should as my
- 4:23measure also suggests. So if I just
- 4:25maybe take a look right here, this is
- 4:26actually coming off from my sales table
- 4:28as a category. For some reason, the
- 4:30category is also mentioned in the sales
- 4:32table and that is where it's being
- 4:33dragged from. Now this could be a
- 4:35genuine mistake. I did not intend to do
- 4:36it. I just saw the category and I
- 4:38dragged it. Now my calculation is not
- 4:39working. So before you start going down
- 4:42in the rabbit hole to fix your DAX,
- 4:43please just check where the columns are
- 4:46coming from. Are they coming from the
- 4:47right tables or not? Now if I happen to
- 4:49replace that and if I say, hey, this is
- 4:51not the correct category column that I
- 4:52would want. I would want it from the
- 4:53products table. So if I just made a
- 4:55click right here, as soon as I do that,
- 4:56you can see that it just get fixed. It's
- 4:57all good now. That's it. Here is another
- 4:59one of the innocuous mistake, but it is
- 5:02deadly. Take a look at this particular
- 5:04visual that I've made. I've got the
- 5:05colors and I've got the total sales.
- 5:06Just a while ago on the previous page, I
- 5:08saw that my sales of the all categories
- 5:11was about 11 million. And if I'm just
- 5:13trying to find out sales against the
- 5:14colors, of course, the colors are going
- 5:16to be different, but the total sales
- 5:17should match 11 million. But it's not.
- 5:19So I wonder what's going on. A lot of
- 5:20times you're going to find that you have
- 5:22unnecessary filters applied that are
- 5:24hidden behind the bookmarks or somewhere
- 5:26on the page which are not visible to
- 5:28you. So if I maybe take a look at this
- 5:29particular thing, there is a icon up on
- 5:32the top which is the filters icon. It
- 5:33literally was the filters icon. It
- 5:35changed it now to this. It doesn't look
- 5:37like a filter icon, but please hover
- 5:39your mouse on top of this icon. And you
- 5:41would be surprised what filters are
- 5:42applied to your visual through which the
- 5:44numbers are seeming incorrect. So if I
- 5:45just maybe hover my mouse on top of
- 5:47that, you're going to see that a few
- 5:48subcategories are excluded. I don't know
- 5:51where the filter is. It's all blank
- 5:53right here in the page, but I can see
- 5:54that few subcategories are excluded. A
- 5:56couple of things that you can do is you
- 5:57can build a new visual on a new page
- 5:59just to negate any kind of filters that
- 6:02might be there. You can look for hidden
- 6:03filters. Somebody must have created a
- 6:05bookmark or hidden the filter. Or you
- 6:07can also take a look at in the filter
- 6:09pane. So I have turned that off. Let me
- 6:10just turn that on. So I can go over to
- 6:12the view. In the view, I can just maybe
- 6:13turn on the filters pane. And if I just
- 6:15take a look inside of the filters pane,
- 6:16you can see that the subcategory are
- 6:18applied right here, which I do not
- 6:19really want. If I just cancel them out,
- 6:21the sales is back to the value that I
- 6:22needed. So look for the hidden filters.
- 6:24And the best way to look for those
- 6:26filters are this particular hover where
- 6:28you can take a look at are any filters
- 6:29applied to this visual which may be
- 6:31wanted sometimes are not wanted as well.
- 6:33Another very common mistake that I have
- 6:35found myself making is that when you are
- 6:37creating some calculation and the
- 6:38calculation is not giving you the
- 6:39expected answer, please by all means
- 6:42apply those filters physically on the
- 6:44data inside of PowerBI and please take a
- 6:47look that the data exists or not. If the
- 6:50data does not exist, it clearly gives
- 6:52you an indication that the data was lost
- 6:54in the ETL layer. The next logical step
- 6:56is that you go back to the data perhaps
- 6:58in the CSV or the Excel file and apply a
- 7:00filter over there. Is the data existing
- 7:02over there or not? If the data exists in
- 7:04the Excel file and does not exist in the
- 7:06PowerBI, there must be something that
- 7:09you might have done in Power Query so
- 7:11that the data was filtered out. And
- 7:13please look for such errors where you
- 7:15just check the validation or the
- 7:17existence of the data itself. And you'll
- 7:20be surprised how many times you will
- 7:22save yourself from nasty debugging.
- 7:24Another very common problem that a lot
- 7:26of people get stuck with and the PowerBI
- 7:28community is filled with questions as
- 7:31such which is incorrect totals of the
- 7:33matrix or the table visualization. The
- 7:35thing is that every single value
- 7:42in the table or the matrix is evaluated
- 7:44as per the context and the total
- 7:45although it looks like a total but it's
- 7:47not the sum of the rows. So whenever you
- 7:50are in the developer mode and trying to
- 7:51create calculations, it's imperative
- 7:53that you please take a look at every
- 7:55single value including the totals which
- 7:57is typically what your bosses are going
- 7:59to see first. Take a look at the totals
- 8:00and match it to the totals row or not.
- 8:02Now I've done videos in the past where I
- 8:04have spoken about this particular
- 8:06problem, why does it go wrong and also
- 8:08giving you some fixes around it. Please
- 8:10go take a look at that video in case you
- 8:12have not been able to do work around
- 8:14with totals in the past and you've
- 8:15struggled with that. The next most
- 8:16common mistake are the stupid blank
- 8:18values that you get in PowerBI. Now it
- 8:21might not be your problem as a developer
- 8:23of the report but it could be just the
- 8:26data inconsistencies and this is called
- 8:28referential integrity where something is
- 8:30there in the sales fact table or just
- 8:32the fact table and is not there in the
- 8:34products table. Now take a look right
- 8:35here I've got the simple visual and
- 8:37somehow there's a blank right here
- 8:38against which the 10 is appearing and I
- 8:40do not understand that where does this
- 8:4210 come from? What the heck is this
- 8:43blank? it is very likely that your sales
- 8:46table contains a few values which are
- 8:49missing from your products table. So
- 8:51what I can do is I can just go over one
- 8:53of the easiest ways to check it. I can
- 8:54just go over to the sales table. In the
- 8:56sales table I can just right click and I
- 8:58can just make a temporary column for the
- 9:00just a quick second and I'm just going
- 9:01to go inside of this column. I'm going
- 9:03to write a VLOOKUP and I'm going to get
- 9:05the product key from the products table
- 9:07and find out if there are any
- 9:08inconsistencies or not. Close the
- 9:10bracket and I'm just going to go and
- 9:12filter in the blank value in right here.
- 9:13So click on this click on okay and there
- 9:15is one such row where the product key is
- 9:18one no order number no date the quantity
- 9:20is two the unit price is five and we
- 9:22have nothing against it and that's the
- 9:24reason why 2 into 5 is 10 is the value
- 9:26that I was getting and that's the reason
- 9:28why we were getting that blank value
- 9:29please check for such values if they are
- 9:32just maybe notify of the data team or
- 9:35just correct the data and now you know
- 9:36that why those blanks appear in your
- 9:38PowerBI reports my last stupid mistake
- 9:40that I have committed a lot of times is
- 9:42not applying ing proper data types in
- 9:45Power Query which has caused my
- 9:46calculations to break. Let me show you
- 9:48what do I mean. At the moment against
- 9:49the month I'm seeing total sales but I
- 9:51also want to see sales delivered. That
- 9:53means okay this is the value of the
- 9:55sales that was sold in the month of
- 9:56January. How much of that sales was
- 9:58delivered? So I'm going to find some
- 10:00value right here. So for which I'm just
- 10:02going to go maybe build a relationship
- 10:03between the delivery date and the date
- 10:05right here which is going to be an
- 10:06inactive relationship many to one. Good
- 10:08to go. Uh that is done. And now I will
- 10:10use the calculate function and activate
- 10:12this particular relationship. So I'm
- 10:13going to come in right here. I'm going
- 10:14to make a new measure. I'm going to call
- 10:16this as delivered sales. And I will
- 10:18write a simple calculate function and
- 10:19I'll say hey I'm still trying to
- 10:21calculate my total sales. So that but
- 10:23the relationship that you should run is
- 10:25the relationship between the calendar
- 10:28table and the delivery date which is
- 10:30right here. Close the bracket and close
- 10:31the bracket and press enter. If I drag
- 10:33that off to my visual, it just gives me
- 10:35blank. Now I'm wondering what the heck
- 10:37happened. Now if I just go take a look
- 10:38at my delivery date which is right here,
- 10:40it seems fine. There is no problem at
- 10:42all. The only problem is that if you
- 10:43take a look, this one is left aligned
- 10:45and this one is actually a right aligned
- 10:48date. Now, if you just even click on the
- 10:49date column right here, it's going to
- 10:51show you that this column has been
- 10:52marked as a date. And if you click right
- 10:53here, this column has been marked as a
- 10:56text. And this is absolutely not right.
- 10:58Now, once the relationship which is
- 10:59between the calendar table and this
- 11:01particular column, the date table is
- 11:02coming through. It's not able to find
- 11:04any single value because these are texts
- 11:06and in the calendar table what you have
- 11:07is a date. So, the VLOOKUP is not
- 11:09working. the relationships are not
- 11:11working and if they're not working
- 11:12they're just giving you blank. That's
- 11:13it. So what do we do about this? We just
- 11:15actually go back to Power Query and take
- 11:16a look at the data type. You can also
- 11:17mark the data type right here in PowerBI
- 11:19but let's just go back to the source and
- 11:21correct the data type. So this is where
- 11:23I have calculated my delivery date and
- 11:25within the formula only I will declare
- 11:27my data type as a type and I will call
- 11:29this as date. Press enter. Now this
- 11:31actually becomes a date. If I now click
- 11:32on close and apply come back and this
- 11:34just takes a while to load the data.
- 11:35Good. This seems like a date. Date is
- 11:37good. I come back and here is my
- 11:38delivered sales. So, please check for
- 11:41data types. I've got another very
- 11:42interesting video on excellent pivot
- 11:44table tricks that you might not have
- 11:46seen before. I would love for you to
- 11:47watch that next. I'll see you in that
- 11:49one. Cheers.
- 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.