DAX: The very long message "The semantic error or perhaps this error or this error.."

One day, I was working with a DAX expression in PowerPivot (Excel 2013) and I got the following error:

"Semantic error: The value for column 'Date' in Table 'dimCalendar' cannot be determined in the current context. Check that all columns in referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation -- such as sum, average, or count-- on that column. The column does not have a single value, it has many values, one for each row in the table, and now row has been specified."

Okay.. I prefer shorter messages normally and this error message popups in a tooltip and this tooltip disappears after a couple seconds. You have to move the cursor back and again and then you have a couple of seconds to read the tooltip again, before it disappears again. Well, I have written it all out in this blogpost and so you can read it at ease with a cup of coffee.

This was this the DAX expression that generated the error:

SumNetRevenue:=IF(MONTH(DimCalendar[Date]) = MONTH(NOW());
  SUM(FactCurrentMonthSales[NetRevenue]))
 

Now, the second part of the error is the interesting part and that said that there are different granularities in the expression. The SUM is on filtercontext but, the DimCalendar[Date] is on row level. I solved it with this DAX Expression:

SumNetRevenue:= IF(MONTH(MAX(DimCalendar[Date])) = MONTH(NOW()) ;
                 SUM(FactCurrentMonthSales[NetRevenue]))

Conclusion

Don't use very very long error messages in a tooltip ;-)

Greetz
Hennnie