Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cymbolz
Helper III
Helper III

Contexts and Expanded Tables

I feel I have a good understanding of explaned tables now, but this bit I cannot work out...simple example I've created:

 

Two Tables:

 

  • Fact
    • id
    • amount
  • Dim
    • id
    • name
    • category

 

Relationship on [id], single direction. 

 

The expanded Fact table would look like:

  • id  (native)
  • amount  (native)
  • Dim[id]
  • Dim[name]
  • Dim[cateogory]

 

Dim table has no expanded / related columns, just the native ones.

 

I create a measure as follows as it produces the correct result on a matrix with rows as Dim[name] or Dim[category] because the expanded Fact table contains those columns which will have the query / initial filter value per the row in the matrix.

 

Count Fact = COUNTROWS('Fact')

 

I try a similar concept in a calculated column of Dim table.  The column expression is per below and produces the expected result for each row, as CALCULATE transforms the row context to filter context and when counting the rows of Fact the expanded table has the related columns with filters on them. 

 

Fact Count = CALCULATE(COUNTROWS('Fact'))

 

Now for the part I though I understood until the concept of Expanded Tables was introduced to me.  I tried two methods of caculated columns in the Fact table both producing the correct (data wise) result.  But I'm struggling to understand why it works.

 

Dim Name = CALCULATE(VALUES(Dim[name]))

And

Dim Name 2 = CALCULATE(MIN(Dim[name]))

I don't get how the row to filter transformation has any impact on the Dim table.  I though maybe VALUES is doing something special, but can't see how MIN or similar functions do anything fancy.

 

Maybe the Dim[name] values are coming from the expanded Fact table.  If so, why and how? 

 

How does the DAX engine decide which table / expanded table to use in the evaluation?

 

Link to pbix

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

I won't pretend this is a complete explanation...

 

First off, this is probably a good reference article I recall reading:

https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/

(see the section Understanding the Evaluation Order)

 

As I understand it, when CALCULATE() is called within the row context of a physical table (such as in a calculated column), the physical table (corresponding to the current row) is added to the filter context. Whenever a physical table is added to the filter context via CALCULATE, it is interpreted as the expanded verison of the table.

 

From the article above, this can be interpreted as the expanded table being added to the filter context first within an outer CALCULATE, then an inner CALCULATE is performed with the actual filter arguments specified (none in your example).

 

The expanded table lets you access columns on the 1-side of the relationship.

 

So the calculated column added to 'Fact'

Dim Name = CALCULATE(VALUES(Dim[name]))

is translated to something like:

Dim Name =
VAR T = <filter from context transition> //which is the expanded Fact table corresponding to current row
RETURN
CALCULATE (
    CALCULATE (
        VALUES ( Dim[name] ),
    ),
    T
)

For all intents and purposes in a calculated column,

CALCULATE ( VALUES ( Column on 1-side of relationship ) )

is the same as

RELATED ( Column on 1-side of relationship )

So you could use CALCULATE (VALUES(...)) to refer to any column accessible by following many=>1 relationships, just like RELATED.

 

Anyone else like to add/clarify? 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

I won't pretend this is a complete explanation...

 

First off, this is probably a good reference article I recall reading:

https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/

(see the section Understanding the Evaluation Order)

 

As I understand it, when CALCULATE() is called within the row context of a physical table (such as in a calculated column), the physical table (corresponding to the current row) is added to the filter context. Whenever a physical table is added to the filter context via CALCULATE, it is interpreted as the expanded verison of the table.

 

From the article above, this can be interpreted as the expanded table being added to the filter context first within an outer CALCULATE, then an inner CALCULATE is performed with the actual filter arguments specified (none in your example).

 

The expanded table lets you access columns on the 1-side of the relationship.

 

So the calculated column added to 'Fact'

Dim Name = CALCULATE(VALUES(Dim[name]))

is translated to something like:

Dim Name =
VAR T = <filter from context transition> //which is the expanded Fact table corresponding to current row
RETURN
CALCULATE (
    CALCULATE (
        VALUES ( Dim[name] ),
    ),
    T
)

For all intents and purposes in a calculated column,

CALCULATE ( VALUES ( Column on 1-side of relationship ) )

is the same as

RELATED ( Column on 1-side of relationship )

So you could use CALCULATE (VALUES(...)) to refer to any column accessible by following many=>1 relationships, just like RELATED.

 

Anyone else like to add/clarify? 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you @OwenAuger !!!

 

Makes a lot of sense, and thanks for reminding me of the article too (which I had read a little while ago but makes more sense each time I read it again).

 

So if I've interpreted correctly, the row context transformation as a result of CALCULATE() includes the expanded table columns, they become the filter context which then applies such filtering to the Dim table as well, before the expression - either VALUES or MIN - is evaluated. 

 

Therefore it doesn't / shouldn't matter (to us mortals anyhow) the concept of which table (Dim native columns or Dim related columns in Fact) the VALUES() function is seeing.

 

 

No worries 🙂 Yes your description sounds correct to me - within CALCULATE in a physical table's row context, any native or related (1-side) column will have a single value and can be accessed with VALUES 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.