DA-100 Microsoft Power BI – Part 3 Level 8 – Other Modeling and DAX Topics for the DA-1

1. 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 it into transform data and I’m going to now add a second one, the Drive Safely 2015. So what we did in part two was combine these two together. So we went to combine and append when the pen 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 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 Driving Safely 2015 and all of the roles from Driving Safely 2016. Now, we can do this fairly easily by using what’s called a calculated table. So if we go into the modeling 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. And now hopefully you will know a fair bit about what the output would be. In Dags, we have new column, we’ve had a look at calculated columns and now we’ve got new table. Now, let’s have a table just called driving safely. Combined. And that I’m just going to have a copy of Driving Safely 2015. So there it is, click the check and there it is, exactly the same data. So if we go into Driving Safely 2015, we got 51 rows. If we’re going to Driving Safely Combined, we got 51 rows. Now let’s put a union around this. So we have a union open bracket and name the first table and name of the second table.

So now in our Driving Safely Combined, 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, those the natural inner join. So this is the equivalent of the merge that we saw in part two. You have table A, you have table B, they are united by column C. So what 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 name. So the name has to be exact natural left outer join that gives everything from table A and only those rows in table B that match. So you guaranteed everything from table A whereas the natural inner join only gets those where 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 for 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 in other words you could do an intersect and then find out well is that intersect empty group by that is similar to summarize, we’ve had a look at grouping in part two of this course. So for instance supports have got a million rows and I wanted to say what is the total number of mileage per state that will give me a table? Say 50 rows, one for each state with the mileage. So creating calculated tables, you do this in modeling 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.

2. 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 our model. We have got this due date and I’m just going to create a table visualization which has the hierarchy in it together with the actual due date. So you can see that we have got the 13 July 2005 being the 13th day of the 7th month of quarter three of 2005. So the 7th month of the year and is in quarter three. But what if we didn’t want our year to start on January the first? Maybe we got a business reason that we wanted it to start in April. Or maybe we’re going to school or college and we want it to start in August or September. Well, this is when this autogenerated date table is not going to be very helpful to us. Now we can deselect it, we can switch it off by going to file options. Options. And in the current file data log the checking at the auto date time. Notice what happens to the hierarchy when I do that.

 It completely disappears. So we have to recreate a common date table. Now, there are 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 M function way of creating lots of dates and it didn’t like it. So I’m using the DAX way. So what we 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 modeling. Let’s add a new column. But I want to make sure it’s in this table. So actually I’ll click on the dot dot and go to new column here. So I’m going to say my business month equals the month of date. So see if that works. No. So I need to put date in our brackets. There we go.

Without the word date afterwards. So that’s fine so far, except it’s given me exactly the same result 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 the fourth month is actually the first month. Otherwise I want free deducted and I want twelve 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 ten 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 give me the year minus one. Otherwise just give 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 the day of date. So you scroll down and get date. And I also want a 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 to four numbers. Just make sure that works. No, that’s not working properly. It’s just taking the business year. I just want the date, 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. I might want a column 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. We’ve have 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.

3. Define role-playing dimensions

In this video we’re going to look at what roleplaying 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, descriptions. So you can see we have descriptions, we have class, we have color, dimension products, subcategory of 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 this subcategory, on 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 2, May and so forth. So you can see a bit of seasonality.

We’re 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 have worked, 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 is this all about? Well, in DAX 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 pairs of 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. Oh, I’ve got to make the other one inactive first. So this is being used as a role playing dimension. This 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 is more than one potential role where there is 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 photo and I’m summing up the sales amount based on this new photo. Now, I want this to actually be in our date table. So I actually have got this in the wrong table. So if I can remove it is that the one? Yeah. So I’ll delete that and I’m going to now add it into our date table. So, new measure. So this is sales amount based on the order date. I’m just thinking about it, I think it’s been added. And now I’ll create another visualization showing the business month and the sales amount.

So you can see some slight differences. So this is one way around it. Sales amount, order 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 with its builtin date table. If it needs multiple tables, it will simply create them in its memory. So here we have a date two and that is equal to our date table.

So maybe we call this dates date table two. 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 do 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 it 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.

4. Resolve many-to-many relationships – Joint Bank Accounts

Now up till now we’ve been looking at connections between tables using a onetomany 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 duplicatory. 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 money 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. 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’re 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 are four spreadsheets within this workbook which are prefixed M to M, many to many. So we’re going to have a look at people and transactions. So I’ll just click OK and they can load in here. What often happens is that people so we have Amy, Bryan 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 just have one bank account that might have a savings account for instance. So Amy and Brian have got bank account free 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 they could be related with the bank account field but let’s just find out what happens if we don’t relate them. So we have an owner and we have the amount of money total. So this is the sum, let’s make sure it says sum and you can see that the computer goes 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 6300, it’s not added them up that way. It’s just said that the total is 2100 and each has access to 2100. It thinks, well, what we can now do is have a relationship between bank account and bank account in the two tables.

And you notice what 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 card dunlity. 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 related function isn’t allowed. So now we have got this two way cross filter. We have got many to many. Let’s see what happens with our analysis. And now you can see the analysis works just fine. Amy and Brian both have access to one $400, chloe has access to her $700. So notice the totality still adds up to the totality. It does not add up to this total of what is being shown for Amy and Brian and Chloe. Now what happens if we change this from being both ways to going from people to transactions? 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 honor 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 we’ll 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 principal director going that way or is this the principal direction? If you have just got one direction, then by all means change this crossfilter so it goes in the right way. In the next video we’ll have a look at another many to many relationship that won’t be quite as easy to resolve.