Filter context and row context


#1

Hi Sam

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.

Thanks in advance.
Elizabeth Tachjian


#2

Elizabeth,

I found that the 4 videos by Sam in this section are great for context. They made me understand what the differences were/are.

The videos are under the section - Understanding Context.

Hope that helps.

Guy


#3

Hi Guy,

I have already watched all the videos? Are you able to please answer my question.

Thanks in advance.
Elizabeth


#4

Elizabeth,

Sorry for the delay - busy weekend.

I’d use the following formula for Row level context.

CALCUATE (SUMX(‘Product’, ‘Product’[Unit Price]))

This will give you the same answer as the formula you referenced.

The Iterating (X formulas) use row level context

Guy


#5

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


#6

Hi Nick,

Thanks for making clearer.

How do I set a column to be the primary key.

Cheers
Elizabeth


#7

@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:

But I would say just setting the relationship should work for vast majority, if not all, of the times.

-Nick


#8

Thank you Nick. Much appreciate.