ALLSELECTED vs VALUES

[admin_xlsior]

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,

[jameszhang0805]

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 column

[Anonymous · Accepted solution]

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

[amitchandak]

@admin_xlsior ,

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

[rfigtree]

thank you, this has helped me understand how to use these functions. I was always a little confused.