In the case of creating a calculated column, CALCUATE (Sum('product'[Unit Price])), there is filter context since CALCULATE turns row context into filter context. Am I correct to say that the filter context will use the index column of the table as a filter context hence why the result is different for each row. I am trying to understand what the formula used as its filter context to get a different result for each row.
An index column ( primary key) will only be used if it’s set as such, or used in a relationship. Basically if you have context transition and you are in a row that has a primary key, DAX will look at that column ( and that column only) and filter based on that. If it is not set as a key, DAX will look at every column and use the combined values as a filter. So generally always best to set keys
@ElizabethTachjian
The easiest way is to create a relationship to another table. The table that has the primary key (generally your Dimension/Lookup tables) will then use that column, and that column only, during context transition. And in PBI, looks like that is your only option.
In PowerPivot, there is actually a menu to set this without a relationship: