Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
comish4lif
Helper II
Helper II

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
Anonymous
Not applicable

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
Solution Sage
Solution Sage

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.

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?

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.

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.