Power BI Gets Easier Once You Know These

Before the year 2025 ends, I'd like to talk about 12 of my absolute favorite PowerBI tricks. I'd also be answering a

PowerBI tricks. I'd also be answering a few questions that you guys sent me because we recently completed 12 years of running this tiny business. And I'd

also be awarding a prize to the queen winner from the last video's competition for finding the secret word. No further

ado, all that fun ahead. Let's start.

The first bunch of tricks are power query tricks and of which the first one is how do you sort without actually sorting it in PowerBI. Let me talk about it. It's quite dangerous and fun as

it. It's quite dangerous and fun as well. I'll talk about all the possible

well. I'll talk about all the possible nuances and let you decide how do you find this trick. But nevertheless, take a look at my screen. So I've got this simple visual which is where we have the year, the month and the total sales. And

if you obviously take a look at the month here, the month is not sorted in the correct order. The month order, this is actually sorted in alphabetical order. Now how do you fix it? You

order. Now how do you fix it? You

obviously create an index column in your uh calendar table and then sort the month name by the month index. That's

the standard practice that you have seen in countless videos so far. But how do you not have the month index and still be able to sort it? Let me talk about that. All right, I'm in Power Query

that. All right, I'm in Power Query because my calendar table is created in Power Query. But the technique that I am

Power Query. But the technique that I am talking about here could also be done in DAX in case you are fond of creating calendar or date tables in PowerBI with DAX. Anyways, please take a look. So we

DAX. Anyways, please take a look. So we

have the month here and we have made a few columns date column, month column and the year column. And once this particular data is loaded in PowerBI, this doesn't sort itself. Therefore we

need to create an index column. But can

we do that without that? Yes. Now the

trick is that we add a zero width space character at the start of the month of January. Let me help you visualize. So a

January. Let me help you visualize. So a

zero bit space character doesn't have any width. It's not kind of visible to

any width. It's not kind of visible to the eye. What this simply means is that

the eye. What this simply means is that if before the month of January I add let's say 11 zero width space characters then it's be something like hey all of these 11 characters are there you can't see them they're not actually equating

to a space and then you have the word January and here are 11 characters and then in the month of February there are 10 characters and then you have Feb and so on and so forth because you have these zero width space characters at the

start of the month the data of these months is automatically going to be sorted. But how do you do that? Let me

sorted. But how do you do that? Let me

show that to you. So, I'm actually going to go over to this inserted month column. I'm going to open up uh this

column. I'm going to open up uh this particular gear icon. And that's where we have the date domon name for now because I need the month number to find how many characters to add. So, what I can do is I can do something like hey,

I'm actually going to find the month number instead. So, date do month

number instead. So, date do month actually gives me the month number which is all the way from 1 2 3 4. Now, the

trick is that if I add one here, just like one single character, the problem is that this is going to come in the end. But if I want to add 12 here, then

end. But if I want to add 12 here, then it'll come at the start. So now I want to reverse the one to the largest number and then number two to the second largest number so on and so forth. But

what I can do is I can just go in and I can say hey the largest number of months are 12 and 12 - 1 is going to be 11 so on and so forth. So at the start of the month of January we are having 11

characters and then 10 characters 9 characters so on and so forth until we reach zero characters. That's fine. Now

what we're going to do is we're going to have that zero width space character.

And the way to trigger that is by using the code 8203. I'll tell you how to do that. So I'm going to use the function

that. So I'm going to use the function character dot from number and the number is 8203 which is let's say the code for the non-widp space character and this is what I want to invoke how many number of

times let's say 11 number of times. So I

can use the function text dotre repeat can start the bracket here is the text that I want to repeat. Here are the number of times that I want to repeat and I'm just going to close the bracket in the end. I'm going to click on okay.

As soon as I click on the okay you can see that nothing appears. There's

nothing here. Nothing at the bottom as well. Now there are 11 characters that

well. Now there are 11 characters that we can't see of which is fine but then I have to add the month month name along with that. So I can just go back to this

with that. So I can just go back to this and I can say hey whatever is this I want to concatenate the month name along with that. So date dot month name and I

with that. So date dot month name and I can just maybe fetch the month name from the date itself. Close the bracket. So

the zero width characters concatenated with the name of the month. Click on

okay. And now we have it. Now take a look at the magic. Once I can load this data back into PowerBI you're automatically going to see that the months are automatically sorted in the right order. January automatically comes

right order. January automatically comes first, February, March. And if you do take a look at my calendar table, in my calendar table, I do not have any kind of index right here to sort the months.

This has automatically been sorted from Power Query. Now, this might seem like

Power Query. Now, this might seem like an extremely cool approach in order to reduce that extra sorting that you typically do in PowerBI, but there's a little caveat to that. A little caveat is that tomorrow, if you decide to add,

let's say, a calculate function that you're trying to reference any particular month hardcoded in the calculate function, it's not going to work. Let me demonstrate to you by

work. Let me demonstrate to you by creating a column. I'm going to right click and I'm going to make a new column. And in the new column, I'm going

column. And in the new column, I'm going to say, hey, the month is equals to the month of January. And as soon as I commit on this, obviously this seems like the month of January, which is being equated to the month of January,

but this is still going to give you a false because hey, we don't really have prefixed the empty characters that we did it in Power Query. Therefore, none

of your calculate functions are going to work. You're only going to use this with

work. You're only going to use this with extreme caution and care if you truly believe that none of these functions are going to be called as an explicit filter in any of the conditions. Be it a

column, be a calculate function or whatever that might be. Now, there are ways to get around that, but nevertheless, this was the trick. Please

use it with caution in case you like it.

And let's just move on to my next trick, which is again pretty awesome. All

right, fellas. In a different setting here, obviously, first of all, thank you so much for posing your questions in the ask me anything. I have a couple of very interesting questions right here. I'm

going to take them one by one. The first

question is from Fezil and he asks, "Do we still need in-depth data analysis education after the rise of AI?" That's

a good question. Well, I do believe that the AI systems are getting smarter and they can provide us the answers that we're looking for and even accurately than what they were a couple of years before. But I think the need for

before. But I think the need for understanding what to get out of AI is still something that we would need. The

other thing that I truly believe is important is that we can't just stop learning just because an AI can give us an answer. The only way human beings are

an answer. The only way human beings are able to develop the mindset is by doing ground work. So for example, reading is

ground work. So for example, reading is the most fundamental skills of all. So

if you stop reading, if you stop learning, then you just can't expect to ask very sharp questions from the AI. So

learning is important, but then the time that you take to get to a solution is getting shorter and shorter. But that

does not make learning redundant. That's

what I believe. My next trick in power query is super helpful in case you have witnessed change type errors. Let me

help you understand. So let's just say that here is my products table in power query and I've got these four steps and at the end there is a change type step.

Now you know that in case if I go to the promoted header step and happen to delete the color from here delete it and click on insert a step gets added in between which is where we are removing the color column and in the next step we

are obviously applying a data type to the color column and the color column was not found. Therefore, the query tanks, which is actually a good thing.

It lets you know that you were trying to apply a data type to a column which was not found. So, you can actually work

not found. So, you can actually work with that. The errors are a good thing.

with that. The errors are a good thing.

But for some reason, if you want your query to follow through, even though the column was not found and still kind of pass the query and load it into the model, what you can do now is in this particular function table.transform

transform column types. You can add a missing field enumeration which only happens in the latest version of PowerBI. I think a month or two back,

PowerBI. I think a month or two back, but it only happens in PowerBI, not in Excel Power Query yet. I'm going to show how that works. So, I'll open up the formula bar. And in the formula bar,

formula bar. And in the formula bar, I'll go where the formula ends. And

right where the formula closes, I'll put in a comma and I'll start to write the culture, which is where I'll start to write that missing field enumeration. In

the square bracket, I will start missing field all one word. And I'm just going to use the enumeration missing field dot ignore. And that's pretty much it. Once

ignore. And that's pretty much it. Once

we have put that as a record, I click on okay. And the query works just fine. Now

okay. And the query works just fine. Now

tomorrow if any other column went missing, perhaps let's say the model went missing. I delete that. Click on

went missing. I delete that. Click on

okay. And you have now two columns removed color and the model and the model is being applied a data type in the next step. You're obviously going to not have any kind of errors and the query is going to follow through. In

case you want this particular type of behavior, then you can use it.

Otherwise, just be cautious about, hey, the query is obviously not catching errors because you declared an enumeration for missing field.ign. Let's

move on to my next trick. I work in Power Query a lot and I use a lot of shortcuts and here are the three of my favorite shortcuts all bundled into one trick. Take a look. So let's just say

trick. Take a look. So let's just say that you have a large table and the table is quite fat and you have a lot of columns and perhaps you're not able to figure out that hey where is this column or that column whatever that column is and then instead of actually scrolling

left and right which is not very convenient. What you can do is you can

convenient. What you can do is you can use the shortcut CtrlG and it opens up this go to column and you can actually pick up the column perhaps the model or the subcategory. Click on okay and it'll

the subcategory. Click on okay and it'll actually take you to that particular column. Isn't that awesome? My next

column. Isn't that awesome? My next

trick is again kind of navigating left and right in a fat table. So perhaps you want to take a look at all the values of row number four. So what you do is through your eyes you navigate. Hey,

here is 217 and here is the helmet black and all of all of that. You take a look at this and you use the mouse and things like that. Nevertheless, you don't see

like that. Nevertheless, you don't see get to see all the values of the column headers in once. If you are to the far right of the screen, you have actually missed the information to the left of the screen. So it's kind of

the screen. So it's kind of inconvenient. What you can do is you can

inconvenient. What you can do is you can actually click on the row header which is four. This is going to convert the

is four. This is going to convert the entire row into a record and you can actually preview the data at the bottom and this is super super convenient. My

next trick is understanding any kind of M function. So let's just say that you

M function. So let's just say that you obviously write M functions and you would like to understand any particular function and the documentation behind it or take a look at a few examples behind it. The fastest way to do that instead

it. The fastest way to do that instead of going to the documentation for on the web from from Microsoft what you can do is you can create a new step anywhere in between that's also fine and write the function of your choice. So perhaps I'm just trying to understand the

list.transform function. So I'm just

list.transform function. So I'm just going to write list.transform. Now

without actually committing on the function, without adding any brackets at the end of the function, if I commit on this particular function, you're going to see that this is going to pull up the entire documentation of the function. So

you can take a look at it. Hey, what are the parameters few examples as well and all of the documentation can be just seen right here. This happens for any function. Just make sure that you make a

function. Just make sure that you make a new step and you write that and do not include any kind of brackets in the end.

All right, the next question is from Ben Lynford. It's more like a life question

Lynford. It's more like a life question from the UK. And he asks me about my upbringing. How did I get started with

upbringing. How did I get started with Excel, Power Query, and PowerBI? Any

particular change that I would like to see in the world? And he asks me about that how did I develop this jargon swatting teaching style? And how did I come up with this first principles thinking? Now, the one thing that I

thinking? Now, the one thing that I truly believe that a lot of people should acquire is the skill to read and comprehend. I'll tell you a short story.

comprehend. I'll tell you a short story.

I was almost failing in the chemistry exam in my ninth grade or eighth grade.

I believe and because I did not understand chemistry and my father had put me up to tuions and I did not even go to the tuions and the exam was coming near I believe it was the finals and I had I had to get a 60 or 100 even to

pass the exam. Typically the passing rates are 40 but I had to get a 60 because I had failed the previous exams. So what did I do? I just simply picked up the book and I started reading. To my

surprise I understood every single line what was there. So before that I somehow used to believe that if I would read I would not be able to understand and somebody would have to sit with me to help me understand what that text is.

But if you just drive through just basic pieces of information, put it together, your mind starts to build these patterns. So reading is one thing that I

patterns. So reading is one thing that I would highly recommend that everybody should pick up, it just opens up your mind and you start understanding things that you did not understand before. And

by the way, I did score like a 70 or something on that exam and I passed. I

have also done a lot of content work beyond goodly that a lot of people don't see like written tiny books and blogs and whatnot for many many years that might have helped me to put my thoughts

as clearly as possible. I'm just not the way that I am at the moment. It's just

some years of work that I've actually gotten me to here and of course I would also not discount the luck factor that I've had. So I've had the opportunity to

I've had. So I've had the opportunity to work with some of the brilliant people in my career. My friends, my colleagues, my bosses, they were like super intelligent and I had the opportunity to work with them. So that also helped a

lot. My next trick is pretty nifty and

lot. My next trick is pretty nifty and you are going to find yourself often in scenarios where you want to extract an intermediary step of the query. Not the

final step but an intermediary step. So

you obviously know that you can go ahead and you can rightclick the products table and make a reference of it. So

right click and you make a reference of the query. Now when you create the

the query. Now when you create the reference of the query, what you see on the screen is nothing but the last step of the query. You're not able to pull any of the intermediary steps. How do

you do that? But I'm going to teach you a technique and it is using the meta keyword. So what you do is you go to the

keyword. So what you do is you go to the last step right here. And in the last step you just define the step that you want to pull out. So I'm going to create a record and in here I will start

writing the meta keyword. So me t a meta and then within which I will create a record and the record is going to link to the step that I want. It looks

something like this. So I will say hey within the meta keyword I am having the column name as step and this is within a record and the step that I want to reference is nothing but the promoted header step which is right here and that

is right here. That's all about it. Now

this is not going to change your query.

If you just kind of commit on the formula nothing happens. Your query just works absolutely fine and it's just a regular query. Now the only thing that

regular query. Now the only thing that has happened is that the last step apart from resulting into this particular query right here also kind of remembers the intermediary step that you have declared in the record and you have a

way to pull it out in the reference of the new query. So let's just go do that.

I'm going to right click and I'm going to say hey I want to make a new query and I'm going to make a blank query and the blank query is going to be nothing but the products query. So products and I'm just going to press enter and this is good to go. Now what I can do is I

don't really want to have the last step.

I want to have one of the intermediary steps. So I can actually invoke the meta

steps. So I can actually invoke the meta keyword by the function value dot meta data and I can just do that and I can close the bracket and I can press enter and this actually gives me the step which is nothing but the step column

that I declared and that is nothing but the promoted header step. I can click on it and this is kind of good to go and you have now successfully extracted one of the intermediary steps. In case

you're wondering that hey right now I hardcoded the step to promoted headers that means this was hardcoded here. What

if I want to have the choice of extracting any step that I want within the query? How do I then do that? There

the query? How do I then do that? There

is also a method for that and I've discussed that in another video. I

suggest that you watch that. But for

now, let's move on. All right. Animesh

asks me a question about my content creation career and what did you do to begin the career? What advice would you give to start on the journey for YouTube? Uh team, equipment, and money.

YouTube? Uh team, equipment, and money.

So, I think I have been creating videos for the longest time. I think if you just go to the history of the YouTube channel, you're going to see that I have made videos from I don't know all the way back in 2014, I guess. So, but the

videos were just a part of the blog. I

did not intend to monetize them. And for

the longest time that I could remember, my monetization was also off. It's only

in 2020 that when co struck, I thought, hey, I've got nothing else to do. I'm

free the entire day. Why don't I just start creating these YouTube videos for the assignments that I'm doing for companies and teaching companies, Power RBI and stuff like that. And I had solved some very nasty problems back in the day in 20 2020 or so. And one thing

led to the other. I started creating like two videos in a week. People

started coming onto the channel.

Surprisingly, the problems that I was talking about were the same problems that a lot of people were facing like people in the west, people in Europe, all around the world. And that attracted the start of the YouTube career. So I

believe that if you are trying to start on YouTube, consistency is the key. Um,

start giving value. If you genuinely have a good idea, the only thing matters technically is the sound quality. So if

your sound is muffled, I would not recommend that you have that. At least

get a good mic and a standard screen recording is going to be good and I think you're good to go. But other than the sound, which is one of the technical requirements, I believe that one of the strategy that is almost going to help

any content creator out there is that for how long can you do it? If you are the last man standing, I learned it from one of the Indian content creators and it's actually true. There's so much noise. There's so much content out there

noise. There's so much content out there people are getting into this is that your strategy is simple. Can you do it for a very very very long time and can you outlast everyone and along the in

the journey can you also continuously improve or not and if you have a way to sustain for that amount of time that long I don't know what that time is like sometimes that a year 2 years 5 years 10 years depending upon how competitive is

your niche of content creation but checking for a very long time and improving bit by bit is what I would recommend my next set of tricks are DAX and data modeling tricks and the first one that I have for you how do you stop

the spilling effect that you typically get when you do YDD kind of calculations over the entire calendar table. Take a

look. So I've got this simple matrix that we saw a bit earlier which is where we have the year, the month, the total sales and we have also calculated YD.

Now because our calendar table contains all the dates of the year up until the month end even though the year has not finished. So we have data until June.

finished. So we have data until June.

There are six more months spending here.

The vd although not incorrect kind of spills through these empty cells where the sales did not happen. Now you can obviously write if the sales is not present then you don't calculate this value otherwise you do calculate the value. That's one way of going about it

value. That's one way of going about it and that's not wrong. But what you can also do is you can actually create a data modeling trick in your calendar table. Let me show that to you how that

table. Let me show that to you how that works. So I'm going to go over to my

works. So I'm going to go over to my calendar table and in the calendar table I'm just going to build a simple column nerd check. And that column nerd check I

nerd check. And that column nerd check I can use it anywhere for any search spilling calculations. So I'm going to

spilling calculations. So I'm going to right click and I'm just going to say hey I want to make a new column and the new column is going to be is sales present. And I'm just going to say, hey,

present. And I'm just going to say, hey, what's the maximum date in the sales table date column? Close the bracket and press enter. And that's the maximum

press enter. And that's the maximum date. Now, I want to necessarily take

date. Now, I want to necessarily take the maximum date to the end of the month. This is 15th of June. So, I'm

month. This is 15th of June. So, I'm

just going to maybe wrap that around in the EO month function. Take it to the month ending date. And that's the June ending date. Nice. Now, I'm going to

ending date. Nice. Now, I'm going to say, hey, is 1st of January smaller than 30th of June 2020? The answer is yes.

So, the sales was present. So I can just simply say the calendar date which is this particular date is that less than equal to the largest date of the sales table forced to the end of the month.

Press enter and you get a bunch of trs and falses and these tr and falses can now be used in any calculation to limit the calculation only till the time the sales is present. That's it. I'm just

going to go here and I'm just going to say hey where is my sales byd calculation? It's right here. And in

calculation? It's right here. And in

this calculation I'm just going to do a wrapper. So I'm just going to say hey I

wrapper. So I'm just going to say hey I just want to do the total YTD calculation but I only want to do it up until is sales present table is sales present and that's all about it. And

this is anyways a true and false. This

is going to work just all right. You can

also kind of hardcode it as equal to true but that's all right. I'm going to close the bracket and press enter. And

the sales actually stops right here. And

this check can now be used in multiple scenarios where you want to limit the visual only up till where the sales tables actually end. Taking our previous trick a bit further. Sometimes you would

want to do a trickery with your visual and you would want to stop the visual until not the calculation but the visual until a certain date. Let me help you understand. So let's just say that I

understand. So let's just say that I have got the same visual that we were working with. It's like a table. We have

working with. It's like a table. We have

year month and the total sales column and we have nothing selected in the slicer. So let's just say that I go

slicer. So let's just say that I go ahead and pick up 2019 the month of March. As soon as I select the month of

March. As soon as I select the month of March, the default behavior of the pivot table is going to be that it's only going to show one single value for 2019 for the month of March. But however, I want this visual to truncate right here

and not show the data beyond that. How

do you do that? At the moment, let's just take a look at what happens. So, if

I click on the month of March, sure enough, I only get the month of March.

But that's not what I want. I want that the visual should stop showing the values beyond the month of March and it should just stop right here. How do you make that display until kind of thing happen? So, for that, we need a

happen? So, for that, we need a disconnected table. Let's just go build

disconnected table. Let's just go build one. I've actually already built one.

one. I've actually already built one.

I'm going to show that to you. So if you actually take a look at this calendar until table that I have built, the calendar until table is nothing but just a replica of the calendar table. You can

make it in Power Query, you can create it in DAX, doesn't matter. But we need a disconnected calendar table. Something

like this. Once we have this table, what we're going to do is we're going to build the slicer off of this particular table because imagine if the year and the month slicer is coming from this particular table. Whatever you pick up

particular table. Whatever you pick up in the slicer because it is not connected to the sales table. None of

the values that you pick up in the slicer right here will be able to filter your sales measure and hence it is not going to be filtered. How do you restrict it is something that we will do it in a bit. So first let's just build the slicer. So I'm just going to go

the slicer. So I'm just going to go right here year and the month rather than actually picking it up from the calendar table. Year and the month

calendar table. Year and the month actually going to pick it up from this particular table. So I'll delete both of

particular table. So I'll delete both of these. Year goes right here. Month goes

these. Year goes right here. Month goes

right here. And that's my thing. Now at

the moment if I happen to pick up the month of let's say March, nothing happens. It stays just as the way it is.

happens. It stays just as the way it is.

Now we have to write a calculation exclusively that talks to this particular slicer and limits the calculation up until the month of March.

Let's just go ahead and write that.

Let's start by creating a variable called sales until which is right here.

And in that particular measure, what I'm going to do is first of all capture whatever date is selected right here. So

I'm just going to say hey I'm declaring a variable which is let's say my selected date and that is going to be whatever is the max date in the cal until table and that is nothing but the date column right here. I'm just going

to click that and then that's good to go. Now let's just return and check that

go. Now let's just return and check that whatever date that we have picked up right here is that actually being captured right here and also returned in the visual right here or not. So I'm

just going to maybe do the return statement and after that I will just return the selected date and you can see that our visual is working quite right.

Whatever date has been chosen right here 2019 in the month of March the maximum of that date is being picked up right here. Now comes the limiting part. I

here. Now comes the limiting part. I

would want to take this particular selected date filter and limit my visualization only up till there. So

let's just go ahead and write some more DAX. I'm going to go ahead and say

DAX. I'm going to go ahead and say something like hey I want to do the calculation wherein I want to do the calculate of total sales but I want to limit it. So I'll say hey first of all I

limit it. So I'll say hey first of all I want to remove any kind of filters from the calendar table but then I want to take my calendar date and I want to make sure that the calendar date is at least less than or equal to the selected date

right here. That's pretty much it. Save

right here. That's pretty much it. Save

the measure and let's just see what answer does it get. Now it shows me some answer which is 49,46.

I have no way of identifying that is this the right calculation or not. So

let's just go tweak the visual to at least figure out that are we on the right path or not. So, I'm going to go ahead and perhaps only say that the sales is only going to be until the month of July for 2017. So, I'm just going to change this and I'm going to

say, hey, go ahead and pick up only the month of July. And do I get 1423? The

answer is right. If I just maybe pick up the month of August, I technically should get this this value and this value. But at the moment, it's actually

value. But at the moment, it's actually giving me the sum of the two values, which is 1400 and 2060 is about 3,482, which is right, by the way. It's just

that it's accumulating those values. I

don't really want them to be accumulated for which I'm going to make a very simple change to my particular filter function. I'm going to say that hey do

function. I'm going to say that hey do not hard remove the filter also let these values come in as a filter and that's just going to be it for which I can use the keep filters function. Uh I

can start the bracket and close the bracket all the way towards the end. And

this is kind of good to go and let's just make sure that all the brackets are closed. And that's pretty much it. Let's

closed. And that's pretty much it. Let's

just save this measure and let's just see if it actually gives us the right result or not. Once you do that you actually see that hey the visual is kind of limiting until the month of August.

Let's just say that I pick up 2018. I

pick up the month of March and it just limits up until here. For the reason that we have total sales present in our visual, we'll have to cancel that out.

So I will just have to go right here and I'll say I don't really want to take a look at total sales. I just want to take a look at this particular thing. And if

I now play around with this, I can take a look at up until the month of April or the month of May or the month of June.

This is so beautiful and quite helpful in scenarios where you just want to limit the visual up until the date selected. Okay. Okay, so Jamil asks, "At

selected. Okay. Okay, so Jamil asks, "At what moment did you realize that Goodly could become more than just a project that it could actually grow into a long-term platform?" That's a really

long-term platform?" That's a really good question. I think before COVID, my

good question. I think before COVID, my business model was that I had to travel across the country primarily in India to do training and consulting work for mid to very large companies in India. I had

a stable business. I had I had to travel like 100 200 days in a year. So it was fine. It was going good. It's just that

fine. It was going good. It's just that I had a lot of travel. But it was what it was. In between 2014 through till

it was. In between 2014 through till 2020, there was once when I decided that hey let me just try to make money online and I had some portion of my entire revenue coming from the online business

but that was very very small like literally very small and back in 2016 I gave one full year and I stopped taking a lot of training sessions and a lot of consulting assignments just because I

wanted to focus on my blog and grow my blog to a certain traffic level which I terribly failed and I failed that attempt and I could literally see a drop in my annual revenues in that particular

year. But then I think it was more of a

year. But then I think it was more of a force of nature than by a choice that in 2020 when I had nothing else to do and co shut us down and my entire business was wiped off in a night. I had nothing

else to do. So I just started to create content one after the other and guess I would just give it out to luck that in my second attempt of building out a YouTube channel rather than a blog worked out nice and people started to

subscribe. They started taking my

subscribe. They started taking my courses. We did a bunch of online live

courses. We did a bunch of online live sessions as well. So that worked out really nice. So the moment that I

really nice. So the moment that I realized was I think late 2020 that if I poured more heart and more fuel into building up videos as a form of content I think I can do better because I think

I am slightly better in talking than writing blogs which are SEOfriendly. My

next trick has to do with DAX and especially context transition which is treated quite a dreaded and misunderstood topic. Now you can do some

misunderstood topic. Now you can do some very sophisticated stuff with very simple context transitions examples by just maneuvering through the granularities of different tables that we have. Let me show you what do I mean

we have. Let me show you what do I mean by that. So if you take a look at the

by that. So if you take a look at the model in the model at the moment we have a two dimension tables. We have the calendar table where one single row is one unique date and the products table where one single row is one single

unique product or skew. Now if you write any particular calculation in the grain of this table that means that calculation is going to be calculated daily. And if you write any calculation

daily. And if you write any calculation here that calculation is going to be done by the product. Now this provides us a huge advantage of doing some sophisticated calculations like mins or

averages and things like that. Let me

help you understand. So for example, I'm right now sitting against the year and the month of July and I want to find what was the sales of the best selling day in the month of July. Now obviously

we have the calendar table and if I happen to write a calculation something like this which is where I can say hey I want to find the max x from the calendar table not from the sales table but from the calendar table and I want to take a

look at my total sales calculation. What

this calculation is simply doing is that all that you're doing is that going in every single row of the calendar table which is one row is one unique date for every date you're doing a total sales calculation and because you have maxx

you're trying to find the max value and if you commit on this measure and bring it to your pivot table what you'll get by default is the max selling day sales for the month of July, August, September October November December

and all of that up until the end. This

is the max selling day of the entire year and that's what you get. This is

absolutely beautiful. Now, if you were to go ahead and change that max sales calculation and you were to instead say that, hey, I don't really want to step inside every single row of the calendar table, but I want to step inside every

single row of the products table. Then

what this calculation is simply going to do is this calculation is simply going to go inside every single row of the products for all the products that were sold in the month of July. they have

found out that which product sold the max and the sales for that is 181 and which product sold the max and that's the yearly sales for that particular product. So you can actually go ahead

product. So you can actually go ahead and maneuver through granularities of different tables and this is nothing but context transition happening and you can actually go find the answer. At the

moment we have found the max but you can actually do mins max averages any kind of sophisticated calculation as well and that is going to be super super helpful.

My next trick is fixing the totals in PowerBI. This is a big problem. A lot of

PowerBI. This is a big problem. A lot of people have talked about it on the community forum. Greg Deckler talks

community forum. Greg Deckler talks about it every single day. I'll tell you what's wrong with it and how do you quickly fix it. Although I have done an exclusive video on it in case you'd like to go in depth and understand why does it happen and what's the deep fix of it,

but I'll quickly summarize it and talk about it. So let's just say that you're

about it. So let's just say that you're trying to calculate sales for the first 7 days of every single month because you run some kind of promotions in those states. So first seven days of January,

states. So first seven days of January, first seven days of February and you want to call that sales out separately and take a look at your entire monthly sales. So on one side we have the sales

sales. So on one side we have the sales of the entire month which is fine and then we have the top sales as well which is right here. This is nothing but the sales of the first seven days of the month. So for which I have used a simple

month. So for which I have used a simple top end function go to the calendar table pick up the first seven days and for only those days please calculate your sales and that's fine. Now you're

going to see that at the totals row you don't have anything here because the context actually changes here at the totals row the context is 2017 and what you are taking a look at is the first

seven days of the start of the year which is going to be January 7 days because there was no sales in the month of January so you have nothing here that's empty and here you have again the first seven days of the month of January

but this is not quite what I want if I'm taking a look at top seven days of the month or the first seven days of the month then at the total level I don't really want to take a look at the yearly top seven sales which is technically the month of January number right here which

is not what I want. I want to sum it all that the first seven days included for all the months what's the total at the bottom here. Now these are semi-additive

bottom here. Now these are semi-additive calculations. The easiest way to solve

calculations. The easiest way to solve this problem is that PowerBI forgets that what was the number here and here and here and here. So you have to artificially put that number in

PowerBI's brain and help it remember hey just a while ago when you were there in the previous row you were doing yearly and monthly totals and that's what I want to take a look at. So how do you fix it? You just create that artificial

fix it? You just create that artificial table which you can see it through your eyes right here which is year, month and the top sales and then ask PowerBI to go through that table. How do you do that?

Take a look. So I'm going to go ahead and start to write a summarize function.

So I'll say hey I want to do summarize.

Let's just delete that part. I want to say summarize in the summarize function.

The first part is the table. So what I want to do is I want to summarize my calendar table. And the calendar table

calendar table. And the calendar table is going to be summarized by two columns which is going to be my calendar year and the calendar month right here. Once

I've summarized these two columns, now what I want to do is I want to go in every single row of this particular table like the first row and then calculate this number. Then go to the second row and then calculate this number. So on and so forth. So I'm just

number. So on and so forth. So I'm just going to go ahead and do the add columns right here. So I'll say hey I want to

right here. So I'll say hey I want to add columns to this particular table.

I'll start the bracket and the column that I would like to add to this particular table is nothing but the top end calculation that we just did. So

I'll say top end and then I will just do that which is nothing but the calculate function that we wrote and the top end against that. That's my calculation. And

against that. That's my calculation. And

this calculation is now going to run across all the rows of the table. Now

once this particular table has been formed, PowerBI is going to remember once it reaches here which is at the total level. It'll have to summarize by

total level. It'll have to summarize by the year and the month. It'll have to do this particular calculation. It'll

remember all of these values that you left by. And now all that I can do is I

left by. And now all that I can do is I can just do the sumx function start the bracket and I can you know kind of iterate through this particular table and then I can say hey why don't you just do the top end sum. The one

additional thing that we needed in the end is we also need to context transition this particular table. So we

can also wrap this around in the calculate table function. Start the

bracket and close the bracket towards the end. And that completes our formula.

the end. And that completes our formula.

Now if you do this and if you save the formula and this is going to refresh and now you have the correct totals right here for all of these months total right at the bottom of this table and right at this table as well which is beautiful.

The next question comes from Leonel is what do you think of the no calculate approach by Greg Deckler? What are the pros and cons of that? Now I believe that Greg is right. All that he's trying to do is simplify the understanding of

tax by creating tables and then iterating through it. That's what his approach is like. Why don't you form a table and you can take a look at that table and then iterate through the table to get your answers. And he in his book has several examples as well that talk

about the similar approach. So I don't think that approach is bad if you want to develop your understanding of how tables are formed. In my opinion, I favor his approach, but I also don't mind learning the calculate function

because the versatility of the calculate function is pretty good. And there are a few scenarios where the calculate function can do a few things that iteration cannot. For example, some used

iteration cannot. For example, some used relationship kind of formulas where you have to activate an inactive relationship. Nevertheless, I like his

relationship. Nevertheless, I like his approach, but I don't mind learning calculator and teaching calculator as well. My final set of tricks are

well. My final set of tricks are visualization tricks and these are just going to help you beef up the quality of your visuals, beef up the insights of your visuals. The very first one is

your visuals. The very first one is using visual calculations to find the max and the min values. It's

interesting. Take a look. So I've got the year slicer right here and through which I have taken a look at all the months in that year. And what I want to do is I want to build a line chart and that is going to be something like this.

And on the line I want to highlight the marker on the point which is not this point particular but this point in particular which is going to be the highest value of the month. So I want to kind of do the dot right here. Well, to

be able to do that kind of dot, I need to identify which month was the highest and then I can do the dot. Let's just

see how can we do that not by DAX but by using visual calculations which is relatively easier. So, I'm going to go

relatively easier. So, I'm going to go to this particular visualization and first we will work with a simple table visual and then write some calculations.

So, in the modeling tab we have the new visual calculations. I can click on that

visual calculations. I can click on that and then I can say this is going to be my max value my max value. Now the way that I think about it is something like this which is where hey I'll say that please take a look at this entire table

and this entire table is referred by a keyword called rows. So once I take a look at this entire table I find out the maximum value. So I'm just going to say

maximum value. So I'm just going to say something like this. I want to say that hey rows is what refers to this entire table and I want to pick up the maximum value in this particular table. So I can just wrap this around in the max x

function and then say rows and I want to find the maximum of the total sales value. If I close the bracket and press

value. If I close the bracket and press enter, I get the maximum sales value for the month of whatever month that was. So

I think this is the month and that's the maximum value that I get across all the cells. Now I don't really want to have

cells. Now I don't really want to have the rest of the cells. I only want to have this particular cell because that is going to be highlighted by the marker. Now that's a simple check. I

marker. Now that's a simple check. I

want to check, hey, are you matching with this? Are you matching with this?

with this? Are you matching with this?

Are you matching with this? So on and so forth. So all that I'm just going to do

forth. So all that I'm just going to do is go back and take a look at my current value. So for which I can say my current

value. So for which I can say my current current value is going to be nothing but my total sales. And then I can build uh a check. This is my max and then max

a check. This is my max and then max value. And then I can just go ahead and

value. And then I can just go ahead and build a check. So this is my check. And

I'm going to say, hey, is the max value equal to the current value or not? And

if this particular check is true, then I want to return my total sales or my current value, whatever you'd like.

Otherwise, I just want to return a blank. Press enter. And then I think I

blank. Press enter. And then I think I forgot the return statement. So I'll

just do the return and return the check instead. And that's it. If I now press

instead. And that's it. If I now press enter, you can see that I get the value only here. Now once we've gotten this

only here. Now once we've gotten this particular thing, you can also do something very similar for the lowest value and then you can have another column for that. That's it. As simple as that. Now I'm going to show you how do

that. Now I'm going to show you how do you convert it to a chart. So you take this particular visualization and you convert it to a line chart. Once you

make the chart because you can see that I only have one single data point for the highest value. It automatically

shows up as a marker and that is nice.

If you happen to change the visualization to 2020, some other month gets highlighted 2018. Some other month gets highlighted 2017. Some other months get highlighted. And this is beautiful.

get highlighted. And this is beautiful.

It's the easiest way of highlighting the maximum and the minimum values. Once you

understand the concept of rows and visual calculations, you can design any kind of customized calculations just based on the visual data that you see in the matrix. My next trick is data bars

the matrix. My next trick is data bars using conditional formatting. And

everybody uses that, but I want to talk about a nifty trick. What happens when you use a data bar? I'm sure you know how to do that, is that a bar is going to be created right here in the cell and that sometimes overlaps the value. Now

you can tone down the color of the bar and things like that, but that's still doesn't look very nice. It looks clunky.

So what you can do is you can make an exclusive bar and hide the values. How

do you do that? Let me show that to you.

So I'm going to right click on the sales table and ask it to make a new measure.

And this is going to be my bar measure.

And I am just going to reference my total sales just as the way as it is not top sales but total sales calculation.

Press enter. And my total sales is now referenced in a bar measure. I drag that off to my visual. I'm actually going to go ahead and move that to the first place right here. or maybe the second place right here. Now once I have done

that, I will actually trigger the conditional formatting uh of the data bars on this particular calculation. So

conditional formatting and then I will go data bars and then I'm just going to say hey the data bars on this let's just say that it'll have maybe like a color of blue or something. That's nice. I

want to show the bar only and I don't really really want to show the axis as well. So that goes white. That's good.

well. So that goes white. That's good.

Direction is left to right. All is good.

I'm just going to click on okay and we have the bars right here. Now you can obviously turn the totals off as well.

But now you can take a look that the visualization looks quite neat and quite clean. You have the number right here

clean. You have the number right here and you have the bar right here. It

looks very very presentable. The last

question is from Finfi. She says that after 12 years of long journey of YouTube, how much money do you make from YouTube? Okay, so money is quite the

YouTube? Okay, so money is quite the question that everybody is interested in and I'll try to give you numbers as transparently as possible. First of all, we just don't make money from YouTube.

We have other sources of making money as well. I will list down the sources and

well. I will list down the sources and I'll tell you that YouTube is one of the most terrible sources of making money.

So when you turn on the ads on YouTube, people see these ads and through that we make about $1,000 every single month round about that 1,000 900, 1100 at times. Sometimes it goes to 1,300 but

times. Sometimes it goes to 1,300 but mostly stays around the benchmark of,000. Now that is just probably barely

of,000. Now that is just probably barely enough to pay for the software bills every single month. Maybe the software bills are more than that. through all of the attention that we gather through YouTube, people like you who are

watching us, we sell them our premium content. So which is our courses and our

content. So which is our courses and our books and live trainings at times and I get to be invited doing training sessions in the companies in India which is very very small at the moment but still I do get invited and I do get

invited to speak at conferences. All of

the courses, books, invitations to come and speak are one segment of content business. Then through that a lot of

business. Then through that a lot of people fall in the second bucket which is where they are more senior people and they asks hey can you just perhaps do the project for us can you do the consulting work for us and that

translates into consulting for which we have a separate company. So all of this did not come about in just about like a day or so. We gradually built all of these revenue lines up. But if you're particularly asking that how much money

literally do I make off of YouTube as a platform that's about $1,000 a month.

That's pretty much it. It's only when we add additional sources of revenue through consulting, content, books, courses, other sources all put together, it then makes the business nice and flourishing and we could pay our

employees and all of that stuff. My next

trick is quite useful and somehow a lot of people seem to need that. If you work with Excel pivot tables, you're going to see that often times you're going to pick up a classic style pivot table layout because it takes the fields and

put them out in a separate field or separate column instead. How do you do that inside of a pivot table? things

have been changing quite a lot in terms of the user interface. So I will talk about that once again. So let's just say that I right click on the empty visual and I say that I want to make a new visual and that visual is nothing but the pivot table by the way which is

called as the matrix right here. I don't

know why not a pivot table but a matrix.

And now you have similar options like the way that you have it in Excel which is rows, columns and the values. Let's

just drag something in the rows. So I

will drag two things. Let's just say that I drag the year and the month and against the values I will drag my total sales calculation right here and press enter. Now, as soon as you do that,

enter. Now, as soon as you do that, you're going to see that you have the plus minus signs. And even if you click on the expand sign, all of the two columns that we had, year and the month, they are there in the first column. This

is called the compact layout. Not quite

the one that we needed. We want two separate columns, one for the year and the other one for the month. How do you do that? Click on the visual that you

do that? Click on the visual that you have made and then go over to the formatting options. In the formatting

formatting options. In the formatting options, you're going to go over to layout and change the layout from compact to a tabular. And that appears something like this. In case you would like to repeat the labels right here,

you can also do that by actually clicking on the repeat row headers and that actually repeats the label as well.

And then you can format it the way that you like. But this is quite similar to

you like. But this is quite similar to what you would do it in a classic style layout in a pivot table. All right

people, thank you so much for being the part of the competition in the last video. The secret word was definitely

video. The secret word was definitely queen. A lot of you did spot it out. And

queen. A lot of you did spot it out. And

we have put all the names of the people who have participated in the competition. And we're going to spin the

competition. And we're going to spin the wheel and let's just see who's going to be the winner.

And the winner is congratulations for winning one year subscription to Goodly Insider. Somebody from my team is going

Insider. Somebody from my team is going to reach out to you and give you one year Goodly Insider access. My next

visual calculation trick which is super super helpful in a lot of scenarios is customizing how the numbers look on the screen which is through dynamic formatting strings of a measure. super

helpful. Take a look. So I've got the year and the month and against that I have sales but I also want to have how many units were sold. And if I happen to drag the total units measure which is by the way a simple sum of the quantity

column as simple as that. If I drag this particular measure it is not going to show you the sum not just the sum but in brackets it also shows you how many units were refunded. And that's

interesting. Now the custom representation of what I have shown right here could change it to anything that you like but this is one example and you can take it to whatever extent once you understand that how did I do

this so now the first thing that you would do is you would actually click on the calculation that you've made which is appearing as a very straightforward calculation nothing that fancy and then I'm just going to change the formatting

of this particular measure from a standard format to a dynamic format up on the top you have the measure tools and we have a measure tools right here in the format I chose something called as dynamic and you can also do that in

the model view right here. So if I just go take a look at my total units measure and that's where I have made it as dynamic. Now here you actually get the

dynamic. Now here you actually get the option of creating a DAX code that is going to define what that dynamic string is going to be. Now I click on edit in the DAX formula and I'm just going to show you that what my DAX formula is and

how it is concatenating the units refunded. So if you take a look the drop

refunded. So if you take a look the drop down here has changed from the measure to the format as soon as I clicked right here and then it's showing you that what is the DAX being used to just beautified

the cosmetic change the number is the units number this is just the beautifification or the cosmetic change I've taken the selected measure whatever that is units or whatever that is I've done that a one decimal place and then I

have also concatenated the is refunds column in a bracket and things like that that's simply it you just need to add these empty strings at the start to make it work and that's about it now the

number that you see right here is 1 1 09 and all the future calculations all the other calculations are going to happen on this number not the six the six is just an added custom formatted thing

that can enhance the look and feel of it or maybe customize the look of it once you understand how this technique works you can carry it over to charts your tables anything for that matter the number stays the way that you have it in

DAX but the formatting can be customized to the next extent I've done an exclusive video on this in case you would like to see I'm going to leave a link and you should watch that definitely Hey.

[music]