DAX Measure Error

[deeave]

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 specifying an aggregation such as min, max, count, or sum to get a single result."

Confirmed it is a date field and the field exists.

Project Active, No Achievement Updates in 30 Days = CALCULATE (

    COUNTROWS ( 'Projects Data2' ),

    FILTER (

        'Projects Data2',

        'Projects UA2' [modified] < TODAY() - 30

            && ISBLANK ( 'Projects PA2'[AchievementDate] )

    ),

    'Projects Data2'[projectstatus] = "Active",

    NOT ISBLANK ( 'Projects Data2'[projectnumber] )

)

[deeave]

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]

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

[deeave]

The relationships between Projects UA2 and Projects Data2 is below:

• Projects Data2 to Projects UA2 (ProjectID One to Many)

[johnt75 · Accepted solution]

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]

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