PL-300 – Section 29: Part 3 Level 7 – Filter and Value Functions Part 2

213. RELATEDTABLE and COUNTROWS

So, that’s all very well going from the many to the one, but what if I wanted to go from the one to the many? For example, I have these categories. What if I want to know for each category what the total number of sales or the total standard cost is inside the model?

Now, yes, I can do that very easily by going to my visualisation and going okay, give me the category name and give me the total standard cost and it’s this one and now give me the answer, but suppose I wanted it in one particular table. For instance, I might want to hide this Fact Internet Sales and just give you standard fields based on what I think the model should be. So, we can do that by using a variant of RELATED, so if I was to go into the category and add a new column and this column is… Let’s call it standard cost. If I type in the word related, you can see that there aren’t any fields that the computer gives me and the reason for that is because we are right at the end of the chain. There is no further many-to-one that we can go up. Instead of using RELATED, however, we can use RELATEDTABLE.

So, what RELATEDTABLE does is it returns a filtered table. So, I want to filter from Fact Internet Sales. So, close that and there we go. That is our expression but, unfortunately, this gives us an error. This expression refers to multiple columns. So, what’s happening here? Well, RELATED gives us a single value going up the chain from infinity to one for many-to-one. So, it goes from Fact Internet Sales and gives us a category for instance, but we’re going down. Instead of it giving us a single value, it is as the name of the function suggests give us a table. So, it’s not just one column, it’s an entire table full of rows, full of columns.

Now, can you remember, let’s say I wanted to aggregate all of the product sales costs, can you remember what the function is for aggregating a table? So, it’s not SUM, it is SUMX.

SUMX returns a sum of an expression evaluated for each row in the table. Well, we’ve got the table. It’s this related table and what do we want to bring from it? Well, let’s say we wanted the product standard cost. So, there we have product standard cost. So, now let’s press Enter and see what we get and here we have got our standard cost. So, you can see we have 16 million for bikes, 203,000 for clothing, 262,000 for accessories. Notice that components is blank and notice that components is not there on this visualisation. The reason for that is because there aren’t any items which have successfully gone down the chain. What do I mean by this? I mean that there are no components, in fact internet sales. We may have the product, but we actually haven’t sold any and we can show this quite easily if we create another visualisation and we put in the product key. So, you can see there are 60,398 of them. We’ll put in product key again, but this time do not summarise. So, we still have the same total, 60,398, and we put in the category that we’d worked out previously using the RELATED function in the previous video and if I now sort by the category and sort ascending, you can see that there are no components. We haven’t sold any components. This count of the product key remains the same. So, this is the totality of the data we’ve got. So, we just haven’t sold any!

Now, we’ve got here SUMX. Maybe, we wanted to do COUNTX. So, COUNTX would count all of the rows in which the product standard key evaluates to a number or something that can equate to a number. So, if I just check that, you can see we’ve got 15,000, 9,000, 36,000 rows. If I use a calculator to add them together, we get to our 60,398 items.

Now, suppose, you want to just count the number of rows regardless of what the contents were in a particular key. Well, then, we would choose the function COUNTROWS. So, COUNTROWS just requires a table, but it doesn’t require a field because we’re just seeing if there is a particular row there regardless of what kind of information it is and you can see that gives us the same answer in this particular example. So, RELATEDTABLE, wherever you see in this spreadsheet Table Name, don’t forget RELATEDTABLE can give you a table. So, you can use whatever you get from a RELATEDTABLE as a table.

Now, notice what the description says. It evaluates a table expression in a context modified by the given filters. Very important last point, which we’ll have a look at content in the next video.

214. Context

In the last video, we saw that the related table function evaluates a table expression in a context modified by the given photos. So, what is context? And why does it matter? Well, let’s go back to our previous example, where we’re using SUMX. So, got SUMX of the related table fact internet sales, and we’re looking at the product standard cost. And we’re just going to change this into US dollars, just to simplify the numbers. So, here we have a number of 16 million. What is that number? And what is the difference between this number, and that formula, and this formula? While we just have SUMX of fact internet sales, and the product standard cost. What’s the difference between the other formula which we used related table and this formula? So, here are the two formulas on the screen. Why don’t you pause the video, and think what will the difference be? Well, the difference is that this second formula takes the entirety of the fact internet sales table and sums the product standard cost. So, that gives us, for every single row 17,277,000. Whereas, this formula takes the product category key, in this case, category one, goes down this chain all the way to fact internet sales, and then says okay, for just those categories, which were category number one, what is the total cost? But just for those items where the product category key, is equal to one.

Now, that’s the sort of filtering, and this filtering is what we call in Power BI, the context. So, here we’ve got the standard cost, where the standard cost is for banks was here, we’ve got it for the entirety. Now, this could be quite useful to have these two different formulas. Because what if we have a third formula? This is the percentage, where the percentage is equal to the standard cost, divided by this new column that we’ve just calculated. So, format it says percentage. So, this formula, which is the equivalent of this function, these two functions, one divided by each other, allows me to say well bikes are 97.31% cost of the totality of the table, whereas clothing is 1.18% and accessories 1.52%.

Now, we have already been looking at context in our visualisations. If we go back to this visualisation, you can see that, we have a product standard cost of 60 million, but only where the product category name is bikes. Same for accessories, same for clothing.

Now, if I change this visualisation to a matrix, and then add the order date, then we can see that the context changes again. Here this five and a half million is for bikes, with an order date in the year 2007. So, hopefully you can see from this, what A) context is all about, and B) why it’s important. Is the difference between having a figure of 16,000,001 being 17 million, is the difference between going through all the rows of a table, and going through just relevant rows? So, in Excel terms, a context is a bit like a filter.

215. ALL

Two videos ago we created this Percent column. So, this is in the Category table and it’s added to every single row. So, if we go to the visualisation that we were looking at last time, in the context, and we add in the Percent. Here we can see the Percent, need to Sum it. And there you are, 97 percent for Bikes, one and a half percent for Accessories, exactly as we’re expecting. So, now, as this is a matrix, let’s add onto the columns, the OrderDate.

So, we can break down this 97 percent into the various years. Except it doesn’t. You can see that it’s 97 percent, allegedly every single year, including years when there were no other things being ordered. So, what’s going on? Well, the problem is that this is being added to each and every row, this isn’t being added as an actual measure, it’s been added as a finished calculation. If you want it to be dynamic, if we wanted not just to be in the context of this ProductCategoryKey, but any other context, like for instance year 2006, we’ve got to go from it being a column, to it being a measure.

So, what do we want to do as a measure? Well, we want a measure that does this calculation. So, we’ve got this SUM of the related table, and we want to divide it by the entirety. So, let’s copy this, so I’m not recreating this new column from scratch. We’ll delete all of these existing columns. And we will add a new measure. So, this measure will, like the columns that were before it, calculate the percentage. So, here we have the Percent of Total and that is equal to the sum of X, to the SUMX of the related table, divided by the SUM of the entirety of the related table.

Now, because it’s a measure, it doesn’t appear in each and every row. It’s something that is calculated on demand. So, now, when we go back to our visualisation, and put in PercentageOfTotal, and I’ll just change it before we do, so that it’s a percentage. The computer gives us 100 percent, why is that? Well, the reason for that is because the context each time is, in this case, Bikes. So, we’re looking at the total of all of the bikes ProductStandardCost and dividing it by the SUMX of FactInternetSales, ProductStandardCost for this particular item. So, these two are in fact identical in this particular context. So is there a way in which we can say, “hang on, I want you to forget context. “I want you to take the entirety of the table, “regardless of the current context.” And what there is the word ALL. So, what ALL does, is it removes context from the current equation. So, you can see, ignoring any filters that might have been applied. So, we’ve got RELATEDTABLE and we’ve got ALL. So, let’s see what happens now. And now you can see that it worked. We have 97 percent, 1.5 percent, 1.52. So, it’s taking the RELATEDTABLE, FactInternetSales and dividing by the entirety of FactInternetSales. So, if we now go back and add the OrderDate into the columns, again it all works. It’s taking the internet sales in the context and dividing it by the totality.

So, does that mean that because we’re using it as a measure which has a context, do we actually need RELATEDTABLE here? And in this particular case, no we don’t. Because the computer will take SUMX based on the current context. So, let’s see what happens when we do this. And you can see, exactly the same result. So, we needed RELATEDTABLE when we were going up and down the chain and specifically down the chain. When we were doing a column. Because the column wasn’t taking advantage of the fact that it has a context. Whereas here, in a measure, it always does have a context. And so if you want the context to be ignored, then you should use ALL. And here we can see ALL in the functions spreadsheet. Returns all the rows in a table, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all of the rows in a table which is exactly what we’ve done.

So, ALL removes any existing context, which is applied using other formulas.

• Category: Uncategorized