DAX and RELATED in regard to SUMX

[dicken]

Hello,
Could someone explain or point me in the right to some information regarding these two measures;



< Total Revenue Fact 21M = SUMX(fTransactions,RELATED(dProduct[Price])*fTransactions[Units]) >

< SUMX(dProduct,dProduct[Price]*CALCULATE(SUM(fTransactions[Units]))) >

My question is why don't I need to use RELATED in the second example they both achieve the same result,
So if i'm reading it right SUMX is iterating over the dproduct table and then the calculate causes a row context ?(I think).
But I would have thought I would have to tell it that it was a related table to SUM?

Richard.

 

[Matt Allington]

Close.

i will refer to the formulas as 1 and 2

• both have a row context from the sumx. It has the same behaviour as a calculated column
• a row context does not automatically give a filter context. Filter context is completely different and separate. more on that later.
• the table containing the row context is the first parameter.
  • Formula 1 is a fact table
  • formula 2 is a dim table
• the second parameter can only reference a column in that same table specified in the first parameter, unless
  • You use related to bridge through an existing active relationship to another table. This can only access a column moving from the many side to the one side, not the other way (think vlookup)
  • you use an aggregator, like sum(). If using an aggregator, it is simply aggregating that column, unfiltered. Remember, row context ≠ filter context. It is irrelevant if there is a relationship, or not.
• so, in summary, there are 3 ways to reference a column inside sumx
  • Naked column (no function wrapping it) as long as that column is in the table specified in the first parameter.
  • Related, if the column is on the 1 side of an active many to 1 relationship (also 1:1)
  • wrapped inside an aggregator (works for any column in any table, relationship or not. There is no auto filtering)
  • technically there is also relatedtable(), but it’s rarely used.

To answer your questions,

• you don’t need related in the second formula because you are using an aggregator instead (Sum)
• you can sum any column in any table regardless if you have a relationship or not. The relationship is therefore irrelevant in the ability for sum to add up the numbers in the column.
• even if there is a relationship, the column is unfiltered
• if you want to cause the row context to filter the table before sum does its job, you need to wrap it inside CALCULATE (as yours is), hence causing context transition (the row context converts into a filter context). Once again, the relationship is irrelevant apart from the fact that CALCULATE propagates all filters (including the context transition) to all other tables. If you happen to be summing a column in a table that is affected (ie there is a relationship), then the result will most likely change. If you are summing another column, then no change occurs.

Note: 2 is more efficient than 1

 

[dicken]

Close.

i will refer to the formulas as 1 and 2

• both have a row context from the sumx. It has the same behaviour as a calculated column
• a row context does not automatically give a filter context. Filter context is completely different and separate. more on that later.
• the table containing the row context is the first parameter.
  • Formula 1 is a fact table
  • formula 2 is a dim table
• the second parameter can only reference a column in that same table specified in the first parameter, unless
  • You use related to bridge through an existing active relationship to another table. This can only access a column moving from the many side to the one side, not the other way (think vlookup)
  • you use an aggregator, like sum(). If using an aggregator, it is simply aggregating that column, unfiltered. Remember, row context ≠ filter context. It is irrelevant if there is a relationship, or not.
• so, in summary, there are 3 ways to reference a column inside sumx
  • Naked column (no function wrapping it) as long as that column is in the table specified in the first parameter.
  • Related, if the column is on the 1 side of an active many to 1 relationship (also 1:1)
  • wrapped inside an aggregator (works for any column in any table, relationship or not. There is no auto filtering)
  • technically there is also relatedtable(), but it’s rarely used.

To answer your questions,

• you don’t need related in the second formula because you are using an aggregator instead (Sum)
• you can sum any column in any table regardless if you have a relationship or not. The relationship is therefore irrelevant in the ability for sum to add up the numbers in the column.
• even if there is a relationship, the column is unfiltered
• if you want to cause the row context to filter the table before sum does its job, you need to wrap it inside CALCULATE (as yours is), hence causing context transition (the row context converts into a filter context). Once again, the relationship is irrelevant apart from the fact that CALCULATE propagates all filters (including the context transition) to all other tables. If you happen to be summing a column in a table that is affected (ie there is a relationship), then the result will most likely change. If you are summing another column, then no change occurs.

Note: 2 is more efficient than 1

 

[dicken]

I appreciate 2 being the more efficient, but I understand the first (related ) better, thank you for taking the time to answer so fully,
it will take a bit longer for it to sink in, but will persevere.

Richard.