Does using fact tblname in all DAX fucntions result in table expansion?

0
votes

Given a model with fact table having multiple dimension tables, Does using the fact tblname in all DAX fucntions result in table expansion?

Example:

SUMX(tblname, expr)

CALCULATE(expr, FILTER(tblname, criteria))

1
powerbidax
what do you mean by table expansion? both SUMX and FILTER are iterators, the difference here is that in first case expr is evaluated for each row of tblName and then summed, while in the second case you change the filter context based on criteria (iterating with FILTER), and pass the result as new filter context in CALCULATE which evaluates expr only for that specific filter context. Which will have the same result for expr=SUM(tblName[column]) (just faster), but can be different for more complex calculations – Stachu
I'm trying to understand whether table expansion occurs. – variable
This may be helpful – Stachu

1 Answers

1
votes

Yes, when a relationship exists, there is an expanded table.

In the first example

SUMX(tblname, expr)

the iterator creates a row context over the tablname. RELATED must be used to access fields in the tblname expanded tables that are not parte of tablename, but reachable through a many to one relationship.

in the second example

CALCULATE(expr, FILTER(tblname, criteria))

the FILTER is an iterator and therefore it works like in the first example.

The table returned by FILTER inside CALCULATE acts as an expanded table, therefore affecting not only the existing filters over tblname, but also the existing filters over the columns that are part of the tblname expanded table.

For instance, assuming to have a model with a table Customers and a table Sales with a one to many relationship between Customers and Sales, in this code

CALCULATE([Sales Amount], FILTER(ALL(Sales), Sales[Quantity] > 2))

the FILTER returns a filtered Sales expanded table that removes any existing filter over Customers table.