DAX Filter context

Question

I am wondering what the difference is between these two DAX expressions which each return what I need:

(1) =calculate([PctMkt], FILTER ( ALL ( BondDim), BondDim[Quality] = "HY" ))

(2) =calculate([PctMkt], ALL(BondDim), BondDim[Quality] = "HY" )

Does the DAX engine make these two expressions equivalent? Is (2) just short hand for (1)? Also, would (1) calculate more quickly? Trying to make sure that I don't cause problems in the pivot table by "hacking" together calculated measures. These measures allow the user to drilldown to a lower grain while keeping higher level data in context of the pivot table.

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-30T03:44:45

A simpler, similar question is well-known.

CALCULATE (
    [PctMkt],
    BondDim[Quality] = "HY"
)

is the shortened equivalent of

CALCULATE (
    [PctMkt],
    FILTER (
        ALL ( BondDim[Quality] ),
        BondDim[Quality] = "HY"
    )
)

Your formulas need a bit more thought.

When ALL is used as an argument of CALCULATE, it only removes filters (like REMOVEFILTERS) rather than acting as a table expression.

When ALL ( < table > ) is used as an argument of FILTER, it is necessarily a table expression.

The first link I gave gives a detailed example of how this distinction can make an important difference. I'll provide another example here:

Suppose MarketDim has a one-to-many relationship with BondDim (and is not bidirectional) on the ID column from each and the tables are as follows:

  BondDim          MarketDim

Quality   ID       ID    Pct
-------------      ----------
HY        1         1    5%
VY        1         2    10%
XY        2         3    20%

Let's suppose [PctMkt] := MAX ( MarketDim[Pct] )

Then, in your first formula (1), the FILTER argument is the following table:

Quality  ID
-------------
HY       1

Thus, the result of (1) can only be 5% (or blank) since ID = 1 is the only option.

In your second formula (2), there are no table arguments since ALL ( BondDim ) is only removing filters and BondDim[Quality] = "HY" only acts on a single column. Without table arguments, since MarketDim filters BondDim but not vice versa (since it's a single direction relationship), neither of these column filter arguments has any effect on the measure I've defined (it might on your actual measure though).

Thus, the result of (2) is the same as just CALCULATE ( [PctMkt] ), which will be 20% unless there is filtering on MarketDim to exclude ID = 3.

Note: This simplification for (2) does not hold if [PctMkt] operates on BondDim columns or any table columns that are filtered by BondDim in your model. I.e. the filters propagate "downstream" across relationships but not "upstream".

DAX Filter context

2 Answers