Context transition, expanding tables and CALCULATE

[Cymbolz]

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...

1. I think I understand what is happening in my calculated columns
2. I see similar for my measures, but not entirely working as I expect
3. 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:

factSales  dimLoc  dimColor

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)?  

Link to PBIX

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.

[lasse0509]

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,

[Cymbolz]

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/

[Cymbolz · Accepted solution]

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.