DAX Tricks - Webinar

all right hi everyone I hope you can see me I think it's life that's what it says here and you can also listen to my voice just give me a thumbs up in the comments if you were able to listen to me and you were able to see the video as well just give me a thumbs up in the comments if you're able to see me watch the video as well and of course if you're able to listen to the voice as well all right hi people hi Chantelle oh hi children hi Saurabh I'm Shaka 18 what's up so Jay I how are you good good yeah guys I'm super happy to kind of do this on this for the second time here although we don't have a big turnout turnout I can I'm surprised that we don't have a lot of people here today but that's okay we'll get to answer a lot of questions so if there are less of people I get to solve more doubts handle more questions so that's that's actually a good thing all right hey a PNA good to see you up hey what's up good to see you again here as these what's up good to see you I'm gonna start in another two not two but let's say one minute or so and then we just kind of go on with tax like a quick question for all of you how many of you here work with Dax actively I mean you work with Bobby I you right that's in your in your data models and you do a lot of tax calculations I'd like to know that how many of you actively work with Dax here so in the comments if you can just mention if you do work with Dax I'd be interested to know even that what's up Keshawn what's up good to see you man Poosh what's up all right cool cool so I see that a lot of people do work with knacks although I will kind of you know kind of make this session and intermediate session not discuss very complicated topic syntax as well but not discussed - basic stuff as well like some count kind of that stuff so we learn some interesting tricks around Dax you know that you can actually use in your data models to make it more effective make your DAC supports very very effective and cool so also off late what happened was that recently me along with a couple of other people who work with Excel and we got together and then we created a course on Excel and the link for that course is actually down in the description of the video so you can go watch that and this course the proceeds from the course will directly go to the kovat 19 people I mean the poor people who are affected from 4:19 they're just going to go to them so it was actually a good cause I was very happy to be a part of it it was actually not my initiative it was an initiative from Ola and she runs again an Excel blog so I think she is based out in Dublin so she kind of got together all the people and then she asked us to kind of prepare a small tutorial everybody contributed their part and we created a course it's actually a very good course I first need learned myself and I've learned to quite a bu quite quite some interesting stuff around automation around other tricks in Excel that I did not know myself so if you haven't actually you know bought that I will highly recommend that you go take a take a copy of that it's not much it's just about ten dollars in case you'd like to donate more it's about twenty dollars so it's actually a good thing that you can contribute to that cause I mean you learn of Excel that's a good thing but you will also contribute to a good cause around and yeah and that's actually a good thing and I also kind of for those people who did you know buy that course I was also giving a little appreciation you can call it as like a 50% off on my course that I'm going to be releasing soon on on Dax so I feel free I mean it's no pressure but it will be nice if you did all right let's just kind of start before I start I highly recommend that you download the data again the data the link for downloading the data that we are going to use is again in the in the description of the video so you can just maybe go there and just just download the data it's a very simple data model that we'll be working with today so you just download the data model and we just get get going all right all right so good for T or people here let's just get started I'm just going to flip over and maybe show you my screen and we get started alright so I have turned on my screen and hopefully you can see it all right one second all right so you can see that this is a like a power bi file it's empty there are no visuals here I'm quickly going to explain you the data model that we have here it's really simple all that I have here is the products we have the calendar and we have the sales table let me also activate my zoom tool actually no I don't have it okay now mine so we have products we have calendar and we have the sales table right here and let me just show you the three tables that we have so in the sales table we have our transaction ID we have date we have region the product the units the channel and the auto type as support or no priority that's the kind of data that we have and this is a transactional data that means every single sale means one single transaction that's what this data means and in the products table we have the the products years we have brought a code type product category and the price of the product alright so this is nothing but the master table or the dimension table if you'd like to call it that way and that links to my sales table using the product code right here and then we have a calendar table simple calendar table nothing to complicate it here we have the date column the index the month and the year and that actually links to my sales table right here all right so the the first thing that I'd like to do is the problem of let's say kind of the granularity problem let's just say that I am creating a simple pivot table so I drag my ear into the pivot so here it is right here then I drag the month into the pivot and in the visualizations I make it as a matrix visualization if you did not know it already matrix is actually the pivot table of power bi so so I make it as a pivot table we have here and the month I expand that and also let me just zoom out in in Excel if you would like to kind of split it into two different columns either you know you do a what is it called classically out or you do we lay out but in power bi that's called stepping so if I just go over to the format tab I write the word step I can actually turn it off step layout off that will actually bring it into two different columns all right good and let's just also write a simple sales measure so if you just take a look here if I go back to my sales table in my sales table if I take the units and if I multiply the units to the price which is in the products table I will be able to get sales so that is a classic stomach so I'm just gonna write a new measure I'm sure if you have done some preliminary tax you would be aware of writing some simple sum X measure so I'm just going to write a simple measure called total sales and I'm gonna say hey why don't you go in every single row of the sales table and in every single row of the sales table why don't you do a multiplication of the price the price is going to come from the products table so I will get the price from there and I'll multiply with the units in the sales table so related is nothing but the vlookup of Barbee I I we look up for the price and multiply that with the units and then eventually I sum up all the records so that's the sonics for you I kind of commit to that function and the measure that I've made and I drag that to my pivot table and also let's just increase the size of this this is actually too small so I'm gonna go to the grid here and I'm gonna increase the size here so let's just make it I don't know maybe 15 or so let's just make it 20 or all right now this is a legible I believe okay so let's say we have a total sales of the year and the month all right and what I'd like to do is I'd like to find out what was the sales of the best-selling product that's what I'd like to find out so in the month of January says the product ID and all of these products were sold but I'd like to find out the sales of the best-selling product right so against the sale of $1200 what was the sale of the best-selling product now this is a classic problem of granularity now if you take a look at your sales table in my sales table one record means one single transaction and although I would like to do the calculation per product if I'm able to calculate the sales of every single product then I can find which product is the max sales right so that's that's easy to find out but the problem is that in my sales table I have one single transaction the granularity is transaction so one transaction means one row so how do I actually get rid of that granularity of transactions and come to the level of products so what I can do is I can do two things okay so I'm sure first method you would know off is let's see I'm just gonna create another measure so I'll say new measure and I'm gonna say Max product sales so which is zoom in and I'm gonna say that I'd like to calculate max product sales and I'm gonna say max X now if I just say max X and then if I just go in every single row of the sales table the problem is that if I just kind of write this expression and I close the bracket and I commit to this formula this will actually give me the maximum transaction value not the product value so I don't want the transaction value so if you actually kind of commit to that formula so let's just see that if I kind of press ENTER and commit to this formula this and drag this formula to my 1.12 my pivot table what I get here is the maximum transaction value and you can actually do a vlookup in your sales table and multiply every single value in the month of Jan you will see that one transaction is going to give you one hundred and seventy four dollars you know all right so how do I actually find out what is the sales of one single product instead of writing the sales table here I can write the products table here because if you take a look at the products table one single row means one single product because you have unique products over there now what this is going to do is this is going to go in every single row of the products table for the month of January calculate product by product by product sales that's what it will do and then find the max of it now if this if I write this measure this is going to be max product sales that's exactly what I wanted so if I commit to that and you can see that we have 270 as an answer if you actually want to test it I can help you do that so what I'm going to do is I'm gonna real quick maybe make a column here and let's just test it out so what I'm gonna do is let's just say that I'd like to call this as price or let's just call this as sales because I am just going to write the related function and get the price of the product multiplied the price of the product with the units now that's what you would do it in Excel you would create a column write a vlookup get the price and multiply the price with the units and that's how you will get the sales and then you will just kind of make a pivot table like the way you would do it so I'm just going to copy this table so copy table and kind of take it to excel so paste that into Excel now I'm gonna make a pivot table from here and just find if I actually get that answer or not so I'm gonna take the date put it in two rows and that's what I have it and I don't want the quarter's maybe I can just expand the pivot table so in the analyze tab I can click on the plus sign to expand the pivot table and I am just gonna also add the product ID here and of course the sales value here and if I just maybe take a look at the month of Jan 270 let's just sort this so sort in the largest or smallest order that's the product 270 is our answer and for the month of feb 3 hundred and fifty-nine is I want so that's the maximum sales so let's just see if we do get that answer or not right that's that's the answer that we get and that's an absolutely correct so what you can do is using your master table or your dimension table you can actually drive the the formula from there using any maxixe or any hydrator and then calculate the sale now the trick that I wanted to talk about is let's say for example for some reason if you let's say did not have the product master you don't have the product master you just have the Tran action or data because in our in our if you take a look at the formula that we have we have used actually a max X and we have say hey go to the products table and the granularity of the products table is one product or one row as in one product and that's hence we have been able to calculate the maximum sales per product but what if this table wasn't there what you can do is you can alter the granularity of that table by using the values function what the values function does is it actually goes to a one column I mean what a values function actually accepts one column in our table so I can write maybe the sales table and I can write the product ID now if product ID is one single column of the sales table that is the input of the values function as an output of the values function what it will deliver to you it will deliver unique values in that particular column so again you will get a table but in that table you'll get unique products only and if I commit to this formula and again take a look at my result my result is absolutely going to be the same now I do it a lot of times I do this trick a lot of times especially when I don't have the master table and I want to alter the granularity of my transactional table and come maybe one or two levels up to a products level or to another level and then kind of work it out now if you take a look at this trick this trick is also going to be applicable to the calendar month let's see for example what I'd like to do is I'd like to find out the best selling day in the month of January alright so if I maybe just copy that measure and again right-click on the sales and maybe say new measure no come on this is slow and I'm just gonna paste that so max let's just call this as best Mac sales day max day sales let's just call that max day sales and I'm just gonna delete that part all right now what I'm gonna do is if I want to find the max day sales again I can play two tricks I can actually write the calendar table here that's what I can do and this is actually going to give me the the sales of one day that was the maximum and if I actually drag that to my pivot table and if I actually take a look here you will see that this this number 192 is the sales that you actually had it on one particular day in the month of June right and 4:07 is the sale of one particular day in the entire year somewhere right and that and that entire month was somewhere in the month of November right so this is actually the best and that also is the best for the entire year all right so what you can do is instead of writing the calendar table you can also again do a values function and kind of change the granularity of the sales table which is as of now a transactional table - let's say calendar date not calendar date actually let's say the sales state and I will change the granularity of the sales the sales table and bring it to the day level what this is going to do is if at all there are duplicates in the sales date column it would remove the duplicate do a day by day by day by day calculation of sales and then give you the maximum of that right so that's pretty pretty awesome so these are - these are kind of two tricks in which you can if you have the the master table you can drive the calculation from the master table and that's also a good thing to do actually but if you don't have a master table actually you can kind of change the granularity of the of their calculation by using the values function all right this can also be done using the summarized function which actually accepts two or three inputs but I'd like to do it using the values function all right let's just proceed all right another thing that I do it very often is that I check the validity of my of my masters a lot so let's just say that if you take a look at the product master now if you working with a large FMCG or a retail company there could be probably hundreds or thousands or hundreds of thousands of products and your master could be very very long you could probably have five thousand SKU is ten thousand excuse and if you have an SKU master you probably gonna have a lot or lot of rows now the thing is that the product master will have all the products that are kept on the shelves no matter they product they sell or they don't sell and your sales table is going to capture all those records which are actually going to sell now what you can do is in the master you can real quick check that which products am i selling and which products mn not selling this is going to be true for the entire sales table right it's a really smart way to do that so if you go to the products table and if I just create a column real quick here so new column now if you wanted to do a vlookup you will write the related function but the related function is we will write it in the transactional table right and you will fetch the unique values from the dimension table but I will write the reverse of related and I will write the related table so I'm just gonna write let's say related table now the related table function accepts just one input which is the name of the table and let's say the name of my table of sales and I close the bracket now what is the output of this I'll tell you if I commit to this formula right now this is going to give me an error I'll tell you why what this is going to do is this is going to actually take this product code because we have linked the two tables using the product code it's going to actually take this product code and follow this relationship to the sales table so this it will come here to the sales table and the sales table is going to get filtered by this product code so FS e BK is going to filter the sales table and you will have the entire sales table with just one product code F se became the related table function actually delivers you a table as an output and in one single column you cannot fit the entire table but I don't really want a table here actually I just want has it sold has it not sold if I could just maybe count the number of rows here or this particular table count rose function is asking you for a table related table is delivering you a table they both match so I close the bracket commit to that and you can see that right here what I'm going to get is actually the number of times that product appeared in my sales table now as of now this is actually a good company it kind of sells all the products that it has it in the master but if you're naturally working with a large setup with some live data and stuff you would actually find a lot of rows blank here those rows are the rows which never had a mention in your sales transaction beta so you can lighten up your masters by doing this this is not really a calculation that I would you know suggest you to do it it's just a quick check that you can do it in order to just take a look at your master table and maybe maybe if you would like to consider removing a couple of rows on that were never actually sold in the sales table so that's what you can do so it's a really really smart calculation that you can do to just kind of check your masters or your dimension tables all right um another thing actually that a lot of people would like to do is present their data by let's say fiscal year and fiscal quarter and maybe even the the order of the fiscal month and we would like to do that in India because in India now our year starts with April and ends with March so I'd like to actually not start my representation of the data by let's say January I'd like to start it with April so how do I actually first of all bring the April months to the start here that's what that's one thing and I don't want to write let's say 2011 I'd rather want to write 10 11 11 12 and that's the kind of stuff that I want to write additionally maybe I also want to group the dates into quarters and my first quarter is going to be April May June and hot Jen Fair March and fab March is actually going to be quarter 4 so how do I do all of that so real quick let's just go to the calendar we'll have to make some modifications to the calendar table and in the calendar table if you actually write number 3 in the calendar auto function calendar will start from April this is an optional input not a lot of people know about it so calendar Auto actually generates a series of dates it will just scan your entire model pick up all the date columns and just take a look at all the dates and bring the smallest state to the start of the earth take the largest state to the end of the earth and create one single column with dates from the start till the end that's what the calendar auto function does if you don't write anything in the calendar auto function it works from January to December but you can modify it you can say that hey I'd like to start my calendar from April not by not from January so I can specify number three number three means that my fiscal year ends in the month of March that's what I write so three for March I commit to that I press enter you can see that my calendar now starts not with Jan actually it starts with April don't worry it actually starts with a plus just have to sort this so sort ascending and it's actually starts with April 2010 that's exactly what I wanted now let's just add a couple of columns here now I've used add columns here you can use that if you would like but let's just actually physically add columns to this table and let's just calculate the the financial year in the quarter and stuff like that so I'm just going to right click here and maybe say that I'd like to make a new column all right come on come on come on all right and I'm just gonna say hey I'd like to add financial year to my calculation mm and I'm gonna say if the year we are actually already actually let's just say if the month of just call it if the month function of the calendar date is let's say less than four that means you are still in April then I'd like to go previous year so year of the calendar date minus one and I'm gonna say year of the calendar date and then plus one actually let's just wrap this in the ear function and let's just call this a state and this is not going to be plus one this is going to be the same year itself all right so I'm saying that if you are still in Jan Feb and March right if the month is less than 4 which is Jennifer March then the way I'd like to write it I'd like to write 1011 not 1112 right so the current year which is whatever the current here is minus one will take one year back and I'd like to concatenate that with the current year but if that is not the case what I'd like to do is I'd like to say year of the calendar date that means the current year and I'd like to concatenate that with the year of let's say the calendar date plus one all right we have actually gone to the next year close the bracket commit to this formula let's see what do we get all right this is absolutely correct so 2010-11 is is a year now maybe you'd like to kind of add some dashes and that kind of stuff so you can do that so I'm just gonna add some dashes and stuff and had some dashes and stuff and maybe just went to that all right this I think this should work and we have 2010-2011 as one particular column created all right now let's just create a column for the quarter right so I'm just gonna say that I'd like to make a new column all right ah this this method that I'm gonna teach you to calculate fiscal quarter is also going to work in Excel so if you work with Excel and you'd like to do the same trick in Excel you can also actually do the same same formula nearly the same formula actually so I'm gonna call this as a financial quarter and let's just say that I'd like to first of all take the date three months back so calendar date I'd like to push it three months back all right so ediot function actually can take the date forward in time it can actually take the date backward in time why am I taking three months back because in India the fiscal kind of year starts in April although three months have passed by Jan Feb March have passed by but we are still kind of doing the accounting for the last year so we kind of pushing the date back three months right so if you are actually having April here what you would get is first of January in this particular column keep watching so I get um kind of first a date here which is first of Jan against first of April I've pushed the date three months behind now off this date that I get of three months behind I'm gonna find the month number so just give me the month number of the three months behind it if you're currently in April you push the date three months back you get Jan for the month of January so I'm gonna get one here all right now what I'm going to do is I'm going to use the ceiling function and push this by a multiplier of three so one becomes three two becomes three three becomes three four becomes six five becomes six and six becomes six so this is like a multiplier of three so ceiling I'm saying the month is actually giving me the month number from one to twelve and I'd like to kind of factor that by a multiplier of three so eventually in this particular column you will get three a six a nine or a 12 if I just take a look 3 6 9 and 12 that's what I get and what I am going to do is I'm going to add kind of divide that by 3 and I'm gonna get the quarter number so April is q1 that's what I get so q1 q2 q3 q4 and that's what I get now I think there is an easier formula to do that as well which I figured last I think a few days back let me just kind of try that out real quick so let's just say that I am going to maybe just copy this part of the formula this part of the formula now in this part of the formula what I'm doing is taking the date three months behind and off the date which is three months behind I'd like to find the quarter number so I'm just going to use the format function I think it should work and I'd like to format this into QQ means that give me the quarter so Q will actually give you Q 1 Q 2 Q 3 Q 4 that's what it will do and I'll just like would like to prefix that with a Q at the start of this right so that's what I think let's see if we get the same result or not I think we get the same result so this is actually financial water so let's just see so we have quarter 1/4 2/4 3/4 for if I actually select quarter one the only months that I should see here is April May and June that's correct and if I actually see select quarter for here the only months that I should see here is I don't know Jan Feb March so Jan Feb March that's absolutely correct all right so this is working absolutely fine all right so here this is actually a trick so you can use the format function to convert the date into the quarter right so this cue is not available in Excel if you would like to do that in Excel you would have to form you know kind of use this method all right so I'm just going to commit to that and now what I'm going to do is I'm gonna actually take my fine Angelia put it in my rolls right here and take the financial water put it in the rose right here and expand that and that's the data that we have presented now note that we had Jan Feb March data for 2011 and that has become q4 of the last year and then that's that's the numbers that I have all right you can also you know get the month here so if I just kind of get the month here and if I expand it so we have Jan Feb March April May June July and that's absolutely fine so we start with Ashley April and this works absolutely fine all right let's just maybe kind of revert back to the calendar year and that's where we will do kind of all the calculations now if you would like to do a fiscal year calculations I have already left a LinkedIn in the description of the video and I've actually created a full calendar table with all these columns that you would probably need in just one code all that you'll have to do is come here and copy and paste the code and as your calendar table or your day table and this will work and something interesting that I've already done there I've also calculated fiscal weeks so you can also maybe take a look at that okay let's just proceed okay another thing that a lot of people would like to do is maybe write a count if in their in their in their data so I've seen this a lot of times and this happens to be one of one of the most visited articles on my blog as well which is where people would just want to do an excel like countif in power bi I don't know for what reason but they're trying to do that I could maybe think of a few reasons that why would you like to do that maybe you would like to find if there are duplicates here or not especially in your master tables because when you build a relationship of one-to-many nature in the master table it doesn't really except if there are duplicates of the column that you're trying to link so maybe they would like to do a quick check of writing a counter but you don't have a count of formula in in power bi so how do you actually pull that count if off if you would want to do a count of here so let's say that you know against the date column I'd like to do a count it let's just sort the date in the ascending order and you can see that I have 2nd January coming two times and maybe 6 January coming three times and that's the number that I'd like to see here so what I'm gonna do is I'm gonna add a new column right here so the the logic of doing a count if is something like this so I'm gonna say hey why don't you just maybe grab this value 2nd January and see in the entire table if you find any more 2nd January's or not so if you'll find actually 2 times 2nd January it will find it once it will find it twice and against both of these you know row values it'll ID number 2 and number 2 so the first thing is that I want to grab this value the value of the current row right so let's just say that I'd like to call this as count if and oops and I'm gonna say that I'd like to declare a variable now there are two ways to do it I'm just going to show you the variable way so I'm gonna declare a variable and I'm gonna say hey the current row date is a variable so I'm just gonna say current row date and the current row date is nothing but the actually sales table date column is the control rate so this becomes a variable now variable are just calculated once now anything that I do further it will actually pick up 2nd January right so every single it will actually pre calculate whatever date is there in the particular row it will freeze that and I can actually use second January to kind of map it up in the entire column and see if you find any other second January all right so I'm gonna say that return and I'm going to create a small table so I will use the filter function and I'm gonna say hey why don't you remove the filters from you know the sales table so I'm gonna say all all the sales table so why don't you get rid of the transaction ID get rid of the date region all of these filters just remove all the filters consider the entire sales table you can actually also go like consider the entire date column of the sales table right and then I'm gonna say that remove any filters from you and then why don't you see that where is that so if you just maybe see okay current row date if you just compare the current row date with the date column and now if I commit to this formula this formula is gonna give me an error because filter table actually gives you a table and I don't want a table what this will do is this will kind of open up the filters from the date column and start comparing the current row date with the sales date lets you see if this actually gives me the correct value or not I'm kind of skeptical and then I just want to actually count the number of rows here so I'm just gonna say count rows of this table that gets created all right so let's just see if that works so close the bracket commit to that press Enter let's see if that works it actually doesn't work because if when it removes the filter from the date column it actually comes to it just finds second January as one because the all function gives you the possible unique columns that are there in the table so if you kind of remove duplicates from second January it will just count second January as one so if I just kind of write the all function here and even remove the date filter and I commit to that this is actually going to give you number two and number two and you can see there again six January you can see it three three times so what I'm trying to do is I'm trying to say that hey why don't you remove all the filter from the date column or from the entire sales table and then you pick the current rotate which is pre calculated as second January and you compare that in the entire sale state column and find out how many number of times that you find it so actually if you take a look at the result of this table it will actually find it exactly two times for second January and it kind of forms the table which will have two rows and I just want to count the number of rows there so this is exactly nothing but your counter if you don't would if you would not like to use the variable here you can also use the earlier function here the earlier means the current row so I can actually also use that and this will also give me the the same value and then in that case I can just get rid off the variable right so both the functions are absolutely fine so I can just maybe comment that and write another one which is nothing but my current row let's just comment that and you can just take a look at both of them so current row is equals to sales state column all right and I commit to that and this is the Camco to give me the same value so that's the way you can kind of do countifs and you can also do multi multi condition count if by combining two columns and then kind of doing a counter right so yeah and it's a great way to check duplicates if you have any duplicates in your table all right another very very interesting kind of debugging technique that I personally follow is this so I use a mix of a lot of tools to kind of debug my my tax calculation so one is obviously that I use power bi to kind of write and do tax calculations the other one is tax to do I use that sometimes and the other one is Excel so Excel is a brilliant tool if I would like to kind of debug the only problem with Excel is that lets say for example if I would like to take my data to excel and the data is so large I mean it's more than a million rows then obviously the entire data would not fit in so you can see that a while ago actually I copied this data and I paste this into Excel now as of now this data has actually meaningless just about a thousand row data but you could have maybe a 1 million 5 million 10 million rows of data and in that case you would not be able to paste that data in Excel and do any kind of debugging or any kind of checking that you would like to do so what do you do you can actually use the row function generally what I do is let's say for example I maybe want to find let's say I'd like to apply a filter and I'd like to kind of filter for affiliate and say okay once I apply the filter the filter drawers are more than a million records and I would not be able to paste that into Excel and maybe I'd like to take a total of all the sales which are for affiliate now if I was able to filter the data and eyes if I was able to paste that into Excel I can actually kind of do some workings in Excel and I can kind of take the selected column and maybe take a look at the total at the bottom or do some kind of working there but as of now since the data is so large assume that he would not be able to paste that in Excel what you can do is you can make use of the row function it's actually brilliant so take a look so I'm going to go to the modeling tab and create a new table and let's just make a new table real quick and let's just say that let's just call it a dummy table now the first thing that you have to understand is that the row function actually gives you a table with just one single row you can have multiple columns in that table but it will just have one single row all right so I'm gonna say that the name of the column because you can have multiple columns so I would like to call the first column as total sales the total sales is the name of the column and the way I would like to calculate the value for that column is calculate the sales for affiliate only so I'm going to write the calculate function and I'm gonna say hey calculate total sales but for the channel is equals to affiliate AVI Liat alright I hope the spelling is right close the bracket press ENTER now what you can do is you can actually create a one columnar table whereas my dummy table and that is the sales for an affiliate so if you would like to quickly debug very very large tables and kind of do some you know quick calculations you can actually use the row function and you can also use this in that studio and you can also use this in power bi to kind of quickly take a look at you know how your models are doing and you know you can just kind of do an interim calculation to kind of debug so this is actually brilliant you can also add more columns here maybe I'd like to compare affiliate with organic sales so next column so let's just call this as total sales affiliates affiliates what's happening it's okay and then maybe I'll just want to copy the entire stuff and I'd like to say that if I've kind of put a comma here it kept come comes - name - name - means that I'd like to create a second column the row is going to be one but you now are creating another column so I'd like to call the second column as total sales for organic and yeah I mean I just want to calculate for organic right so I'm just going to commit to the formula press ENTER and this is gonna now create two columns with total sales affiliate and total sales organic so if you tables are really large you can create these - you know kind of these interim tables to see whether the value is right or not because in power ba the problem is that you cannot really for Excel users I mean we'd like to do it a lot so I'd like to select the column and take a look at the total at the bottom of the excel or kind of write a quick sum formula just to kind see that okay is this tallying up or not but if your if your row count is large you would not be able to do that and that's where you can make use of the row functions actually brilliant all right another another very very interesting thing that I want to do in my in my especially in my visualizations is using tags is maybe I'd like to press an Enter as explained to you so let's say I write maybe a measure and in the measure for now I'm just not creating any sophisticated measure let's just call this measure as a card and I'm just writing that say my name is John Reba all right this is just a text that I'm writing no tax code that's well that's what I write all right and I just drag that and I create a card out of that and you can see that my name is John deep is displayed as a card now what I'd like to do is I'd like to kind of put an enter after my name is and here I'd like to put enter now one way to do that is what you can do is you can kind of squeeze this in and see that where chandi's kind of falls to the next line that's that's one way to do that but maybe if you kind of stretch it back up it comes in the full row because it's kind of wrapping the text when you squeeze the size of the box but there is no form will enter that you have entered there I mean there is no Enter key so what I can do is now you can't really press enter here because if you press ENTER it commits to the formula but I can actually type enter using the ASCII characters so what I can do is I can use the n percent and I can say unique character which is a formula and number ten number ten is actually the code the unique character code for Enter so I'm going to say hey my name is actually no space and after this you press and enter unique character ten ten means enter in ASCII and then you write the word chandi so if I now kind of press come into this formula Randeep is actually going to come to in the in the next row so I kind of use this a lot especially when I have to bring certain text to the next row and kind of improve my space management so this is a trick that I use and you can also use it you can use it anywhere wherever you kind of combining multiple things and you would like to actually press ENTER so that's a brilliant way to use it all right let's just take a look another thing icons so next topic is icon so you can use icons in power bi like as an emojis so let's just say that I am quickly calculating sales of the let's say last month right so I'm just gonna get rid of this get rid of this so this is the sales of the current month I want to calculate the sales of the last month so I right-click on my sales table make a new measure and I say that this is let's say I'm just gonna use the growth calculations to growth over last month and I'm just going to declare a variable last month sales and I'm just gonna say calculate calculate what calculate total sales but not for the current month for the previous month and calendar date is where I have kept my date alright so that is my measure all right and then I'd like to declare another variable which is where I'll say current month sales current month sales which is nothing but my total sales so I just want to kind of assign that to a variable and now I'll say return I'm gonna say if if the last month sales is not equal to blank that means you have the last month sales and the correct month sales current month sales is not equal to blank if both these conditions are true in that case I'd like to calculate growth and growth is nothing but my current month divided by my last month minus 1 oops last month minus 1 all right close bracket commit to that press Enter and that is my growth calculation take technically speaking the growth calculation is only this much so your current one divided by your last one sales minus one but all this kind of jazz that I have done is to kind of make sure that the sales populate at the right places so if you haven't sold in the previous month you would not calculate the growth if you haven't sold in the current month you would again not calculate growth because you don't have the numbers and it would kind of give you an error so I have done all of this work to kind of silent all of that but the growth calculation is only this much so let's just drag this measure to my pivot and let's just convert that into a percentage let's just select here and let's just let's just convert that to a percentage sign what is happening Modelling percentage and one this one all right now what I'd like to do is I'd like to kind of show icons here and the way I'd like to show it is that if you have a negative growth that I'd like to show a cross and if you have a positive growth then I'd like to show a tick all right or maybe up arrow or down arrow let's just see if we can do that so I'm gonna write another measure and say new measure and gonna call this as KP a growth KPI let's just call this as growth KPI all right and I'm gonna say if come on if if the let's say the growth over last month is less than zero that means it's negative if it's negative then maybe I'd like to show a down arrow or maybe a cross whatever you would like to show so let's just say down arrow so I'm gonna go on the web and maybe find emoji pedia let's say down arrow emoji let's just see if we find that on the web somewhere all right so we have a website here emoji period let's see if we find anything there mmm maybe let's just use a cross I I don't really like this so I'm just gonna use the cross here all right cross mark and we'll copy that cross mark and come back to my attacks and come and come back and paste that in the inverted commas and if that is a if that is the other way I mean if the growth has happened I'd like to kind of use a tick mark and that's a check mark and I'm gonna copy that come back and paste that here close the bracket commit to that press Enter let's just see what we get all right it actually shows you four for the previous months as well so let's just say that so I am actually going to do this calculation when I have both the things available when the when the sales and the last month sales are there so I'm going to use another if and I'm gonna say if the total sales is not equal to blank and oh my god we haven't really created the last one so let's just only can you know kind of you work with this condition so actually we will have to create another condition for the for the last month sales as well maybe I can just write another calculate here actually I should have created another measure for last month's sales which I did not create that's why I'm having a problem here so I'm just writing a measure but instead you should actually create a new measure and then assign it here so I am just gonna say previous month calendar date close that not equal to blank all right and maybe I just use this as an end condition and put a comma right here and I commit to that all right now which actually shows me for the month so me so cross cross cross and tick mark tick mark that means the cross has happened cross cross cross so love crosses here just three or four tick marks when you've actually grown so this is actually a big one here already you've grown 216 percent over the previous month so this is actually good now this is something that you can do but I highly highly recommend that you just restrict yourself to meaningful emojis nor just kind of swarm your your models with just emojis all around so this is something that you can do you can also kind of add a slicer and put emojis there you can do that I mean I have done that maybe once or twice for some meaningful purposes so let's just say that I right click here and make a new column here and I'd like to kind of show priority or no priority as a tick mark and no tick mark so I'm going to call this as maybe priority pretty check and I'm gonna say if the order type is equals to priority then I'd like to do a tick mark so where is the tick mark copy that and I'm just gonna paste that right here otherwise I'm gonna do a cross mark all right so I'm gonna say cross mark right here copy that copy that come back to my power bi paste that Coke that way sir thank you all right now created a column here with tick marks and cross marks right so this is actually showing me if I have a priority transaction or a support no supports transaction now because you have created a column and on the column you can make a slicer so let's make a slice it and the slicer is going to show you these icons so this is that's actually pretty cool so I'm just going to come here to the priority and maybe get that in the fields right here and that's what we have right so we have a check mark and cross and you can actually take it here you can cross it here that works fine you can also convert it like an excel slicer so that's what we have in excel excel slicer so that also works fine all right so you can do these things but just one one one recommendation to all of you just don't go overboard with these things these things look cool but if you kind of swarm your models and your pivot tables and your you know kind of dashboards with these your dashboard kind of clogs and doesn't really make a lot of sense so just use it with some meaningful icons at some meaningful places and just see where you're using it so just take it with a pinch of salt okay all right ah let's just proceed the last one here is the use of q8e quick quick access toolbar that's what I have it on the top so if you take a look at my my formula bar on the top I have a formula bar and I have not the formula bar it's actually the quick access toolbar and I've customized my quick access toolbar and if you wondering that you know new versions of power bi have come and they have changed the layout completely altogether I have purposely not updated my actually I have abated by a but I have reverted back to the old look and feel of Bobby I only to kind of stick around with the quick access toolbar that I've customized it for myself because I feel very very comfortable and very efficient using this quick access toolbar that I have created for myself why don't you take a look so the way I can access my quick access toolbar is something like this let's say if I click on the table and I'd like to create let's just go here if I click on the table I'd like to quickly create a column I don't have to use the mouse to right click and then say new column what I can do is I can select anywhere on the table and I can press alt one because on the one I have actually put up a new column and a new column is created and I can just start typing the formula and I'm just good to go the second thing the second most common thing that I do is create a measure I mean that's the most common thing that I do and on all - I have customized that as a new measure when I am creating visuals and let's say if I create a card visual let's just create a blank card visuals so I'm just going to make a card visual and you know what maybe I'd like to align these two again you know kind of on the top so generally you will have to go to the format tab hit the align and then say a line talk but I don't do it that way I use all three and then I can just use in the keyboard press kind of top and they kind of align it to the top so I find it very very comfortable to use these keys so what I've done is I place them right here just about 7/8 of them the things that I use the most and in the new power bi they still do not have the ability to customize the quick access toolbar as of as of the most recent update I haven't found that so I have still not kind of change the look and feel to the new new looks I mean that's the way it is so I feel really comfortable with these and if you would like to use these these will actually improve you improve the productivity of your reports and you can quickly create measures quickly format so the rest of the the tools that I've kept here is format painter and in the modeling tab you have the data type declaration this is another one the decimal place is the comma separator so these are a few things that I have done to put it so what you can do is that say for example if you want to make a new table and that's something that you would like to add it to it to your quick access toolbar so you can right click and you can see just add it to the quick handle bar it just goes up right on the top now this shortcut becomes alt 8 because or 9 actually because you've added on the 9th position so I just wanna don't add it I just want to remove it so that's what you can do alright those those were you know kind of my tips to you know share the DAX things with you now I personally want to kind of talk about my Dax journey that how I I began to learn Dax I remember that I started learning that's in about in the year of 2015 and at the start it was really really difficult I think I picked up a book from Rob Kohli and then I started learning Dax I mean I was able to understand what he was doing in the book but it was really hard to kind of get around to making that calculations make on your work on your own date it was really really hard it was so hard that I quit learning the hacks in 2015 I closed the book because that was the only good resource around and I closed it and I just put it off and until the next year 2016 when I actually started Meena kind of seriously thinking about learning Dax and I started to put a lot of effort in learning packs so yeah and since 2016 I've read a lot of books on Dax and from someplace I've got some resources and from someplace I've got some resource and using a lot of trial and error I've kind of built some you know reasonably good understanding of tax and of course I've done a lot of consulting work for companies as well to you know do some sophisticated projects where I've coded a bit a lot of tax so yeah those things helped me but a lot of lot of trial and error so what I've actually done is that oh wait I have produced a course on tax it's a very logical course it actually has three parts the first part is of the courses fundamentals I'm just going to teach you some basic concepts of tax things that you essentially need to know in order to get started with tax like filter context like thumb rule of Nags and how the context transition happens what exactly our calendar tables and things like that those are the fundamentals not number two is practical case studies of Dax so learning fundamentals is not enough so you actually have to also apply Dax to real time scenarios so that is another part so I have discussed five or six cases there practical cases where you can actually use Dax in real time scenarios and the final part is that you have to kind of expand your library of Dax function Dax is made up of these formulas it's data analysis expression these formulas are tax language and the more formulas you you know the better you going to be so I've collected some formulas there those are the essential formulas that you need to know first and then you can expand and knowing more and more formulas so that is the tax library you can just go there pick up a formula learn how it works and keep on expanding your library and this course is live as of now so you can take a look at the link of this course in the comments and if you definitely were one of them who contributed to the Cova 19 cause thank you thank you so much for that and you can just write an email to me and I'm just going to give you a coupon that is going to give you a 50% discount right off the bat of the course so you can just enjoy the course and yeah there's another coupon in case anybody would like to use this this twenty percent off coupon as of now which i think is going to last for another five or six days so if you register before that you can actually get a twenty percent off so in the comments not in the comments actually underneath the video if you just take a look there are two links there one is of the tax course and the other one is of the training outline and the coupon code is also mentioned there so you can actually use those two things if you actually haven't enrolled in the excel course of excel experts where the entire proceeds of the course is going to pallas you know the initiative that she has taken to give it to co-ed patients I highly recommend that you do that it's actually a good cause the additional benefit is that you you get 50 percent of my course but that's not the reason that you should do it you should actually do it for to help people and maybe just learn Excel so feel free I mean those are the two ways to kind of do it and be more than happy to help you out I'm very happy to kind of address any questions if you have any so please let me know if you have any questions I'd be more than happy to address the questions that you have so let's just open up the chat box all right so let's see a lot of questions here my god so emojis work in Excel and power paper the answer is surprisingly yes they do work in Excel as well and I believe they do work in PowerPivot as well so you can take a look the only hitch that I have found is that you may not have the color like the way the way we had the color captured from the web and the the cross was red and the tick was green you may not have that color but you can use conditional formatting to color it up again so but you can have those icons so in actually not just Excel power pivot but you can also have it in the regular Excel also while writing in a formula so you can have it in the inverted commas if that answers your question y2d versus current week versus y50 calculation all right so in Dax you can have the formula called previous month which actually calculates the previous month value can just watch the video again we actually have done the growth over previous month calculation and it is a formula called MTD which is going to do the totals for month to date there is a formula called QT and my ID for QT and mightily calculations but we don't have a formula for current week valve it and give it back to you but there is no direct formula of you know current week over previous week I mean you don't have a ready-made tax code for that but yeah I mean you can do that using some date calculations you can I think use date which dates between or things like that but if you could just send me or your data I can precisely make the tax code and send it back to you which is you're not able to find the tax feel link it's on the course page so if you just take a look at the course page it's it's right there so if you just scroll down to the bottom of the page it's right there any other questions people are ok how to fill the ear number hold ok another good question from Krishna is that how to fill the ear number in all the rows in the visualization so I'd like to take that and maybe I'd like to show it to you how do you do that so if I kind of go back to the visual here so that's what he's asking he says how do I actually take this 2011 and fill it across all the rows I don't think that's possible in a matrix visualization so as of now I'm working with matrix but if you actually convert the matrix into a table format this is going to work so you would have 2011 throughout all the data so if that answers your question ok any other questions please yes Ajit I just answered that question so if you can just maybe Devine and take a look how do you actually fill the ear in all the rows aziz asks could you explain the DAX formula to compare the values of the current fiscal with the previous year fiscal it's actually pretty pretty easy so let's just take a look at that so let's just say that if I convert it back to a matrix here you can see that can you see my screen okay so if you just see that we have maybe I'd like to compare January of 2011 with let's say January of 2012 right so that's what a same period of the last year so the way that I'm gonna do is I'm gonna come and maybe write another measure and I'm gonna write maybe sales last year sales last year and I'm gonna say hey why don't you calculate oops calculate total sales and but the total sales is going to be in the context of the same period last year so that's the formula that you can use for this you need to have a date table and you need to have the date column in that and then close the bracket press ENTER now you can see that if I drag this measure into my pivot table what I am going to get is a particular column and you can see against three four five five I'm getting to see the sales of the last year now one one nine nine was the sales of January of 2011 now I can pick up these two values so I can pick up this value and I can pick up this value divide them minus one and here I can do the growth calculation like I have done it for growth over last month so the only you know change that I have done in the previous formula is that instead of using the previous month here I have used the same period last year all right I hope that answers your question anyways I'm gonna give you this power bi file that you're seeing here so you can actually work with this power bi file so I'm just gonna save that for now all right any other questions um can you suggest any other books other than the definitive guide to Dax I think definite of guided access is a great book but it's definitely not made for beginners that's what I've personally found and although I purchased this book both the versions of it long ago the first version was purchased by me long ago but then I didn't really get around reading that book for a long time unless I understood the fundamentals of Dax works so to be able to start with Dax I will highly recommend a book from Matt Ellington and he's written a book on - so it's a really really good book and you should maybe pick up that one to kind of begin with Dax and I think a book from another book from maybe Mark Caruso and Alberto Ferrari is data models in PowerPivot that's also a good book and you can actually read that that's slightly more advanced and so I suggest that you read up that and also books from I actually really enjoy books from this author called what's his name his name is what's his name actually my library is right there I can just go take a peek but Phil SEMA actually his name is Phil see mark I loved his books actually he writes very clearly his books are slightly more advanced but his books are pretty good very very good examples I saw some complicated problems using Dax so that's a good another good book but - if you wanna ask me hey which book should I begin with a highly recommend that you begin with Matt Ellington's book on Dax do I have any book or book no you'll have to pay it all right so what I'm going to do is I'm going to save this power bi file that I have worked with throughout the session and you can actually download that from the same place where you downloaded the data and you can actually work with this power bi file and if you would like to learn tax from me please feel free to just take a look at the course that I have created for you it's a really really good course I have kind of divided Doc's into three major parts the fundamentals the tax library and practical cases and you can take a look at that it's going to be interesting and there is a little discount as well especially for those people who have enrolled in The Cove in 19 case you can still enroll in that case in that course and get a 50% off if you don't want to do that you can actually get a 20% off by using the coupon code and all of the links the course outline the link to the course and everything is there in the in the in the description of this video so you can just watch through that alright is there a last question okay Kishen is there a tax measure to color the font I think there is so what you can do is you can use hex codes to kind of do color of the font in the color but so there are there are there are several ways you can do that but I will highly recommend that you do that using conditional formatting and in conditional formatting you can start using tax codes so this is where it comes up maybe I can just show it to you so let's just say that let's just say that I'd like to color the negatives as red here right in my in my pivot table and I am going to go over the where is that growth over last month and I'm gonna come here and I'm gonna say conditional formatting and I'm going to go to font color and that's what I'm going to say so I'm just going to say that it's the color scale so I'm gonna say rules so growth over last month is the calculation if the value is let's say greater than zero actually say greater than zero number and is let's say less than you can just write a very big number here number again then I would like to color it as blue and I'd like to add a new rule if the value is let's say let's just say if the value is greater than equal to let's say minus 100 and less than zero then I'd like to call it as let's say red right so that's the thing at the greater than zero and that means it's a positive and less than 100 that's a very large number then I'd like to call it red blue that means you have done some good sales and that's blue and if it's less than minus 100 s again a very large number on the negative side and less than zero then I'd like to call a test right let's see if that works or not and it works so that's that's the way you can do it I don't know if you if I answered your question right or not but if you're asking me that using Dax code can be colored yes I believe there is a way to do that maybe if you can just share the exact case with me I can just take a look at that and give you the code alright that's about it oh thanks Christian if you delete the very big number you will get the maximum body force that's a learning for me I didn't know that so actually I was always doing a very large number always because I never thought of leaving that as blank but thanks so much thanks guys it's been a pleasure doing this webinar with you and hopefully I'll organize more webinars I think I'm just thinking of another webinar on maybe some visualization techniques if you have taken a look at my dashboards maybe I can share some techniques that I personally use in my dashboards to make them swanky and look really good and also display the information and a really really good manner so I'm just thinking of that let me know if you what do you think about it and I'd be happy to do another webinar all right thanks for being along check out the course of Excel experts which are which is going to help some COBIT patients around the world check out the course and acts that I have created and let me know if you have any questions I'd be more than happy alright thanks so much I'll see you again sometime soon bye and how do I turn this off

Channel: Goodly

Views: 6,107

Rating: 4.9620852 out of 5

Keywords:

Id: l4NQoTB0Qok

Length: 73min 25sec (4405 seconds)

Published: Sat May 09 2020