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] )
    )
)
johnt75's avatar
johnt75
Super User
3 years ago

Because you're trying to access columns from a different table you need to use the RELATED function. Also, you can combine all the filter conditions into one,

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