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.
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".