CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9664

DAX - ALLEXCEPT vs ALL+VALUES

In this video: https://youtu.be/teYwjHkCEm0

It is said that ALLEXCEPT is not the same as ALL and VALUES. Further he says that - for filter context having CountryRegion, it makes no difference. But if there was a filter on Continent then the both are not equivalent. What exactly is happening in case of Continent?

enter image description here

Upvotes: 0

Views: 438

Answers (2)

Ahmed Hafidh
Ahmed Hafidh

Reputation: 21

In simple words, ALL(tbl) removes filters, while VALUES([C1]) create a table filter that retains back the values of [C1] that is why this filter exists all the time unlike ALLEXCEPT (which is basically ALL) can't produce a filter unless it exists in the filter context

Upvotes: 0

sergiom
sergiom

Reputation: 4877

When the only existing filter is on Customer[Continent] then the

ALLEXCEPT( Customer, Customer[CountryRegion] )

simply removes all the existing filters from the Customers table.

The

ALL( Customer ),
VALUES( Customer[CountryRegion] )

first saves the VALUES( Customer[CountryRegion] ), that are in the subset of Customer table resulting from the filter on Continent (that's to say the CountryRegions of the currently selected continents), then ALL( Customer ) filter modifier removes the existing filter on the whole Customer table and after that the VALUES( Customer[CountryRegion] ) filter that was evaluated before is applied. The result is that the CountryRegion is "Cross-filered" by the Continent.

this is also explained in this article Using ALLEXCEPT Versus ALL and-VALUES

Upvotes: 1

Related Questions