cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin_xlsior
Post Prodigy
Post Prodigy

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,

1 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

View solution in original post

4 REPLIES 4
jameszhang0805
Resolver IV
Resolver IV

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


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

View solution in original post

amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors