Forum Discussion
Calendar Table returning error: The expression specified in the query is not a valid expression
Try using the CALENDARAUTO() function, that will automatically scan all the date columns in the model and should return a valid table even in the presence of blanks.
Super UserHi Previsible
The issue stems from the possibility that your DateofQA column in the Deskside QA table has blank or invalid date values, causing MIN() or MAX() to return BLANK, which is not allowed as input for the CALENDAR function.
Date =
VAR MinDate =
CALCULATE(
MIN('Deskside QA'[DateofQA]),
NOT(ISBLANK('Deskside QA'[DateofQA]))
)
VAR MaxDate =
CALCULATE(
MAX('Deskside QA'[DateofQA]),
NOT(ISBLANK('Deskside QA'[DateofQA]))
)
RETURN
IF(
ISBLANK(MinDate) || ISBLANK(MaxDate) || MinDate > MaxDate,
CALENDAR(TODAY(), TODAY()), // Fallback to avoid errors
CALENDAR(MinDate, MaxDate)
)
By excluding blank values and handling invalid ranges, you ensure CALENDAR gets proper inputs, preventing errors.
The fallback mechanism provides a temporary calendar if the input data is problematic, allowing you to fix the source data later.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
I agree that it's possible the tables have empty entries, maybe a sharepoint list issue (there are no actual empty entries when I look however and overwriting the data doesn't resolve it. )
When I use the DAX in your suggestions I still return an error:
"The expression specified in the query is not a valid table expression."
