Forum Discussion
Help understanding measure
Hello
I have been trying to understand a calculated column that my predecessor wrote.
The part i'm struggling to understand is the Filter All bit.
1) As its a calculated column why would you need to remove filters?
2) All with a column parameter returns all the unique values right?, I don't understand why you would use all here and not just Filter(All(Activities)
Latest submission date =
CALCULATE(
MAX(Activities[Submission date]) ,
FILTER ( ALL ( 'Activities'[LearnerID]) , 'Activities'[LearnerID] = LearningPlan[LearnerID])
)
Hi, SBR1D
Thank you for your reply. ALL is used as a function to return a table when used in the Filter function.
The Values function returns a column without duplicate values.You may not have much impact on your results when using them in calculated columns, but they are usually used in measures and they play various roles.
Values can only be used as table functions. ALL can be used as both a filter and a table function. When we need to clear the filter in some cases, we usually put it in the second parameter of calculate.If you don't understand it, it's normal. I suggest you read the SQL BI article, which describes the differences and calculation logic in detail.
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Using ALLEXCEPT vs ALL + VALUES - SQLBI
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
SBR1D ,
- Even in calculated columns, you might need to clear filters to ensure you're getting values from all rows, not just the current row context
- ALL('Activities'[LearnerID]) is more efficient as it only removes filters from one column
FILTER(ALL(Activities)) would remove filters from the entire table, which is unnecessary here
Using just the column is faster and uses less memory
// Less efficient
Latest = FILTER(ALL(Activities), condition)// More efficient
Latest = FILTER(ALL(Activities[Column]), condition)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInThanks for your reply.
Why do we need to clear filters in learningplan? We want to filter the learnerid in Activities by the learnerid in LearningPlan.
SBR1D ALL basically remove any filters applied. Coming to your point 2, Filter(All(Activities) it scans enter table with all columns which is not a good practice and lead to performance issue. And if you mention specific column inside ALL so it removes filter from that particular table.
Putting it all together, your DAX formula calculates the maximum submission date for the learner specified in the LearningPlan by filtering the Activities table to only include records where the LearnerID matches. The use of ALL ensures that the filter context for LearnerID is ignored, allowing you to compare across all learners while still isolating the results to the specified learner.
Thanks for your reply but one thing is confusing me.
' The use of ALL ensures that the filter context for LearnerID is ignored, allowing you to compare across all learners while still isolating the results to the specified learner.'
Why would we want to ignore filter context for learnerid? We want to find the learnerid in activities that matches learnerid in learningplan right?
Why would the filter need to be removed for that?
so this line is returning all Activieis[LearnerID] where Activieis[LearnerID] = LearningPlan[LearnerID] ?
FILTER ( ALL ( 'Activities'[LearnerID]) , 'Activities'[LearnerID] = LearningPlan[LearnerID]) )
Thank you Tahreem24
Hi, SBR1D
What the computed columns provide us is row context:
When we add the calculate function to this max function, the current context will become a filter context:
Here is the understanding of filter context:
The rows filtered out under these three row filters will be passed to the max function to calculate the maximum value: 10
This further confirms that under the three column filter contexts, the maximum value is: 11
FILTER is an iterative function that returns a table. ALL ('Activities'[LearnerID]) will ignore the filter on this column, and LearningPlan[LearnerID] returns the LearnerID of the current row. Take my sample data as an example:
In this case, if I have two other columns as filters: status, value, and 'Merge1'[Table 2.Task]='Merge1'[Table 2.Task] added in the filter function). With the combined effect of these filters, a table will be returned, and the max function will be calculated based on this table.
If ALL table is selected, all rows will be returned, and then for these rows, 'Merge1'[Table 2.Task]='Merge1'[Table 2.Task] will be selected and returned to max for calculation.
You can learn more about how they work and how they are calculated in this article from SQLBI.
How CALCULATE works in DAX - SQLBI
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Filter Arguments in CALCULATE - SQLBI
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Firstly, thank you so much for going in to this level of detail but i still don't understand why we need to clear the filter on the line below , and then we reapply that filter? That does not make sense to me.
ALL(Merge[Table2.Task])
When you FILTER Table2 why do we need to remove the filter from the ID, surely we want to FILTER the 2nd table by the ID of the row in the 1st table, so why remove the filter on the ID?
Hi, SBR1D
Thank you for your reply. ALL is used as a function to return a table when used in the Filter function.
The Values function returns a column without duplicate values.You may not have much impact on your results when using them in calculated columns, but they are usually used in measures and they play various roles.
Values can only be used as table functions. ALL can be used as both a filter and a table function. When we need to clear the filter in some cases, we usually put it in the second parameter of calculate.If you don't understand it, it's normal. I suggest you read the SQL BI article, which describes the differences and calculation logic in detail.
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Using ALLEXCEPT vs ALL + VALUES - SQLBI
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Furthermore if i add another computed column with the following code i get exactly the same result as my original computed column without the need of having to clear any filter on Activities[LearningPlanID].
This further adds confusion as to why need to clear the filter on Activities[LearningPlanID]
CALCULATE( MAX(Activities[Submission date]) , FILTER ( VALUES ( 'Activities'[LearnerID]) , 'Activities'[LearnerID] = LearningPlan[LearnerID]) )
