I have a measure that was working previously, but is no longer working after a data refresh. The following function:
Requests Completed by Month = COUNTX(FILTER('Production Approval Workflow', 'Production Approval Workflow'[Date_Prod_Val_Review].[MonthNo]),[Title])
Is producing this error:
Column reference to 'Date_Prod_Val_Review' in table 'Production Approval Workflow' cannot be used with a variation 'MonthNo' because it does not have any.
The column is a defined as a date datatype. Are the null dates the issue? What I am trying to do is get a monthly count of completed items [Title].
Any thoughts or suggestions?
First the error has nothing to do with Null dates.
And I can repro your issue. If I disable the Time intelligence (Auto Date/Time) option in File/Options and Settings/Options/Data Load. The error shows up.
Thereby, please verify your Time intelligence setting.
Thanks for the response - but the Time Intelligence was selected (it had the checkmark).
I unchecked Time Intelligance and a bunch of visualizations and measures that were working then became broken.
Any other suggestions?
One possibility. Have you created any relationships on this table 'Production Approval Workflow'? Relationship can affect date hierarchy. Please verify this.
Do I have relationships on that table? Only about 9.
What type of relationship should I be looking at as the source of my problem?
Ok. It's hard to say. Could you please share us your pbix file with OneDrive if possible? So that I can know your actual situation and make some proper tests.
If you can't share us the file. I would suggest you check all the relationships one by one. Start from date columns.
You could try to change the formula to:
Requests Completed by Month = COUNTX(FILTER('Production Approval Workflow', MONTH('Production Approval Workflow'[Date_Prod_Val_Review]),[Title])
So in stead of: Table[date.column].[MonthNo] --> MONTH(Table[date.column])
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!