topic Re: ALLSELECTED vs VALUES in DAX Commands and Tips

ALLSELECTED vs VALUES

admin_xlsior — Mon, 22 Feb 2021 09:22:14 GMT

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,

Re: ALLSELECTED vs VALUES

amitchandak — Mon, 22 Feb 2021 09:28:53 GMT

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

Re: ALLSELECTED vs VALUES

jameszhang0805 — Mon, 22 Feb 2021 09:38:24 GMT

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

Re: ALLSELECTED vs VALUES

Anonymous — Mon, 22 Feb 2021 11:56:02 GMT

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

Re: ALLSELECTED vs VALUES

rfigtree — Mon, 01 Mar 2021 08:49:52 GMT

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