PL-300 – Section 31: Part 3 Level 9 – Other Modeling and DAX Topics for the Exam

235. Create Calculated Tables

Hello, and welcome to this last level of part three, and in this level, we’re going to have a look at all of the items from the DA-100 certification, that haven’t fitted neatly into the other levels.

Now, in, a previous, level in part two, we had a look at the drive safely text files, and I’m just going to use this text file as an example of how we can create a Calculated Table. So, here we have the data, I’m just going to put this into transform data and I’m going to now add a second one, the DriveSafely 2015. So, what we did in part two was combine these two together.

So, we went to combine and append. When the append is new, we did some merging as well, previously as well. And that gives us an overall table, however, what if you don’t have this at the end of the get and transform. For example, maybe, you’ve got some data set from one source and data set from another source, and for some reason, you haven’t been able to combine it, not generally the case, but it’s possible. And what I’m going to show you is how to do this in DAX using what’s in my spreadsheet are called other functions. And these can be used with more complicated DAX formula. So, we have in our model, these two separate tables, and we want to union them together. We want to join them together so that we have all of the roles from DrivingSafely 2015, and all of the roles from DrivingSafely 2016.

Now, we can do this fairly easily, by using what’s called a Calculated Table. So, if you’re going into the Modelling Section, we see New measure, we’ve had to look at that earlier in this part, Quick measure, we’ve had to look at that in part one, but if you remember all of the Quick measures, allow us to get various calculations, very quickly, now hopefully you will know a fair bit, about what the outputs would be in DAX. We have New column, we’ve had to look at Calculated columns, and now you’ve got a New table.

Now, let’s have a table just called DrivingSafelyCombined. And that I’m just going to have a copy, of DrivingSafely 2015. So, there it is, click the check, and that is exactly the same data. So, if you go into DrivingSafely, 2015, we’ve got 51 rows. If you go into DrivingSafelyCombined, we’ve got 51 rows.

Now, let’s put a UNION around this. So, we have a UNION, open bracket and they have the first table and name of the second table. So, now in our DrivingSafeLyCombined, we have 102 rows. So, what we could probably then do, is to hide in the report view, the other tables, and then just say, we have this one table.

So, let’s have a look, at some of the other functions you can use. First of all, there is the NATURALINNER join. So, this is the equivalent of the merge that we saw in part two, you have column, you have table A, you have table B. They’re United by column C. So, once you get at the end is table A and all of table B, except for column C, which is being used to connect them together. It is called a Natural Join. Natural is terminology taken from Oracle SQL, in which you allow the computer to look for two columns, or, maybe, more than two columns, which are identically named. So, you can see the tables are joined on common columns by names. The name has to be exact, NATURALLEFTOUTERJOIN. That gives all everything from a table A, and only those rows in table B that match. So, you’re guaranteed everything from table A, whereas the NATURALINNERJOIN only gets those table A and table B match. Intersect, this is where both tables, match in terms of the rows. So, this is the equivalent of a union, but only full duplicated data, EXCEPT is the equivalent of a union, except it will only show those items which are in table A, but not in table B.

So, we’ve had a look at UNION, VAR, that can be used to store the result of an intermediate status available that you can then use later. You’ll see this quite used quite often in quick measures for instance. And then you’ve got some other things like, is this table empty? So, and, of course, you could do an intersect and then find out well is that intersect empty? GROUPBY that’s is similar to summarise. We’ve had to look at grouping in part two of this calls. So, for instance, supports have cost a million rows. And I wanted to say, what is the total number of mileage per state. That would give me a table say 50 rows one for each state, with the mileage.

So, creating Calculated Tables. You do this in Modelling or in Table twos, if you’ve got it available. And you go to new table and you say this is the table, and it is equal to either an existing table, so you can do a duplication and then do things to that table. Or you can manipulate more than one table, with UNIONS or other set operators or with Joins.

236. Create a Common Date Table

Hello, in this video, what we’re going to do, is have a look at creating a common date table. So, why do we need a common date table? Well, let’s have a look at some model.

We have got this due date, and I’m just going to create a table visualisation which has the hierarchy in it together with the actual due date. So, you can see that we have got the 13th of July, 2005 being the 13th day of the seventh month of quarter three of 2005. So, the seventh month of the year and it’s in quarter three. But what if we didn’t want earlier to start on January the first? Maybe, we’ve got a business reason that we wanted it to start in April, or, maybe, we’re going to school or college and we wanted to start in August or September. Well, this is when this auto-generated date table is not going to be very helpful to us.

Now, we can deselect it, you can switch it off by going to file option to options and in the current file data load dechecking the auto date time. Notice what’s happens to the hierarchy when I do that? It completely disappears. So, we have to recreate a common date table. Now, there’s several ways to do this but I’m going to do this in DAX, and I’m going to create a new table.

Now, we know how to create a new table. So, this is my date table and it is going to be a calendar function from the first of the first 2000 to the end of 2030. So, here we have our new table, so let’s just have a quick look at it and you will see it’s every single date. So, what I want to do now is have this as a date table.

Now, first of all, I need to ensure that this is a date. So, let’s go into column tools and you can see, yes, it has been selected as a date time. So, with that in mind, I’m going to now put this as my date table. So, I’m going to go to table tools and I’m going to mark as a date table. I could also right and click on it and go mark as date table as well. So, select a column to be used for the date. The column must be of the date data type and contain only unique values. So, there is my date column.

Now, interestingly I tried this before using our any function way of creating lots of dates and it didn’t like it. So, I’m using the DAX way. So, what we would now need to do from here is create additional columns. So, we need for instance, a year, a month, a day. So, we need to do all of this housekeeping ourselves.

So, let’s go into modelling, let’s add a new column but I want to make sure it’s in this table. So, actually, I’ll pick on the dot and dot, and go to new column here. So, I’m going to say my business month equals the month of date. So, I’ll see if that works. Nope, so I need to put date in brackets. There we go without word data afterwards. So that’s fine so far except is given me exactly the same results as before. So, I’m going to say, if the month is less than four, then, Well, if it’s greater than four, then I want three deducted. So that’s the fourth month is actually the first month. Otherwise, I want three deducted and I want 12 added. So, let’s see what that looks like. So, we now have January being the 10th month which makes sense. April through to January is 10 months inclusive. And then when we get to April, April is the first month, so March is the last month. Right, I need to create a year as well.

So, I’m going to now create another column. Again, I just want to show that it’s in date table what it was going to be? And so this business year, is going to again, rely on the month. If it’s earlier than April, then it gave me the year minus one, otherwise it just gives me the year. And then I want other things like a business day. So, let’s do this as well, but this one’s fairly straightforward because the business day is not going to change. So, we have it the day of date. So, when you scroll down and get date. And I also want key as well. So, a new column, again, making sure it’s in the right table. So, this is our date key, quite often common date tables have a date key and that is the format of the year two for numbers. Just to make sure that works. No, that’s not working properly as it’s go. It’s taking the business year. I just want the data actually. I want the year of the date. There we go, and add to that the month to two places. So, we have 2001 and then add to that the day. So, you can see all the other things that we can do, with date tables amount to 20 columns for the name in English, the name in French, name in Spanish. So, we have month names, easily retrievable. However, you choose to do it. Make sure you have a list of consecutive dates with no duplications. And then just right and click and go to mark as date table, or go to table tools, mark as date table, and then select which table and column it is, and you’ve got your new common date table.

237. Define Role-Playing Dimensions

In this video, we’re going to look at what role playing dimensions are.

So, dimension, first of all, it’s a table, it doesn’t contain facts, it contains descriptions. So, dimensions of products are things about products, description, so you can see we have descriptions, we have class, we have colour. Dimension product subcategory are things about the subcategory, the name. Fact internet sales, these are your base sales. So, we sold this product, which happens to be in this category, in the subcategory or this date and so forth. And we have got a due date key. I am going to link that to the date table using the date key, so there we go.

So, what I’m now able to do is create a bar chart or any other type of chart. And we have the total amount of sales by month, for instance. So, we can see if there’s any seasonality. So, month number one that happens to be April, month number two May and so forth. So, we can see a bit of seasonality. We are getting disappointing sales during the summer.

Now, that is based on the due date, but we’ve got more than just that as a date. We’ve also got the order date key. So, I’m going to connect the order date key to the date key here. And well, it seems to work, but we’ve got this dotted line as opposed to a solid line, what’s going on here.

Let’s have a look at the properties. And we can see, that we have got make this relationship active unchecked. That’s what the dotted line means as opposed to make this active. So, what’s this all about? Well, in tax you can only have one link between a pair of tables. So, I’ve got a link between due date key and date key. I can’t have a second one between order date key and date key. Could I have a relationship between order date and date, because that’s not using either the columns. And again, it’s just marking it as an inactive relationship. So, it’s not between passive columns. It’s between pairs of tables. So, what can we do? So, this is an analysis based on the due date key, but let’s say I wanted one based on the order date key. Well, first thing we could do is make this one active.

So, let’s go into properties and check, make this active. I’ve got to make the other one inactive first. So, this is being used as a role playing dimension, it is playing a role. The role is the connection between due date key and date key. There is another role which is between order date key and date key. Each of these are roles. And when there’s more than one that potential role, where there’s more than one relationship. Where they both can’t be active that is called a role playing model. Right, so what is it that we can do? Well, let’s go back to fact internet sales. We can add a new column. So, this column is going to be the business month based on the order date. And I can use a function called use relationship. So, this allows me to use a different relationship. So, the relationship I want to use is between the order date key, and the date key. I’m going to actually get the total sales. So, let’s call this sales amount based on order date. And I use calculate because we now have a new filter and I’m summing up the sales amount based on this new filter.

Now, I want this to actually be in our date table. So, I see, I’ve got this in the wrong table. So, if I can remove it, it’s not the one. Yep, so I’ll delete that. And I’m going to now add it into our date table. So, a new measure. So, this is sales amount based on the order date. Computer thinking about it I think; okay it’s being added. And now create another visualisation showing the business month and the sales amount. So, you can see some slight differences. So, this is one way around it, sales amount or the date. So using the use relationship. It’s a bit messy, not particularly keen on it, but it is one way.

Now, another way of getting around this is to actually have a separate table. So, instead of having one date table, we have two. And how do we have two, we create a new table. So, this is exactly what power BI does when its put in date table. If it needs multiple tables it was simply create them in its memory. So, here we have a date two, and that’s equal to our date table. So, maybe, we call this dates, date table 2. So, now we have a second date table, and we can then get rid of this relationship. And we add in a second relationship with a second table. So, there we go, so now no role playing required. We actually now have two separate dimensions.

So, how would you define a role playing dimension? Which is exactly what the DA-100 exam asks you for. A role playing dimension is a dimension, so a table that can slice that can filter on another table in more than one way. So, here we could filter on the due date or on the order date, or on the ship date. So, a role playing dimension is one that can take on many different roles.

238. Resolve Many-To-Many Relationships – Joint Bank Accounts

Now, up till now, we’ve been looking at connections between tables using a one-to-many relationship. So, for instance, we have the date table, which we know is going to be unique in that the date key is going to be unique for every single row. Whereas the connecting table Fact Internet Sales we have a due date key, but this may be duplicated. There might be more than one item being sold on the same date. In fact, if you run a business, you hope so.

However, what if you don’t have a many-to-one or a one-to-many relationship? Well, you could have a one-to-one this means that it’s unique in both tables. This doesn’t often happen because if you have got something which is unique in both tables, you could just merge the two tables together. You would have the same number of rows by the end reasons you might not do that is if there’s security involved, for example, one particular table, you need a bit higher security. It could be that there’s people’s salaries, which you don’t want disclosed on another table, which is more available to everyone.

But what about this other cardinality, many-to-many? What we are going to do is create a couple of scenarios? So, we’re going to create a couple of specific models, just to show a many-to-many relationship. So, if we go into our query editor and we load from our Excel spreadsheet, the Power BI data, we’ll see that there were four spreadsheets in this workbook, which are prefixed M to M many-to-many. So going to have a look at people and transactions. So, I’ll just click, okay. And they can load in here. What often happens is that people. So, we have Amy, Brian and Chloe. They have bank accounts and some may have joint bank accounts. Amy and Brian, have got account number one, Chloe’s got account number two.

However, it’s very rare for people to just have one bank account they might have a savings account, for instance. So, Amy and Brian, have got bank account three as well. So, two people have got two bank accounts. So, this is a many part of the cardinality.

Let’s have a look at the transaction and here we can see that each bank account, there are two transactions. So, again, this side of the cardinality is many. So, we have a many-to-many relationships, so let’s close and apply. So, we have these two completely unrelated tables at the moment.

Now, that could be related with the bank account field, but let’s just find out what’s happens if we don’t relate them. So, we have an owner and we have the amount of money in total. So, this is the sum.

Let’s make sure it says so. And you can see that the computer goals. Okay. I haven’t a clue what the relationship is. I’m not even going to try and guess. So, I’m going to give everybody the maximum money. So, we have got 2100 in the totality of the accounts. So, we’ll give everybody access to that 2100. And we’ll say the total is 2100. You notice the total is not 6,300. It’s not added them up that way. It’s just said that the total is 2100 and each has access to 2,100 things. Well, what we can now do is have the relationship between bank account and bank account in the two tables and you’ll notice what’s happens. We’ve checked automatically make this table active. That has to be done. Otherwise, the relationship won’t apply, we’ve got many-to-many in the cardinality. So, the computer has checked whether this column is unique in either table, and then the cross filter direction is both. So, it’s not just one single direction. It is both ways. And you can see a warning at the bottom that neither column contains unique values. And there may be some differences in the relationship of many-to-many relationships. And there’s a hyperlink there. If you want to find out more, one of the big changes that you can’t make, you can’t use the related function because related from either side, won’t bring back a single row. It may bring back multiple rows. So, the relative function isn’t allowed. So, now we have got this two-way cross filter. We have got many-to-many. Let’s see what’s happens with our analysis. And now you can see the analysis works just fine. Amy and Brian both have access to 1,400 dollars Chloe has access to her 700 dollars. So notice the totality still adds up to the totality. It does not add up to these total what is being shown for Amy and Brian and Chloe.

Now, what’s happens if we change this from being both ways to going from people to transaction? So, the people filters the transactions and you can see the answer is absolutely nothing. That’s because we have got the thing that we are trying to measure being at the end, the filter. So, we have an owner and we have to filter down the transactions to get to that particular answer. If it was the other way around. So, we have transaction filters people then would just have the mess that we started off with. So, you can probably see why it’s best to have it on both because the computer doesn’t know which way you are going from. Is this the principle direction going that way? Or is this the principal direction? If you have just got one direction, then by all means change this cross filter. So, it goes in the right way. In the next video, we’ll have look at another many-to-many relationship that won’t be quite as easy to resolve.

• Category: Uncategorized