Reply
Regular Visitor
Posts: 16
Registered: ‎02-28-2018
Accepted Solution

Context transition, expanding tables and CALCULATE

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

 

Capture1.PNG

 

Data within each:

 

 

Capture2.PNGfactSales  Capture3.PNGdimLoc  Capture4.PNGdimColor

Simple matrix FYI

 

Sum and count respectively

 

Capture5.PNG

 

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.  

 

Capture6.PNG

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.


Accepted Solutions
Highlighted
Regular Visitor
Posts: 16
Registered: ‎02-28-2018

Re: Context transition, expanding tables and CALCULATE

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:

 

And some great articles on all things realted to this:

 

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

View solution in original post


All Replies
Highlighted
Regular Visitor
Posts: 16
Registered: ‎02-28-2018

Re: Context transition, expanding tables and CALCULATE

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:

 

And some great articles on all things realted to this:

 

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

Occasional Visitor
Posts: 1
Registered: ‎04-30-2018

Re: Context transition, expanding tables and CALCULATE

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 Smiley Wink

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

 

Br,

 

 

Regular Visitor
Posts: 16
Registered: ‎02-28-2018

Re: Context transition, expanding tables and CALCULATE

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

 

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/