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, 6 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: 9
• Replies: 2244
• Total: 2253

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.

= 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,

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