Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Unexpected ALL() Behavior

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!
Need Help
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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.

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      Thank you yes, thats true!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]))