Retrieving Values from UP the Hierarchy
Let's start by looking UP the hierarchy, i.e. from the Sales table up to the Categories table.
|Category Type (Invalid)|
|Category Type (Related)|
Retrieving Values from DOWN the Hierarchy
Next, let's try to pull a value from DOWN the hierarchy, i.e. from the Categories table down to the Sales table. Since we have multiple rows in the Sales table related to a single row in the Categories table, we're going to need to aggregate the values. Let's try to pull up Total Sales.
|Total Sales (Invalid)|
|Total Sales (Calculate)|
Iterators are a huge category of functions in DAX that can be used to solve almost any problem. We could easily do an entire blog series on iterators. So, we're just going to touch on a very simple iterator to showcase a little more about Row Context. Let's say that you wanted to create a new column in your table for Running Sum of Sales. You can order this running sum however you want. For our purposes, we'll order it by Sales[Order Number]. This means that [Running Sum of Sales] for Order Number 1 is just the sales of Order 1. The [Running Sum of Sales] for Order Number 2 is the sum of Order Number 1 and Order Number 2's Sales.
Before we can start looking at other rows, we need to be able to escape the Row Context of our current row. We can accomplish this with the ALL() function. The ALL() function simply removes all filters applied to a calculation. Remember that once we wrap our calculation in the CALCULATE() statement, our Row Context becomes Filter Context, meaning that it can be escaped using ALL().
|Running Sum of Sales (Incomplete)|
|Running Sum of Sales|
Now, our calculation is complete.
Hopefully, learning about Row Context has opened your eyes to some of the more interesting possibilities within DAX. Keep your eyes peeled for more posts on the amazing world of DAX. Thanks for reading. We hope you found this informative.