Lesser Used Web Parts of SharePoint

This week I’m beginning my summer break from BI and returning to SharePoint to look at some of what I like to call the lesser used web parts. Some of these web parts I will discuss over the next several weeks did not exist in the original SharePoint 2007 (Original for me because that is when I started using SharePoint). Some of the web parts might not have even existed in SharePoint 2010, but only appeared in SharePoint 2013. So depending on which version of SharePoint your site is current on, you may or may not see some of the web parts I will describe. However, I will try to tell you whether the web part existing at least in SharePoint 2010 and/or SharePoint 2013. Some might even appear within different categories of web parts because Microsoft chose to regroup some web parts between 2010 and 2013. I will try to let you know that too. With that in mind, let’s begin with a web part that did exist in both SharePoint 2010 and SharePoint 2013:

Relevant Documents Web Part

Often the number of documents in a site becomes overwhelmingly large and finding the documents I worked on can be quite a challenge. This is especially true of collaboration sites. The Relevant Documents web part, which exists in both SharePoint 2010 and SharePoint 2013, helps me find what I want. Furthermore, my site does not need a custom view or custom page to display the documents relevant to each person who has edit rights to the site. This web part automatically detects the currently logged in user and filters the documents returned by that user. I don’t even need to know in which library to search in because this web part searches across all libraries in the current site (but not subsites). That means that it returns not only documents from the document libraries in the site regardless of the library names, but also items from image libraries and page libraries. Let’s see an example.

The Relevant Documents web part, like all web parts, must be hosted within a page. Therefore, I must first either create the page I want to use or navigate to an existing page.

Next I edit the page. Depending on the version of SharePoint, the Edit this page option may either appear in the Site Actions dropdown menu (2010), the Actions icon (2013), or the Edit button (2013).

I then find a place on the page where I want to add the web part and from the Insert tab, click on Web Part in the Parts group as shown in the following image.

SharePoint then displays three boxes across the top of the page beginning with Categories on the left. Select Content Rollup from the Categories list.

I now see the Relevant Documents web part in the Parts box. Select this web part by clicking on it.

Additional information about the selected web part then appears in the About the part box. To add the web part to my page, I simply click the Add button in the bottom right side of this area.

The following figure shows how this dialog looks in SharePoint 2013. However, the changes in SharePoint 2010 are minimal.


After I add the web part to the page, it automatically displays any documents in the current site that I last modified by default. The theory of this default is that documents I recently modified would be the most likely files I would want to return to edit further.

This web part does have some properties that I may want to tweak. To open the web part properties, hover over the web part title until the dropdown arrow appears on the right side of the header.

Select: Edit Web Part from the dropdown menu. I then need to scroll to the right and possibly up to see the properties panel. This dialog consists of several property groups. The first group: Appearance, is open by default. Here I can change the Title property to change the web part’s displayed title.

The other properties I may want to change can be found the Data group as shown below:


Note that there are separate options to let me see all documents that I created, even after someone else modifies it and documents which I may have checked out that others created and modified. The checkbox to include a link to the folder or list allows me to open the library rather than just opening the document. Finally, I can adjust the number of items shown in the list. However, my recommendation is that for most users, a number from 1 to 100 makes the most sense.

In conclusion, I could create a page on my site with the name My Relevant Documents. Then by using this web part, every contributor to my site can go to that one page to see only the documents that they have added or have been working on.

That’s all for this week. Hope you are having a good summer and next week I will continue with a related web part: Site Aggregator.

C’ya.

Map It For Me, Please

Last week I introduced Power View by creating a simple table and then a chart from that table. This week, I’ll do a quick overview of another visualization within Power View, the ability to display your data on a map.

To begin, I open Excel and build a data model with the data I want to visualize in a map. I need to specify location information using some of the fields in the data model. In theory, I can use anything that identifies where the measure I’m displaying takes place. Ideally, I would like to have latitude and longitude for each fact instance in the fact table, but that is not always possible or even necessary. For example, let’s begin by looking at the relative sales by city from the Contoso dataset.

After opening my Excel spreadsheet and building an appropriate data model, I return to the Excel window and from the Insert tab click the Power View button in the reports section.

This opens a new worksheet as shown below with a blank design area on the left and my field list from my data model on the right.

I then drag the fields I want to use in my data visualization to the Fields box at the bottom of the right panel. For this example, I will drag the CityName field from the Geography dimension and the SalesAmount field from the FactSales table. This gives me the two column table shown below with sorted by the city by default.

To change the visualization, I need to open the Design tab which appears when I click anywhere within the table in my design area. If I had multiple tables, I would have to be sure to click in the table for which I want to change the visualization first. Then from the Switch Visualization group, I select Map.

The default visualization, shown below, displays a bubble for each city that I have data. Each bubble’s size represents the relative sales amount derived from that city.

Because I did not specify a field to use as a group level for color, all of the bubbles initially appear the same color. However, I can easily specify a different color for each country by copying the field RegionCountryName to the Color parameter. This assigns a unique color to the bubbles within a country that is different compared with other countries. At first glance, everything may appear to be okay, but then I noticed a bubble in the southeast portion of the United States that had a different color. Hovering over that bubble, I see information about the bubble including the city name, the country, and the sales amount. In this case, the city is Saint Petersburg. However the country is Russia, not the United States (Saint Petersburg, Florida is perhaps what the map was thinking.) This occurred because the location criteria was only based only on city, not city within the country. In fact, if I zoom into the map further, I find other bubbles that placed the city in the wrong country.

One way to fix this issue is to use a field that has both the city name and country in it. However, you cannot create a new calculated column from within Power View. This type of change must occur in the data model. Therefore, I could return to the data model and open the DimGeography table to create a new concatenated field. This field combines the city and country names into a single new field: City_Country using the following formula:

= [CityName] & “, ” & [RegionCountryName]

The resulting new column appears in the following figure.

If I replace the [CityName] field with the [City_Country] field in the locations box as shown below, it appears at first glance that the problems with incorrectly positioned cities have been solved.

But again if I expand the map, I can find a few cities such as the one shown in the figure below that are not correctly positioned.

Honestly, I have not been able to figure out why a few cities are still displayed incorrectly. However, I have another way I can ‘fix’ the problem. First I turn on the Filters Area which has been turned off to maximize the size of the map.

I then drag the RegionCountryName field from the DimGeograph table over to the Filter panel. This shows me a list of unique values for this field. I can then use the check box to select one or more countries to display on the map at one time. For example, let’s just display the United States.

When I add the filter, the bubble for Cheshire, United Kingdom disappears. As you can see in the following figure. Changing the map background to Road Map Background, I get a more colorful map that might be more suited for a report that appears in color.

However, this is not the only way to filter data. I can also create a slicer in the design area by dragging the field by which I want to select data and dropping it in an empty part of the design area. The figure below shows me dragging the field RegionCountryName to the design area to the right of the map legend. This initially creates a single column table with the values from this field.

Next, without leaving the field, I can go to the Design tab to the right of the Power View tab and select the Slicer button from the Slicer group. This action converts the table into a slicer object that controls all the other objects in the current page.

Now I can filter the map to any country I want to focus on. Typically, selecting a country also zooms into the map to display that country as shown in the following image in which I selected Japan.

Any time that I want to return to the map displaying all the countries again I can click on the small blue ‘eraser’ button in the upper right corner of the slicer table. Note that this button only appears while the mouse is hovering within the slicer.

That’s it for this week. Next time I will look at some other features of Power View. C’ya.

A New View in Excel

I have used many of my blogs over the last couple of years to talk about PowerPivot in Excel 2010 and Excel 2013. By now you should have a pretty good idea how to build data models and analyze your data. You many even have used the Pivot Chart capability to display your data results visually. But did you know that there was another way to visualize your data within Excel 2013?

Just like PowerPivot that now comes preinstalled with Microsoft Office Excel Professional Plus 2013, so does Power View. To use this tool, you must also have installed Internet Explorer and Silverlight on your computer. If you have those prerequisites, you can enable Power View by opening the options dialog in Excel and selecting Add-Ins. At the bottom of the right panel is an option to manage different classes of Add-ins. Using the drop-down menu select: Com Add-ins and click the Go button. From the list of available add-ins, locate Power View and make sure the checkbox to the left is selected. Then click OK.

Next open the Insert ribbon in Excel 2013 and click the Power View button that appears in the Reports group. If this is your first time using Power View, you will need to enable it by clicking the Enable button that appears. This opens a separate Power View worksheet in the current workbook which will look something like the following:

If you do not have Silverlight installed, you will be prompted across the top of the Power View worksheet to do this. Click “Install” to install Silverlight. When the process completes, click the “Reload” button in the message bar to return to Power View.

In addition to the Power View worksheet, you should see a Power View tab with several options as shown below:

Because I started from a blank spreadsheet, I have no data I can use in my Power View report yet. In fact, the Power View Fields panel tells me that I need to create and select a range of cells with data and then click Power View from the Insert tab to proceed. For today’s quick example, I will create a simple table as shown below:

Once I’ve added this table to my Power View report worksheet, the Fields panel now shows me my active tables and the fields within those tables. Notice that numeric fields are automatically treated as aggregated sums. I can select the fields that I want to appear in my table by either clicking on the checkbox before the field name in the top half of the Power View Fields panel or I can drag the fields that I want down to the FIELDS box in the lower half of the panel. The advantage of dragging fields to the FIELDS box is that I can arrange the order of the fields here by dropping new fields in the position I want and I can even drag and drop fields later to rearrange the order of the fields in the table. The following figure shows the result of including all of the fields from the original table in my Power View table.

Of course if I don’t want to display all the fields, I can remove them by simply unchecking the check box in the upper portion of the Fields panel or I can use the dropdown menu to the right of any field in the FIELDS box to remove the field or change the aggregate function used for that field. For example, suppose I only was interested in the total student populations of each of the schools. I can simply remove the Student Grade column to achieve that result.

Next, suppose I wanted to have a second table or a chart on the same page. To do that, I begin by clicking in any blank area (not defined by the rectangle created by the first table). I then select the fields I want to include in my table or chart. In the example below, I use only the school name and student population just as was done in the first table.

I can then go to the Design ribbon associated with the Power View worksheet whose tab is displayed to the immediate right of the Power View tab. The first group contains options to switch the visualization of the data. The fourth icon, Other Chart, displays a dropdown that includes options to display the data as a line, scatter, and pie chart as shown below. There are some other interesting options in this ribbon like Map and Tiles that I will cover in future installments of this blog, but for now, let’s display a Pie chart of this data.

When I select PIE, Power View attempts to determine which fields to use for each part of the pie chart. In this case with only two fields, the answer is easy since only one of the fields is numeric. The numeric field is chosen as the size field that determines the size of the pie slice while the text field becomes the identifier of the slice which Power View calls Color. Note that there is no way currently to change the individual colors used in the pie chart. However, you can use the Themes dropdown in the Themes group of the Power View ribbon to change the color set used. Keep in mind that this can also change the font used for text on the page.

So what happened to Student Grades? If I click anywhere within the pie chart and the select the checkbox next to Student Grade in the Fields panel as shown below, Power View creates dividers in each of the schools that represent ‘sub-slices’ one for each grade in the school.

Unfortunately these sub-slices are not labelled so it is not possible to determine visually which slice belongs to each grade. Suppose we went back to the first table in this worksheet and added the Student Grade column back in as shown below.

Now the table includes a row for each grade with the number of students in that grade. At first you might say that did not help much.

However, if we click on any of the sub-slices in the pie chart, the table automatically filters to the information for that sub-slice/grade as shown below.

Ok, I know we have a lot more to learn about Power View and over the coming weeks, I will attempt to introduce you to its many other features. I also know that the charts created today were not that dissimilar to charts and table you could create with PowerPivot tables and charts. In fact, those tables and charts have a greater degree of formatting flexibility. However, we have to walk before we can run. In future segments, I will show how to create Power View charts that you could not create before.

Until then, C’ya!

Can You FILTER() That Down For Me

The last several weeks I have been looking at how PowerPivot in Excel works with Row Context and Filtered Context. I showed that most column expressions use a row context while measures use a filtered context although I could add and remove filters using certain expressions that allowed me to define a filter as a Boolean expression in one of the parameters. Last time we even looked at how to remove the filters by using the ALL() function. This time I will explore the FILTER() function which allows me to define a permanent filter condition to a measure no matter what dimensions or slicers the user chooses for the pivot table. In fact, in the case I am going to show you today, I need to do this because I need one measure to use all filters defined by the dimensions in the pivot table, and I need another measure to obey those filters plus one more.

Again I will use my Contoso data model that I’ve been using for all the examples in this set. I want to look at the number of orders that have returns and compare that to the total number of orders. I initially will want to show this information by sales channel and year/month. However, once I have my pivot table defined, I could of course change the dimensions I want to explore.

Let’s begin with a basic Sales pivot table as shown below.

I built this table using my basic data model with no additional calculated columns or measures except the calculated column in the date dimension that I use to order the name of the months correctly. I can use any of the columns in the FactSales table as my value field as long as I change the aggregate function from SUM to COUNT. By default, Pivot tables assume that numeric fields are summed and non-numeric fields are counted. But as long as I change the aggregate function for numeric fields to COUNT, I will get my desired results. I also modified the formatting to get rid of any decimal places and to add a thousands separator. Other than that, I did nothing special to build this table.

However, now I am going to return to the FactSales table and add a simple measure to count the total number of sales. The expression I will use is shown in the following figure.

I use the COUNT() function which has a single parameter, the name of the column I want to count. Again I could choose any column, but I chose the column [ReturnQuantity]. I will come back to format this measure in a moment, but you can see that the count is a little over two and a quarter million sales records. In fact, I know that this is correct by simply looking at the number of rows in my FactSales table.

Next, I want to count the number of sales records that have returns. This I can do by comparing either the [ReturnQuantity] column or the [ReturnAmount] column to 0. Only sales records which have values greater than 0 for these two columns represent orders which had returns. How can I do this?

One way I could do this is to use the SUMX() functionwith a second measure named [ReturnCount2]. This function has two parameters. The first parameter must be a table and second parameter is an expression of what I want to count. So I might think that I could do something like the following expression:

ReturnCount2:=SUMX(FactSales,IF(FactSales[ReturnAmount]>0, 1, 0))

The theory is that I want to compare the column [ReturnAmount] to 0 and if it is greater than zero to add one to my ReturnCount2 value. I cannot simply sum the [ReturnAmount] because this column represents the dollar value of the return. Nor can I use [ReturnQuantity] because the buyer may have returned more than one of the item from the order and summing the quantity would over count the total number of orders with returns.

I could also use the COUNTX() function. However, if I simply replace SUMX() with COUNTX(), I will get the total number of orders in the FactSales table because COUNTX() will could all non-blank rows. But I can trick the IF() into returning a blank for orders without returns by using the following expression:

ReturnCount2:=COUNTX(FactSales,IF(FactSales[ReturnAmount]>0, 1, BLANK()))

But both of these solutions used the entire FactSales table. There is one other way I want to show you today. I can use the FILTER() function to apply a filter to the FactSales table to return a subtable that only has rows with returns by using the following expression to return a table

FILTER(FactSales, FactSales[ReturnAmount]>0.0)

I can now replace the first parameter in COUNTX() with this FILTER() result which is a filtered table. I can then use any column in FactSales as the column I want to count. Well, almost any column. Actually, I cannot reuse the [ReturnAmount] column which is used in the FILTER() expression because this confuses DAX, but as I said before, I can count on any column in the table. Therefore, my [ReturnCount2] measure expression is shown below.

In this image you can see that I already formatted my measures as numbers without decimals but with thousands separators. Why do I format the numbers here? Simply because it saves time from having to format the numbers in each pivot table in which I use the measures. If I display these two measures in my pivot table side by side, I can see the total number of order by channel in each month along with the number of orders that had returns.

Suppose I wanted to show this information to management and rather than look at the raw counts which could take a bit of time to interpret, I decide to calculate the percent of orders that have returns. I can create a third measure as shown in the following figure that uses the results of the first two measures. I can then format this measure as a percentage prior to using it in my pivot table.

Returning to my pivot table, I remove the counts which I no longer need to display and replace them with the [Percent_Returns] so that management can quickly see that Catalog sales result in the most returns and Store sales in the least returns. Returns do not vary greatly by month, something that I will leave up to you to explore with a Pivot Chart.

Well, I hoped you learned some new ways to apply different filters in your measures from this discussion. C’ya next time.

I Want It ALL()

This week I am going to reverse direction on applying filters to my pivot table and show you how and why you might want to remove all filters instead of adding filters to an expression.

Again working with the Contoso dataset, I am going to start by looking at sales by product category. In addition, I want to be able to slice my data by channel or combinations of different channels. To do this, I want to use the visually friendly slicer tool as shown in the following figure. Note that in this case, I already have selected only the Online channel which results in a total of $2.6776 billion in sales. Keep in mind that the total of all sales across all channels is a little more than $12.4 billion.

Now for each product category, I want to see the total sales in that channel compared to the total sales across all product categories and all channels. If I refer back to my earlier blog on the different ways pivot tables can represent data by using built-in features, I might try looking there first to see if there is a fast way to accomplish my task. By right clicking on any of the rows in the Sales Total column and selecting the option: Show Values As, I can pick from a dropdown menu of different built-in calculations. Many of these options calculate percentages of row, column or grand totals of either the entire pivot table or a group level. These options also provide difference and running total calculations.

For example, if I were to select % of Grand Total, I would get some interesting percentages. However, these values would be based on the total sales of the slicer filter, in other words, the total sales for online sales as shown in the following figure.

So let’s play a little with a different pivot table that shows total sales by each of the channels. In the figure below, I’ve included the channel as my row filter and a have two columns which both show the total sales amount. Note that each row of the total sales amount is filtered by the channel. This is an example of filtered context when calculating a measure.

Now I’ve labeled the first of the two column: Total Sales Amount and will therefore let the pivot table display the sum of the measure filtered by the channel.

However, I’ve labeled the second column: % Dales by Channel. I can right click on any of the values in this column to select one of the other built-in calculations. In this case, calculating a percent of the grand total will show me the percent of sales that come from each of the four channels as shown below. The value in the Grand Total row displays 100% because all sales are represented by one of the four channels. Note here that it is clear that online sales account for only 21.57% of the total sales. I can use this information to validate what I’m about to do in the next step.

I am going to create a new measure named: Percent_of_Total_Sales. To generate a value for this measure, I want to sum the column FactSales[SalesAmount] for the filtered context of each place this measure appears in my pivot table. However, to get a percentage of total sales, I need to calculate the sum of FactSales[SalesAmount] for all sales, not just sales for a channel or product category, or any other filter criteria. In effect, I want to calculate the total sales as if there was no filter context in the pivot table at all. I can do this by creating a ‘new’ table for the SUMX() function (remember SUMX() has two parameters, the first of which must reference a table of values). There is another function I must use to eliminate the filter context for this ‘new’ table. This function is aptly named: ALL(). When I use ALL(FactSales) (and yes, the parameter for the ALL function must be the name of the table and it returns a table with all filters removed), I can get a ‘copy’ of the FactSales table without applying the filter context of the pivot table. In other words, all of the records in the original FactSales table will be included in ALL(FactSales). If I use this ‘new’ table as the data source for my SUMX() function and then simply sum the Sales Amount column using the SUM() function as shown below, I can return the total sales of the unfiltered FactSales table which then can be used as my denominator in my calculation. The numerator is a SUM() function of the Sales Amount also, but is calculated on the filtered context which in my case is filtered for online sales and product category.

Initially the measure returns a value of 1 because in the data model there is no filter context so the sum of the ‘filtered’ sales amount total divided by the ‘unfiltered’ sales amount total will be equal to 1. Rather than go directly to the pivot table, let’s first format this value as a percent by right clicking on the measure definition cell and selecting Format.

This option displays a dialog that lets me select the data category which is: Number. I then select the format of the number as Percentage with 2 decimal places. I then click OK to accept the format for the measure.

My measure calculation in the data model now displays a value of 100.00%. However, if I place this measure in my pivot table that displays sales by product category and uses a slicer to include on online sales, I can see my sales percentages as shown below. These values are now correctly dividing the product category sales for online sales by the total sales of my company. I can feel confident that the calculation is correct because the percent in the Grand Total line which represents sales from all product categories made through online sales is 21.57% which is the same percent I calculated in the pivot table earlier that only looked at sales by channel.

Using the channel slicer, I can select store sales instead of online sales. My previous pivot table told me that this should be 55.93% of the total sales. As you can see in the following figure, the Grand Total of my pivot table that displays sales by product category has percentages that also (accounting for rounding) add up to 55.93% since this pivot table uses the channel slicer.

In case any reader is wondering, the second pivot table that displays sales by channel, while on the same spreadsheet as the first table that displays sales by category does not use the slicer. If you have a slicer and multiple pivot tables or pivot charts, you must associate the slicer with each table and/or chart to which you want it to apply. It is not automatic nor implemented by spreadsheet page.

I hope you are starting to get a better feel for how row context and filter context work together with DAX expressions and functions to calculate values. Being able to correctly apply and remove filter contexts is essential in calculating values in many pivot table tables.

C’ya next time.

Getting the Right Context – Part 2

Last time, I introduced the concept of context within DAX expressions using by PowerPivot to calculate columns and measures. We saw that the default context for column calculations was row context while the default context for measures was filtered context. However, I ended the discussion by showing that within a measure calculation, I could use column context with certain aggregate functions like SUMX which can be used to apply an expression across all the rows of a table. However, before that expression is evaluated, that table is automatically filtered since I used it in a measure which begins by applying the filtered context of the pivot table to the rows used by the expression.

So this time, let’s dive a little deeper. Let me begin by going to the FactSales table of my Contoso data model and calculate a measure for total sales. This is not difficult and can be achieved by using the SUM() function as shown in the following figure.

Now let’s assume that I want a measure that shows only the sales made through the store channel. At first, I might try to use the SUMX() function along with the FILTER() function. The FILTER() function also has two parameters. The first parameter is the name of the table that I want to apply a filter on. The second parameter is a filter expression. I may at first assume that because I created a relationship between the FactSales table and the DimChannel table, that I can simply reference the column [ChannelName] and compare it to the string “Store” to filter the FactSales table. However, as you can see in the following figure, this expression would result in an error.

The reason for the error is that while the FILTER() function references the table FactSales, there is no context to link records in FactSales to DimChannel. I know you might ask, “Doesn’t the relation between these two tables define that context?” The answer is that the relationship between tables while defining the ‘mechanism’ of how to connect the two tables, it does not activate a context between the rows in FactSales with a row in DimChannel. When pointing from the many side of a relationship to the one side of the relationship, we must use the RELATED() function to activate the context within the expression. I show this in the following figure.

You can see that now I have a total sales for the just the stores as a measure. If I use this measure in a pivot table that displays sales by month and by product category, I will have the additional filter of sales by store in each of the pivot table cells.

What if, however, I wanted to create a calculated column in the table DimChannel that displayed the total sales for that channel? Again you might start with the SUMX() function because you want to calculate an expression from another table. In this case, the first parameter of the SUMX() function would be the FactSales table and the column that we want to sum would be the [SalesAmount] column. However, if we were to create this column, we might be surprised by the result, shown in the following figure.

All of the values in the column are exactly the same. Furthermore, if I refer to the image earlier in this blog for the total sales across all channels, I would see that the value displayed here in each cell of the column is actually the total sales. Again the problem is context. There is no context to refer back to FactSales from DimChannel. Therefore, when SUMX() evaluates the [SalesAmount] in table FactSales, it pulls values from all the sales records, not just the sales from the channel represented by the current row in DimChannel.

In this case, because I am going from the one side of the DimChannel relationship to the many side of FactSales, I need to return a table that contains just the rows from FactSales that represent sales from the channel in the context of the current row. I can do this by using the RELATEDTABLE() function which uses a single parameter, the name of the table on the many side of the relationship. I must also have the relationship explicitly defined between DimChannel and FactSales. I have already done this. So Power Pivot can use the relationship to create a subset of rows from FactSales for the current channel. I can then use this resulting table in the SUMX() function to sum the [SalesAmount] column as shown in the following figure.

As you can see in the figure, the first row which represents the store channel displays the same sales total as we calculated from the measure in FactSales earlier.

So again, you can see that there are many different ways to define the context of an expression. When dealing with multiple tables, it is important to understand whether you can perform a row context calculation by using the RELATED() function to extend the row context to the related table on the lookup side of the relationship or whether you need to use the RELATEDTABLE() function to filter the rows used in an aggregate function like SUMX() to calculate values for a column which uses the row context to define the link to the many side table.

Next time, I’ll look at some functions that let you turn off a filtered context and show where you might use it.

C’ya next time.

Can You Put That Into Context For Me?

Over the past several years, I’ve conducted many sessions on how to use DAX in PowerPivot to create columns and measures. Throughout this time, the one thing that has confused many people with whom I have talked with is the concept of context for an expression. Many do not understand why some equations appear to work across for defining new columns while other equations only appear to work when calculating measures within a pivot table. This week, and perhaps for another week or two, I am going to take a shot at explaining the concept of context. Until you get a good understanding of context, many of the DAX expressions for columns and measures may appear to be magic that somehow get the correct answer but you may just not understand why.

Let me start by defining the two major context types that DAX expressions have to deal with:

  1. Row Context – This is probably the easiest context to understand. When you define a formula to calculate the value for a new column, the formula is applied to each row individually. In fact, the value calculated can depend only on the value of other columns in the current row. This is much like the way you calculate a new column value in Excel. However, unlike Excel, it is more restrictive. Using Row Context, it is not possible to directly access the values in a prior or successive row because those terms do not really have meaning within a table in the data model of a pivot table.
  2. Filtered Context – This is probably easiest to understand when you think of the entire set of rows in a table. If you sum a numeric column or simply count any column in the table, you are applying a filtered context. Of course, in its simplest form, the filter is really the same as all of the rows in the table, or no filter at all. But let’s assume for a moment that you have a sales table that includes sales from each of your stores in each of the states of United States. Furthermore, you only want to know the sales for stores in the state of Florida. In database terms, you apply a filter using a WHERE clause to include only those rows from the sales table in which the sales came from a store in Florida. Of course you can have more than one filter. You might filter not only on the state, but also on a specific product that was sold during a specific month of a specific year. That would require four filters to be applied to the table to reduce the total number of rows of sales to only those you wanted to sum. Filtered context is often used in expressions that involve summing, counting, averaging, finding a minimum or finding a maximum. These are also referred to as aggregate functions.

So let’s see how this works in DAX. Using my Contoso sales table, suppose I want to calculate the profit for each sale. Since I want to perform the calculation per sale and because my sales table has one row for each sale, I can use a calculated column. The formula for my calculated column begins by taking the value of the column [SalesAmount] which is the money received from the sale. This is not the profit. To calculate profit, I need to subtract costs from [SalesAmount]. To do this, I can subtract the value from the column [TotalCost]. This gives me a pretty good idea of what my potential profit might be. However, in the Contoso data, I also have to consider any returned item. These are items returned because they are damaged or do not work. The policy is to refund the customer their money. However, the returned product it not worth anything and is considered trash. Therefore, I need to also subtract from [SalesAmount] the [ReturnAmount]. If I define a new column in my sales table, I can use the formula shown in the following figure to calculate the profit for each of the sales.

As you know, the PowerPivot data model applies the column formula to each record in the table. I can then use this column in pivot tables to display profit for sales by any of the appropriate dimensions such as time, location, product, etc.

Fortunately, in PowerPivot for Excel, displaying profit in the pivot table is as simple as moving the column [Profit] to the Values area in the field list. PowerPivot lets us use any numeric field in the values area without any additional work. However, if I attempt to create the same model in Analysis Services, I must create a measure as shown below.

The SUM() function is an aggregate function and therefore defines a measure, never a column. Note however, I must still first create the column [Profit] as previously defined. I cannot simply create a measure that sums the result of the expression as shown in the following figure.

This is because the calculation: [SalesAmount] – [TotalCost] – [ReturnAmount] is a row context expression since it must be calculated on every row of the table. However the SUM() function is a filter context expression (even if the filter is all the rows). I cannot combine the two in this expression without getting the Semantic Error shown above. The SUM() function can only aggregate the values of a single column, not aggregate the values of a calculation.

What I can do however, is use one of the other DAX functions, SUMX(). This function has two parameters, the first parameter is the name of the table I want to perform a calculation on. In this case, the table name is FactSales. The second parameter is an expression that can include one or more columns from the designated table. In this case, I can use the expression I previously used to calculate the [Profit] column as shown in the following figure.

As you can see in this figure, the resulting total profit across all the records in the FactSales table is exactly the same whether I use the expression SUM([Profit]) which sums the calculated column or if I use the expressions SUMX(FactSales, [SalesAmount] – [TotalCost] – [ReturnAmount]). This second expression essentially calculates the row context expression for profit on each row in the table FactSales. However, as a measure rather a calculated column, the expression is filtered by any dimensions used in the pivot table.

In the following figure, the only dimension is sales channel. Therefore, with four sales channels, there are four rows. Note that both calculations of total profit result in the same values for each channel.

What is the difference between the two? Since all of the sales records were used to build this table since all four sales channels were included, the same number of calculations were effectively performed in both cases, calculating the profit for each sales row and then summing the profit for each channel. The only real difference is that the first one required the prior creation of a [Profit] column which takes some of the precious data model memory while the second method using the SUMX() function does not require that column.

But what if we applied other dimensions any perhaps by using a slicer displayed the profit from only some of the sales. In the following example, the row dimension displays countries. However, only countries in Asia appear based on a slicer that limits which continent I want to see profits. I also included a slicer on year to limit the years in my analysis to only two years. Therefore, in the following figure, the profit amounts represent only a subset of the total profits for the Contoso organization.

How does this affect the calculation? To calculate Total_Profit, I still need the column [Profit] to calculate value for every sales row in the FactSales table, even rows that I will not use. On the other hand, to calculate Total_Profit_2, the filter on continent and year is applied first limited the total number of rows that are needed to be calculated from the FactSales table. The calculation of profit is then only performed on this filtered subset of rows and then aggregated appropriately based on the dimensions shown.

Thus, you might say for now that column calculations are generally row context and measures are filter context expressions. However some functions when used in measures let you perform a row context expression after applying a filter context like the SUMX() function and other aggregate functions that end in ‘X’ like COUNTX(), AVERAGEX(), MAXX(), and MINX().

Next time, I will dive a little further into understanding context within DAX.

C’ya next time.