Forum Discussion
Community ChampionDAX Head Scratcher
Community Championmarcorusso AlexisOlson Yeah, I don't really have a great deal of control over this particular data model, I was kind of handed what I was handed and asked to make it work. Obviously data model changes can always help things out but I was curious about the DAX for one main reason. From what I have generally read, a lot of the blog articles and other guidance out there basically state "don't use FILTER with CALCULATE". One of the main reasons being performance since FILTER tends to create an extra table, yadda yadda. For example: https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter
However, in this case, FILTER has the exact opposite effect, it greatly improves the speed of the measure instead of slowing it down. This is further intruiging to me because it doesn't make sense to me as to why. In theory, they should both be doing the same thing. Maybe I am thinking about this wrong, but my understanding of using FILTER in CALCULATE is that using FILTER adds to the existing filter context. Not using FILTER in CALCULATE has two outcomes:
- If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
- If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
So, in my way of thinking, within that visual, QuotesContracts[PlanID] is not in the current filter context so both versions of the measure should be doing the same thing, simply adding filter context.
I'm going to try some more experimentation around this so appreciate the thoughts and inputs!!
Solution SageApologies for jumping in on (even hijacking) this thread with my limited knowledge, but it appears that the issue here is related to a problem I was investigating recently.
If so, the issue appears to be related to the well-known fact that CALCULATE implicitly applies an ALL function to the column(s) being passed as the filter parameter(s). What I don't understand is how this is affected when a column from a related table is added to the filter context (e.g. by adding it to the Fields of the visual in question).
Take this simple example, which has two tables with a 1-to-Many relationship between the Genre and Films tables, based on GenreID.
I have also added in the Genre column to the Films table for demonstration purposes.
If you create a simple table visual with Title and Genre (both from the Films table), then both of these measures:
Box Office Dollars (CALCULATE) =
CALCULATE ( SUM ( Films[BoxOfficeDollars] ), Films[Title] > "C" )
and
Box Office Dollars (FILTER) =
CALCULATE (
SUM ( Films[BoxOfficeDollars] ),
FILTER ( Films, Films[Title] > "C" )
)
return identical tables, with no duplication in the Genre column:
However, if we remove the Genre column and instead use the Genre column from the Genre table, then the table visual returned using Box Office Dollars (FILTER) is perfectly correct:
However, that returned using
You can reproduce this behaviour using other functions which employ either implict or explicit use of ALL on the Films table.