Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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


Anonymous
Not applicable

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
Super User
Super User

@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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors