cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
comish4lif Regular Visitor
Regular Visitor

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

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

Measure Completed by MonthNo.png

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

 

Date_Prod_Val_Review.png

 

Any thoughts or suggestions?

6 REPLIES 6
v-xjiin-msft Super Contributor
Super Contributor

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

Hi @comish4lif,

 

First the error has nothing to do with Null dates.

 

11.PNG

 

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.

 

22.PNG33.PNG

 

Thereby, please verify your Time intelligence setting.

 

Thanks,
Xi Jin.

comish4lif Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

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 Regular Visitor
Regular Visitor

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

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?

 

Relationships.png

v-xjiin-msft Super Contributor
Super Contributor

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

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.

Anonymous
Not applicable

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,334)