Forum Discussion

esuing's avatar
esuing
Icon for Helper I rankHelper I
6 years ago
Solved

Using CALCULATE and ALL yielding unexpected results

This seems like it should be easy, but my calculate function isn't yielding the results I'm looking for so maybe I'm just misunderstanding it's general usage.  I have a dataset that looks like this:

 

 

Pkup Date is coming from a date table, Prod No is coming from a Producer table, Mnfst No is coming from a Manifest table, and Pkup Lbs is just a simple sum function.  I wanted to add a column that would list the total Pkup Lbs for a producer, so I attempted to use this formula:

 

All Lbs = CALCULATE([Pkup Lbs],all(Manifests[Mnfst No]))
 
And here was the result:
 
 
The total (47995) is correct, but it's bringing in every Mnfst No in the table, even when the date/prod no has no relation to it. I would have expected it to only return those Mnfst No records for which there was a valid date/prod no. Performance is obviously awful because it's bringing back the entire Manifest table.  What am I doing wrong? I believe the relation between the tables are bi-directional... is this the issue?  Any help would be appreciated. Thanks.
 
Eric
 
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi esuing ,

     

    We can try to use the following measure to meet your requirement:

     

    All Lbs = IF([Pkup Lbs] <= 0, BLANK(), CALCULATE([Pkup Lbs],all(Manifests[Mnfst No])))

     


    Best regards,

     

4 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi esuing ,

     

    ALL ignores the "Mnfst No", so the measure returns for each "Mnfst No" the same value.

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

    • esuing's avatar
      esuing
      Icon for Helper I rankHelper I
      6 years ago

      Thanks, Marcus... so how can I change my measure so that only the Mnfst No are returned that are related to the data from the other fields (like shown in the first screenshot)? I can put in a visual filter for Pkup Lbs > 0, but the performance is still horrid (takes minutes to resolve). Seems like there should be a way to only bring back the valid records.

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi esuing ,

         

        We can try to use the following measure to meet your requirement:

         

        All Lbs = IF([Pkup Lbs] <= 0, BLANK(), CALCULATE([Pkup Lbs],all(Manifests[Mnfst No])))

         


        Best regards,