Forum Discussion

Johan's avatar
Johan
Icon for Advocate II rankAdvocate 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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi Johan,

 

Believe that your problem is not DAX related, but visual related, you need to select the Ediut interactions and remove the slicer interaction from the table with order from last year.

 

 

Regards

MFelix

  • Johan's avatar
    Johan
    Icon for Advocate II rankAdvocate II
    8 years ago

    Thanks for taking the time to help.

    Edit interactions can help is some situations, but not all.

     

    What if I want to display them in 1 table?

     

    Slicer filter = Planned  (should only apply to this year)

      This yearLast Year
    MercedesPlanned20 
    FordPlanned25 
    AudiClosed 30
    ToyotaClosed 35
    FerrariClosed 40
    LadaClosed 45

     

    Kind regards,

    Johan

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      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
        Icon for Advocate II rankAdvocate 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.