Forum Discussion
ALLSELECTED vs VALUES
Hello everyone,
In a simple way of explanation, what is the different between ALLSELECTED and VALUES ? as I understand correctly and going through all the resources, the "ALL....." is for us to use if we want to alter filter context, means VALUES is not. I still failed to see when actually we should use ALLSELECTED and when we could use VALUES. Because it looks like why there is VALUES after all ?
If I have a slicer consist of my items, then I want to either count how many is selected or if I want to use it in some CALCULATE function, probably filter my FACT table with items which is selected in that slicer, then which one I should use ? again it looks like I should use ALLSELECTED, and I think it is better everytime I want to get the selected items from my slicer, I will always use it, no need to bother about VALUES.
Anyone can help me to understand more on this ? in what case actually we can consider using VALUES ?
Thanks,
It's way more complex than you might ever think. For a full explanation of all the quirks about ALLSELECTED, please refer to this article: The definitive guide to ALLSELECTED - SQLBI
4 Replies
Just based on my understanding, if anyone felt incorrect please point out：
When they are used as table functions,
ALLSELECTED: It returns the table after it has been filtered by an external filter(SLicer, Page filter)VALUES : It returns a list of distinct values of a column (include blank)
When they are used as calculate parameters,
ALLSELECTED: Remove internal filters from tables or columns, but keep external filters
VALUES: Restore filters for columnIt's way more complex than you might ever think. For a full explanation of all the quirks about ALLSELECTED, please refer to this article: The definitive guide to ALLSELECTED - SQLBI
I think how they return the values
Values
This function cannot be used to Return values into a cell or column on a worksheet; rather, you use it as an intermediate function, nested in a formula, to get a list of distinct values that can be counted or used to filter or sum other values.Allselected
The context of the query without any column and row filters.Averagex(Values(Table[Col1]), [Measure])
Will take an Average of the measure after it is grouped at Table[Col1]
while
Averagex(allselected(Table[Col1]), [Measure]) is measure value across all selected values of col1
thank you, this has helped me understand how to use these functions. I was always a little confused.
