Home Forums Power Pivot Calculate shows no error but results are incorrect

Tagged: , ,

This topic contains 2 replies, has 2 voices, and was last updated by  tomallan 2 years, 11 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #2386

    fnangys
    Participant
    • Started: 10
    • Replies: 19
    • Total: 29

    Hi All,

    The issue is that this calculate formula shows no errors, but the results is incorrect:

    = CALCULATE([Total Amount],fSales[Weekday]>1 || fSales[Weekday]<7)

    Correct formula is:

    CALCULATE([Total Amount],fSales[Weekday]>=2 && fSales[Weekday]<=6)

    In fact, the incorrect formula doesn’t apply any filter at all, and just brings[Total Amount].

    I would like to understand what is happening and why an error messages is not display.

    Regards,

    Fernando

    #2389

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    Hi Fernando,

    Calculate is doing what you told it to do. The double vertical bars mean OR in a computer logical way (if either criteria is true, the test will succeed), not in a way how “or” can be used in everyday speech.

    Your formula

    = CALCULATE([Total Amount],fSales[Weekday]>1 || fSales[Weekday]<7)

    is telling the formula engine that you want the [Total Amount] whether [Weekday] is greater than 1 or whether it is less than 7. That means to the formula engine the same as: give me [Total Amount] whether the [Weekday] is 1, 2, 3, 4, 5, 6 or 7 (because any one of those days is true for at least one of the criteria).

    If you replace your double vertical bar with a double && which means AND in a computer logical way (only when both criteria are true will the test succeed) as:

    Non-Weekend Sales := CALCULATE([Total Amount],fSales[Weekday]>1 && fSales[Weekday]<7)

    you will see different results.

    If you do not already have the book, "DAX Formulas for Power Pivot" by Rob Collie, I recommend it highly (I almost always have it within arm's reach when programming DAX).

    I also highly recommend this website's Comprehensive Power Pivot course (self-paced video training), which you can learn about here:

    http://www.powerpivotpro.com/self-paced-online-video-training/

    I recommend the course above all others.

    #2390

    fnangys
    Participant
    • Started: 10
    • Replies: 19
    • Total: 29

    Hi Tomallan,

     

    Your answer makes perfect sense.

     

    I do have the book! I read it already but now I need more practice as you can see 🙂

     

    Thank for your answer.

     

    Regards,

     

    Fernando

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.