Compare Two Tables Using DAX | Power BI Case Study

Dax is all about working with tables in this video I'm going to talk about a complex tax problem and then how do you think of tax in terms of table validate the results and also use the query Editor to take a look at what's going on under the hood so that you understand Dax a lot lot better you're going to have a lot of fun and we're going to write a lot of Dax let's get started all right let's just get started with the data and the that I'm talking about here so I have a very simple data here the First Column transaction date ID which is transaction ID user email user ID the version of the product and the product name itself now this particular data is let's say from a SAS company that is billing every single month to a customer for the product that they have purchased now let's just take a look at one single customer and you'll understand it better so if I just go down maybe filter for and take a look at all the values that I have you're going to see that has has been build for a particular product for all of these months November 19 December 19 and then January 20 February March April May June July and the billing is obviously done monthly for that particular product and this is where I can take a look at what was the version of the product what was the name of the product so on and so forth a very simple data to understand from this data the problem that I'm trying to solve is this how many customers made an upgrade from the basic version of the product to the professional version of the product from the last month so let's just take a look at the current customer that we have selected so if you take a look at this particular guy this guy in the month of June 2020 he was on this particular product which is producy and he was activating the basic version of the product in the next month which is July 20 he moved on to the professional version of the product which is clearly an upgrade so from the base version to the professional version of the same product that is why I'm going to count this particular thing as an upgrade of the same product now we have to find out that how many such upgrades are happening month on Monon where you take a look at the current month and the previous month of the same product and see how many people shifted upwards all right before we proceed let's just try to create some logic that is going to help us understand that how are we going to solve this problem in parbi and what Dax are we going to write like I said at the start of the video Dax is all about creating tables so we are going to make a few tables that is at least going to give us the visual understanding of at least coming up with the logic so let's just start I'm in Excel and obviously I have made two simple pivot tables right here the first pivot table uh uses the user email or the user ID column the version of the product and the name of the product right here and this table is filtered to the current month assuming August is the current month so August 2019 is the filter that I have applied I'm just trying to take a look at for 1 month month at the moment all right there is another filter that I have applied which is the version should not be any version since I'm only taking a look at upgrades so in the first table I have applied a filter and I'm only keeping the base version of the products right here in the next table the next month filter is applied so if I'm taking a look at the month of August right now I then move the month ahead and I take a look at the month of September again the same three columns user email or user ID the version of the product and the product itself note here the version needs to be the professional version of the product but once I have been able to create these two tables then it's just about finding out that which emails or which users are common between the two tables and those are the users that have upgraded so let's just start I'm going to write a very simple match function to be able to find that out so match match what match this user email match it in this particular column of the next month and then give me an exact match close the bracket and press enter drag this down now you can see that the first particular user right here uh shows us that this is row number five so this is row number one row number two row number three row number four and row number five this user was uh buying supernotes basic version of the product in the next month September 2019 they are again buying the same product it has to be the same product they're buying supernotes and they are on the professional version of the product absolutely valid upgrade that's upgrade number one take a look at the next one which is RO number 11 so which is right here uh and I can see that which is the last customer right here they were on a product called producy and that is also the same product produc ify they are on the professional version in the previous month they are on the base version now I have been able to find it out at least manually that there have been two upgrades if I were to create these two tables which is this table and this table in powerbi and if I were to compare that which are the common items between the two tables I should then be able to find how many upgrades happen it's all about creating these two tables now if you take a look at the tables we have user email version and the product and that's exactly the table that I have to create for the current month and for the next month in parbi let's just jump over to parbi and start creating these tables and try to solve this problem all right I'm in Barbi I have loaded the data and also made a calendar table that I have linked with my data using the date column right here a standard one to many relationship all right now let's just hop on to the B that I have made which is where from the calendar table I have used the month and the year column to draw up this Visual and that's what we have against every single month I'd like to be able to calculate how many upgrades happened and to be able to get the same logic like we had it in Excel I would start to create tables right so I'm just going to go ahead and create a new measure let's just give this measure a name called upgrade and I'm going to start to create a table so the function that I'm going to use to create a table is nothing but the summarized function and I'm going to say hey I'm trying to summarize the data which is my table name and there are two columns that I would want First Column is going to be the name of the user or the user identification which is nothing but my user ID and the second column is the name of the product as to what product has the customer purchased so which is going to be the product right here now if you remember that there were two filters oops I lost the measure I'm going to write it again now if you recall there were two filters that we had applied to this particular table filter number one that this particular table needs to be only for the base version of the product that was filter number one and the base version of the product needs to be checked in the previous month and we going to apply these two filters to this table as well so the first filter is going to be I'm going to apply filters using the calculate table function so I'm saying hey why don't you calculate this particular table and in this table the first thing to calculate is hey why don't you take this table to the previous month and that is my calendar date and then the second filter is going to be let's say the version of the product needs to be the base version all right once we've been able to make this table why don't we uh give this particular table a variable and try to validate are we getting the right answer or not so I'm going to say v r this is the base last month that's my base version of the product last month and as an output I will return the number of rows that exist in this table so return the count rows function and the base l M right you can't really return the entire table so I'm just returning like an aggregation of the table let's just drag this formula onto our pivot table and let's just take a look at the result now if you take a look at the month of August 2019 I am actually taking a look at the previous month let's just take a look at September so if I'm taking a look at September 2019 this result is nothing but the number of rows according to the filters in the previous month which is nothing but August if I go back to my Excel and if if I take a look at hey in the month of August how many rows were there there were 1 2 3 4 and five that were on the base version of the product that is absolutely right so far so good let's just proceed ahead and create another table which is going to be for the current month but it's only going to take a look at the professional version of the product so in the same measure I will start to write one more variable so v r I'm going to say this is my professional current month and that is going to literally make the entire calculation right here so I'm just going to use the same thing once again so this is my logic and this logic is going to have two filters the first filter actually just one filter so I'm going to say calculate table and that's the table and the filter that I would like to apply is the version of the product needs to be professional all right once we've done that let's just validate the result for this I'm just going to say count rows of not the base LM but the pro current month table press enter let's just see what do we get get as an output so I am again taking a look at the month of September right here now this is for the month of September for all the people who were at the professional version let's just take a look at the Excel going on the Excel I can see that how many rows are there there are clearly 11 rows of data that we have and that is correct once we've been able to create these two tables which is for the previous month and for the current month the next thing to do is that I want to be able to compare that are there any customers common between the two table the one who were there in the base version of the product in the previous month and the ones who are on the professional version of the same product in the current month are there any common customers if yes then I would like to take account of those customers and that exactly is the behavior of the intersect function so take a look so I'm going to say that hey I've made these two tables the first table and the second table both of these tables have two columns the First Column is nothing but user ID and the second column is going to be for the name name of the product so this is the product and I just want to compare that how many rows the entire row that means the user and the product is also found here as well how many rows are common between the two tables that I have so I'm going to write a function called the intersect function and in the intersect function I'm just going to say hey my first table is the base last month and my current Table is the pro current month now the intersect function is going to take a look at all the rows and if the rows are mat it's going to deliver a table again but hey I don't really want to have the table I just want to count the number of rows of the table I'm just going to say count rows close the bracket press enter and let's just take a look at the result the result is going to be let's say for the month of September it is two customers found who moved from the base version to the professional version and that is also the answer that we validated in Excel all right if you take a look at my visual that we have although we have the answers for for every single month right here we can see that how many upgrades happened for every single month but guess what we don't have the totals that means the user who's trying to take a look at it if he's not seeing the data by the month and by the year he would not have this answer populated a card visual is going to be blind now what I'd like to be able to do is I want you to take you under the hood and explore what the calculation is doing in the query view of Dax now if you haven't had the query view as of yet I suggest that you download the latest Barbi get the query view in the preview settings of your Barbi and work with me so for the moment I'm going to go over to the query view right here and start to create my query now what exactly is a query I would not use the jargon but query is nothing but a table in powerbi or using Dax using Dax you don't create a measure but instead you create a table of data table simply means rows and columns and that's what we have so what we going to do is we're going to take this particular measure to be able to debug I'll right click on the measure and I say that hey why don't you define this measure and evaluate this measure so right click Define and evaluate and this gets defined right here obviously this kind of gives you a blank here that means the answer is not found and that's exactly the answer that we were getting it in the visualization as well so what do we do about it first of all I'm just going to maybe simplify this measure a bit so what I'm going to do is I am going to delete parts of this and just going to evaluate my table so let's just say that I go ahead and clear up all of this I clear up all of this actually return is fine the first and the second variable is fine let's just delete the Define as well on the top and let's just delete the this as well up on the top and I am going to just write evaluate up on the top which is the keyword which is needed to evaluate the table so that's my entire table so um that's my first table that's my second table and in the return I'm just counting the number of rows of the table but I don't want to count the number of rows of the table I just want to have the table itself so base last month if I just run this this gives me a blank now that means that this table when it is outside of the filter context of the month that means you had months right here the first month the second month the third month when it is running against the total this table actually shows up blank right this table let's just take a look at the second table so Pro current month copy that and return that as an output let's just see what do we get I click on r and we do get an answer that means the first table is giving us a blank but the second table is actually returning a value the only problem is that one table is blank the other table is filled up with values you can't really have matching rows between the two tables because the tables are blank so the answer obviously is a blank now the thing is that in order for us to get the totals right what we need to do is we need to be able to create a table like this which is nothing but the year and the month table against every single row I want to do the calculation once I do the calculation the final calculation which is without any filter context should take a look at all of these rows right here and then add them up that is what I want to do now I have done another video on trying to find out the reasons of incorrect totals in parbi and if you don't know and if you struggle with incorrect totals in parbi you should take a look at that but for now for now let's just go debug this particular problem all right so the first thing that we would want to have is this particular output which is where the First Column is going to be nothing but the month and the year so I'm just going to go ahead and start to create my query view I'm just going to say hey um evaluate and I would like to have the values being called off so values of the calendar month year month year and uh close the bracket and let's just run this particular code and that is what we get which is nothing but the First Column of the data now against every single row right here here and here and here I want to calculate the upgrade now and then once I have calculated the upgrade I would like to sum all of these values up so let's just create a new column in this so I'm just going to say hey add columns to this particular table call this column as an upgrade upgrade I'm going to use the same logic to be able to calculate the upgrade which I'm actually going to take it from my upgrades calculation let's just go right here and take a look at our upgrade calculation so I'm going to copy all of this contrl C on that come back right here in the query View and in the upgrade calculation I will paste my output right here close the bracket and let's just see do we have the correct output or not now this upgrade is going to add a column and it's going to calculate the numbers right here click on run and what we get is nothing but an upgrade for every single month that is the table that I would like to be able to use use in my calculations so I'm just going to maybe copy this entire table crl C and I'm going to come ahead and up you know recheck my Dax or just edit my Dax I'm just going to go right here and I'm going to say hey why don't you delete all of this stuff and use this table instead now in this table there are two columns the First Column is the month and the second column is obviously the um upgrade so I'm going to say hey please step inside every single row of this table in every single row of this table I have created a column called upgrade please take a total of that simple as that press enter commit to the for formula close this and at the bottom now we get to see the total ins so using the Dax query view you can take a look at the tables that you're creating partly evaluate those tables get to the answer and then use the tables to correct your tax all right that's been it let me know if you have any questions on this one obviously we tried to cover possibilities of working with tables and trying to evaluate those tables partially in Dax and the query view let me know if this video was helpful and if you'd like to learn Dax like from scratch or learn the M language or power query right from scratch build up your fundamentals and then move on to a very sophisticated level so that you start solving your own problems please do not hesitate to check out my courses those are also going to be super awesome thanks so much for sticking all around and of course I'm going to catch you guys in the next one [Applause] bye [Applause] I

Channel: Goodly

Views: 10,629

Keywords: Power BI, Power Query, Excel, DAX, M Formulas, chandeep chhabra, power bi goodly, how to learn dax, dax tutorial power bi, how to compare tables in power bi, compare tables in power bi using dax

Id: KrK25zYZGTs

Length: 17min 35sec (1055 seconds)

Published: Wed Jan 17 2024