Static Segmentation Question

[Anonymous]

Hi - I was reading the post about this technique from https://www.daxpatterns.com/static-segmentation/ and am hoping someone could explain the syntax of the FILTER function. 

Specifically, why is it that 'Ranges' is the table in FILTER and not 'Sales' which is the table with the rows that we're iterating over? Basically, my question is why is the syntax not: 

FILTER(
           Sales,
           Sales[Price] >= Ranges[Min Price]&&
           Sales[Price] < Ranges[Max Price]

Thanks so much!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[TomMartens]

Hey,

you have to consider what the calculation want to achieve ...

The fact "sales" table should be able to be sliced by the price range.

This requires a "calculated column" in the fact table that contains a value from the range table.

Due to the fact that the calculation "happens" in the fact table, the values of the current row from sales table can be usesd within the condition of the FILTER part.

This in combination with the iteration over Ranges (FILTER is an iterator) allows to use the references to both tables without any Aggregation function).

An iteration over the Sales table would also result to an error, because the Ranges reference can not be resolve to a single value.

Another smaller detail (one of the mysterious of VALUES), if the returning table just contains one row, this row will be transformed into a scalar (a single value) if needed. This is necessary because a calculated column expects a value and not a table. For this reason the usage of VALUES also "detects" errors in the configuration of the ranges, overlapping,

Guess, this is one the DAX statements, that despite it's less number of components, raises a lot of questions (at least it should, if one tries to fully understand DAX).

Hopefully this answer adds some additional insight.

Regards,

Tom