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 are 2 measures: orders this year, orders last year.

When filtering on status in the slicer, this should only be done for orders this year.

We want to see all orders of last year, regardless of the product status. That simple. 

 

Yet when I use the dax

Ordered last year = CALCULATE(sum(Orders[Ordered qty.])
;'Calender'[YearsAgo]=1
;ALL(Products[Status]))

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It does not display the products sold last year.

 

Actually, in my real pbix (I'm using an example here) it's not even showing a total for the 'last year' measure.

 

Hope anyone has a suggestion.

Thanks,

Johan.

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

5 Replies

  • MFelix's avatar
    MFelix
    Super 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
      Advocate 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
        Super 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