Forum Discussion
RELATED vs FILTER vs CALCULATE
I have the following :
- Fact table has many rows linked to a single row in Dim (1-to-many relationship)
- Dim has a column ColB, and you want to filter Fact where 'Dim'[ColB] = "AA"
and the following measures, I want to understand how the context works in each case and what are the differences ?
M1 = CALCULATE(COUNT('Fact'[ColId]), 'Dim'[ColB] = "AA")
M2 = CALCULATE(COUNT('Fact'[ColId]), FILTER('Dim', 'Dim'[ColB] = "AA"))
M3 = CALCULATE(COUNT('Fact'[ColId]), RELATED('Dim'[ColB]) = "AA")
In your 1st measure, the 'Dim'[ColB] = "AA" is evaluated as a boolean filter inside CALCULATE, meaning that the filter will be applied 'Dim'[ColB] = "AA" at the model level so it will automatically filter all related rows in Fact because of the 1-to-many relationship.
In the second measure, theFILTER('Dim', 'Dim'[ColB] = "AA") creates a table of filtered rows from Dim, which is then passed to CALCULATE and since Fact is related to Dim, filtering Dim also affects Fact.
In the last measure, theRELATED('Dim'[ColB]) only works in row context, not filter context.
In other words, RELATED is used when iterating inside Fact (like in a calculated column), pulling values from Dim to Fact. In this case, CALCULATE does not create a row context, so RELATED won't work properly.Hope I helped 🙂
1 Reply
In your 1st measure, the 'Dim'[ColB] = "AA" is evaluated as a boolean filter inside CALCULATE, meaning that the filter will be applied 'Dim'[ColB] = "AA" at the model level so it will automatically filter all related rows in Fact because of the 1-to-many relationship.
In the second measure, theFILTER('Dim', 'Dim'[ColB] = "AA") creates a table of filtered rows from Dim, which is then passed to CALCULATE and since Fact is related to Dim, filtering Dim also affects Fact.
In the last measure, theRELATED('Dim'[ColB]) only works in row context, not filter context.
In other words, RELATED is used when iterating inside Fact (like in a calculated column), pulling values from Dim to Fact. In this case, CALCULATE does not create a row context, so RELATED won't work properly.Hope I helped 🙂