Forum Discussion

comish4lif's avatar
comish4lif
Icon for Helper II rankHelper II
8 years ago

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

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?

Need Help

6 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      comish4lif
      Icon for Helper II rankHelper II
      8 years ago

      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's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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