Forum Discussion

deeave's avatar
deeave
Helper I
3 years ago
Solved

DAX Measure Error

Receiving error "A single value for column 'modified' in table 'Projects UA2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specif...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    As its a one-to-many you would need to use RELATEDTABLE, e.g.

    Project Active, No Achievement Updates in 30 Days =
CALCULATE (
    COUNTROWS ( 'Projects Data2' ),
    FILTER (
        'Projects Data2',
        VAR MaxModifiedDate =
            MAXX ( RELATEDTABLE ( 'Projects UA2' ), 'Projects UA2'[modified] )
        RETURN
            MaxModifiedDate
                < TODAY () - 30
                && ISBLANK ( RELATED ( 'Projects PA2'[AchievementDate] ) )
                && 'Projects Data2'[projectstatus] = "Active"
                && NOT ISBLANK ( 'Projects Data2'[projectnumber] )
    )
)
deeave's avatar
deeave
Helper I
3 years ago

Thank you! 

 

It comes back with " The column 'Projects UA2[modified]' either doesn't exist or doesn't have a relationship to any table available in the current context." 

 

When I type in the name, it is not showing me all the tables in the workspace, only one that is an excel upload.  

johnt75's avatar
johnt75
Super User
3 years ago

What is the relationship between 'Projects UA2' and 'Projects Data2' ?

  • deeave's avatar
    deeave
    Helper I
    3 years ago

    The relationships between Projects UA2 and Projects Data2 is below:

    • Projects Data2 to Projects UA2 (ProjectID One to Many)
    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      As its a one-to-many you would need to use RELATEDTABLE, e.g.

      Project Active, No Achievement Updates in 30 Days =
CALCULATE (
    COUNTROWS ( 'Projects Data2' ),
    FILTER (
        'Projects Data2',
        VAR MaxModifiedDate =
            MAXX ( RELATEDTABLE ( 'Projects UA2' ), 'Projects UA2'[modified] )
        RETURN
            MaxModifiedDate
                < TODAY () - 30
                && ISBLANK ( RELATED ( 'Projects PA2'[AchievementDate] ) )
                && 'Projects Data2'[projectstatus] = "Active"
                && NOT ISBLANK ( 'Projects Data2'[projectnumber] )
    )
)