- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Context transition, expanding tables and CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-15-2018 02:50 PM

Hi,

I'm gaining a better appreciate for DAX and context. There is one topic that still has me a little puzzled. Appreciate some help to reaffirm my understanding (or corrections are welcome) and help with the bits I don't quite get yet...

- I think I understand what is happening in my calculated columns
- I see similar for my measures, but not entirely working as I expect
- I'm not looking for better methods to achieve a result, I'm looking for ways to prove the behaviour and theory

**Data and model**

Three tables, one fact (with amounts) and two dimension (colors and locations).

Data within each:

**Simple matrix FYI**

Sum and count respectively

**Calculated Columns**

Note - I know I could (should) use RELATEDTABLE or RELATED for these but am testing this theory with simplified examples.

I create the following calculated columns. The results are as expected when 'wrapped' in a CALCULATE that causes a context transition from (calculated column) row to a filter, thanks to the 1-to-many relationships.

__In dimColor__

Total Amount CALC = calculate(sum(factSales[fAmt]))

Correctly results in Red =11, Green = 19, Blue = 17

__In dimLoc__

Total Amount CALC = calculate(sum(factSales[fAmt]))

Correctly results in North = 15, South = 21, East = 11

Now, the fact table...

__In factSales__

Color Name CALC = calculate(values(dimColor[dCname]))

and

Loc Name CALC = calculate(max(dimLoc[dLname]))

Both result in the correct colour and location (values, max or I could have used various other functions).

So, these last two columns in factSales work even though the relationship is many-to-1 and single direction back to the dimension tables, because CALCULATE transitions the row to filter context.

- This 'magically' works uphill of the relationship because of Expanding Tables???

**Measures**

Tried to replicate above theory.

Measures are:

Count Color noCALC = countrows(dimColor) Count Col from dimLoc = sumx(dimLoc, CALCULATE(countrows(dimColor))) Count Col from factSales = sumx(factSales, CALCULATE(countrows(dimColor)))

and

Count Loc noCALC = countrows(dimLoc) Count Loc from dimColor = SUMX(dimColor, CALCULATE(countrows(dimLoc))) Count Loc from factSales = SUMX(factSales, CALCULATE(countrows(dimLoc)))

Both noCALC ones are incorrect - filter context flows in direction of relationship, measure can be made to work modifying the relationship or using CROSSFILTER().

But, I to understand how to make **Count Col from dimLoc** and **Count Loc from dimColor** the same as the similar measure based on iterating factSales.

So my questions:

- Is the theory of row to filter context using CALCULATE, expanding tables and uphill many-to-1 true for both measures that iterate factSales?
- Why does the same not hold true for the measures iterating the other dimension table (separated from factSales by 1-to-many then a many-to-1 to the other dimension table)?

I greatly appreciate the time taken by those willing to offer help and advice. And truely hope I can repay the favor one day, or pay forward the kindness.

Solved! Go to Solution.

Accepted Solutions

## Re: Context transition, expanding tables and CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-17-2018 02:18 PM

Not an answer, but I've been furiously researching and wanted to share some related and very useful resources that I believe are explaining this. I advise those wanting to understand this better, spend time reading (and re-reading many times over) these:

- Good introduction https://powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/
- A good explanation http://blog.gbrueckl.at/2012/05/resolving-many-to-many-relationships-leveraging-dax-cross-table-filt...
- Quite a technical overview, I feel this is necessary to know http://mdxdax.blogspot.com.au/2011/03/logic-behind-magic-of-dax-cross-table.html

And some great articles on all things realted to this:

- Intro to contexts https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
- Then context transition https://www.sqlbi.com/articles/understanding-context-transition/
- Then calculate https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
- Finally https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

PS I need to read over these many times still, but I am less convinced I have the right expectations in my original post.

All Replies

## Re: Context transition, expanding tables and CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-17-2018 02:18 PM

Not an answer, but I've been furiously researching and wanted to share some related and very useful resources that I believe are explaining this. I advise those wanting to understand this better, spend time reading (and re-reading many times over) these:

- Good introduction https://powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/
- A good explanation http://blog.gbrueckl.at/2012/05/resolving-many-to-many-relationships-leveraging-dax-cross-table-filt...
- Quite a technical overview, I feel this is necessary to know http://mdxdax.blogspot.com.au/2011/03/logic-behind-magic-of-dax-cross-table.html

And some great articles on all things realted to this:

- Intro to contexts https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
- Then context transition https://www.sqlbi.com/articles/understanding-context-transition/
- Then calculate https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/
- Finally https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

PS I need to read over these many times still, but I am less convinced I have the right expectations in my original post.

## Re: Context transition, expanding tables and CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2018 03:49 AM

Hi

You are almost there - and yes understanding the concept of table expansion is what you need to focus on to understand why this is not working for you.

First get rid of the concept that filters flow from the one side of a relationship to the many side. (Dimensions can filter facts). That is a very simplified explanation and in order to really understand filter context you need to move on the more advanced topic - table expansion.

I will not go into to much detail here about it - you can text me an email if you find it hard to understand (Lasse0509@gmail.com)

Note: I havnt downloaded your pbix file and looked at the data I just think I know what your issue is simply by reading the text here.

Summary:

Tables in DAX are always expanded following a *-1 relationship. So quite often the expanded fact table actually contains almost the entire datamodel (and this has nothing to do with bidirectional filtering fyi).

So the reason why fx. Count color NoCalc is not working is because you want a countrows of DimColor and then filter it by locations in the pivot table. Does the expanded DimColor table contain the columns of DimLoc? No because they cant reach each other through a *-1 relationship. Thats why you see 3 in the rows and on the total. Look at your datamodel and make sure you understand what i just wrote.

Now try to write this formula instead:

Calculate ( Countrows ( DimColor ) ; factSales )

Did it work? If not i have misunderstood what you want. If yes try to study what I did - I parsed the factsales table as a filter argument in calculate. Does the expanded factsales table contain all the columns of both DimColor and DimLoc? Probably yes

Now you know a bit about the magic of table expansion.

Br,

## Re: Context transition, expanding tables and CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-01-2018 06:09 PM

Hi @lasse0509, thanks for your response. I thought this topic might have been rather buried by now

I have a much better appreciation of Expanded Tables now, and your post has reaffirmed my understanding, thanks.

There was recently a post by Alberto on his SQLBI blog that went a long way to helping explain this as well - https://www.sqlbi.com/articles/expanded-tables-in-dax/