Forum Discussion
Help understanding measure
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.
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.
I totally get difference between VALUES and ALL in measures. I just do not understand why ALL was used in the calculated column in my OP. The code was from my predecessor and i was trying to work out why she had used it. It seems it wasn't really needed though.
Thanks for your help.