Forum Discussion

Johan's avatar
Johan
Advocate II
8 years ago
Solved

All() dax not working

I know this issue has been raised before in the forum, but still I cannot get it to work. I've got the most simple example: 3 tables: products, orders and calendar. products have a status there a...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Johan,

     

    Create a table with status:

     

    Status

    Open
    Planned

    Production

     

    Then make an inactive relationship between Status table and Products Table:

     

    Change your two measure to the following code:

    Ordered last year =
CALCULATE ( SUM ( Orders[Ordered qty.] ); 'Calendar'[YearsAgo] = 1 )

Ordered this year =
CALCULATE (
    SUM ( Orders[Ordered qty.] );
    'Calendar'[YearsAgo] = 0;
    USERELATIONSHIP ( 'Status'[Status]; Products[Status] )
)

    Now use the Status table on your slicer and everything should work as you want:

     

    Regards,

    MFelix

Johan's avatar
Johan
Advocate II
8 years ago

Thanks a lot MFelix, this really helped.

I don't understand quite yet why the relation must be inactive in order to have the all() condition working. But it's working,  thanks again!

 

Johan.

MFelix's avatar
MFelix
Super User
8 years ago

Hi Johan,

 

The UUSERRELATIONSHIP is not making the ALL to work. When you do measure these are based on context so if you have on your visuals year / descriptions whatever type of aggregators the measures will work accoding to those context, as you put in your first post the use of ALL will take the filters out and surpass the context of the visual, however you are placing in your visual 2 conflicting measures.

 

When you place the status of the project and on the table and make it interact with the slicer but then you add a measure that gets all the results the slicer will not be overwirtten since the values come from the same table so you are getting ALL the values from a subset of values and not from the full data.

 

Creating a separeted table with status you are able to make that the original data is presented and applying the slicer only to the related information in this case, the PY is not related with status (that the inactive part of the relationship) and the current year is activated from the slicer that uses USERRELATIONSHIP to activate the relatioship and use context.

 

You are using the new table only for slicing mode not for making part of the table that's so the interaction between the two do not overwrites for PY.

 

Regards

MFelix