You can correct the previous formula with the following code: Sales = Sales - RELATED ( Product ) RELATED accepts a column name as the parameter and retrieves the value of the column in a corresponding row that is found by following one or more relationships in the many-to-one direction, starting from the current row context. If you want to access columns on the one side of a relationship from the table on the many side of the relationship, as is the case in this example, you must use the RELATED function. The row context in Sales does not propagate automatically to Product and DAX returns an error if you try to create a calculated column by using the previous formula. Product is on the one side of a relationship with Sales (which is on the many side), so you might expect to be able to gain access to the unit price of the related row (the product sold). This expression uses two columns from two different tables and DAX evaluates it in a row context that iterates over Sales only, because you defined the calculated column within that table (Sales). You could try this formula: Sales = Sales - Product Imagine you want to create a calculated column in the Sales table containing the difference between the unit price stored in the fact table and the product list price stored in the Product table. The interaction of row contexts and relationships is very easy to understand, because there is nothing to understand: they do not interact in any way, at least not automatically. Now that we have defined the model, let’s start looking at how the contexts behave by looking at some DAX formulas. All remaining relationships are set to be one-way cross-filter direction. The only bidirectional relationship is the one between Sales and Product.There is a chain of one-to-many relationships starting from Sales and reaching Product Category, through Product and Product Subcategory.There are a couple of things to note about this model: In order to examine the scenario, we use a data model containing six tables, which you can see in Figure 4-13.įIGURE 4-13 Here you can see the data model used to learn interaction between contexts and relationships. As you are going to learn, there are some subtle interactions between contexts and relationships and learning them requires some patience. If you create a row context on a table on the many side of the relationship, do you expect it to let you use columns of the one side? Moreover, if you create a row context on the one side of the relationship, do you expect to be able to access columns from the table on the many side? In addition, what about the filter context? Do you expect to put a filter on the many table and see it propagated to the table on the one side? Any answer could be the correct one, but we are interested in learning how DAX behaves in these situations, that is, understand how the DAX language defines propagation of contexts through relationships. Finally, to make things a bit harder, please recall that relationships can be unidirectional or bidirectional, depending on how you defined the cross-filter direction on relationship itself. Thus, an interesting question is “ How do the two contexts behave regarding relationships?” Moreover, because relationships have a direction, we need to understand what happens on the one and on the many side of a relationship. It is most likely that in your data model you will have many tables linked by relationships. Very few data models contain just one single table. With only one table, you need to face only interactions between row context and filter context in the same expression. You might have noticed that we deliberately used only one table: Product. We just started learning contexts, and this led us to some interesting (and surprising) results up to now.
0 Comments
Leave a Reply. |