Unexpected ALL() Behavior

[Anonymous]

Hi everyone,

I am a DAX beginner and I have been having an issue understanding the ALL() function.

Take for example this scenario from the titanic dataset:

So what I would like to display is:

1. Port of embarkation

2. Sex

3. Survived

4. Total number of people that boarded the boat at each port, who survived, irrespective of sex.

So my DAX measure for the variable "Total survived passengers embarked at port" is 

Total survived passengers embarked at port = CALCULATE(sum(train[Survived]), all(sex[Sex]))

However as you can see in the above image, the all(sex[sex]) did not work...

Here is an image of the data model:

What I expected to see was something like this:

W was calculated as follows: 

W = CALCULATE(sum(train[Survived]), all(train[Sex]))

So my question is basically, how come it worked when I used the fact table in the DAX expression and it did not work when I used the dimension table in the DAX.

Thank you for your help!

[Anonymous]

Hi everyone, 

Thank you for all your answers, they were really helpful.

However, I also found out that if your dimension table contains an "order" column, you need to include the order with the ALL() expression as well for it to work properly. 

For instance, if the sex table had a column that looks at the order of male and female to display in a certain way, you would want the expression to be as follows:

Total survived passengers embarked at port = CALCULATE(sum(train[Survived]), all(sex[Sex],sex[Order]))

[harshnathani]

Hi @Anonymous ,

https://radacad.com/how-to-use-all-in-a-dax-expression-in-power-bi

Explains the usage of ALL().

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[jdbuchanan71 · Accepted solution]

@Anonymous 

In your example did you pull the sex column into the visual from the Sex table or the Train table?  You should pull it from the Sex table then I believe your view will work.

[Anonymous]

Thank you yes, thats true!