Forum Discussion
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 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] )
)
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] ) ) )
5 Replies
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] ) ) )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.
