Column reference... cannot be used with a variation 'MonthNo' because it does not have any

[comish4lif]

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?

[Anonymous]

Hi @comish4lif

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])

[v-xjiin-msft]

Hi @comish4lif,

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,
Xi Jin.

[comish4lif]

Xi Jin, 

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?

[v-xjiin-msft]

Hi @comish4lif,

One possibility. Have you created any relationships on this table 'Production Approval Workflow'? Relationship can affect date hierarchy. Please verify this.

Thanks,
Xi Jin.

[comish4lif]

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?

[v-xjiin-msft]

Hi @comish4lif,

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.

Thanks.
Xi Jin.