IF STATEMENT ERROR

[zraopingm]

Hello, 

Image 1 is working; image 2 is a copy of same measure with a new name. but why I have this error as displayed in Image 2? same data model ( DAXsql lesson), see image3. 

Thank you 

Zoey

[SamsonTruong · Accepted solution]

Hi @zraopingm ,

This is due to your working calculation being a calculated column, and your non-working one being a measure. Calculated columns are evaluated per each row in your base table, so it calculates without any issues. However, how the measure calculates varies on how it is aggregrates in your visual. Due to this it could pull in multiple records for each date, not knowing which one to compare. To fix your measure, you can apply a max() around your dates so it only pulls in one date. However, as a note, this may give a different than expected output depending on your use case.

Here is the updated measure:

Delivery State1 =
IF (
    MAX(Sales[Delivery Date]) - MAX(Sales[Order Date]) >= [Avg All Delivery],
    "Above Average",
    "Below Average"
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[zraopingm]

I got you!! thank you Zoey

[SamsonTruong]

Glad to hear it worked!