Filter on multiple columns in a measure

Anonymous — Thu, 29 Dec 2022 22:08:15 GMT

I am new to DAX and appreciate your patience if this is a mundane question. Thank you in advance.

I'm writing two measures, one to filter for projects that were overdue when completed, and one to filter for projects completed before their deadline. These measures will be used to create a stacked column chart of projects completed each quarter throughout the year.

I will use the example of overdue projects. This is my measure:

OverdueProjects = FILTER(Projects, Projects[ProjectStatus] = "Done" && Projects[TimeDiff] < 0 )

I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

In this example, Projects[TimeDiff] refers to another measure that is calculating the difference between the promised execution time for the project, and the actual execution time, so if TimeDiff is negative, it indicates the project is late. I'm not sure if referring to another measure might be what is causing the issue.

Code to evaluate TimeDiff:

Time Promised = --Time between date created and promise date CALCULATE( INT( SUMX(Projects, Projects[PromiseDate] - Projects[StartDate]) ), Projects[ProjectStatus] = "Done" && Projects[PromiseDate] <> BLANK() ) Time Delivered = -- Time between date created and date completed CALCULATE( INT( SUMX(Projects, Projects[DoneDate] - Projects[StartDate]) ), Projects[ProjectStatus] = "Done" ) TimeDiff = [Time Promised] - [Time Delivered]

Re: Filter on multiple columns in a measure

FreemanZ — Thu, 29 Dec 2022 22:57:20 GMT

hi Anonymous

try like:

OverdueProjects =
COUNTROWS(

FILTER(Projects,
Projects[ProjectStatus] = "Done"
&& [TimeDiff] < 0
))

Re: Filter on multiple columns in a measure

Anonymous — Fri, 30 Dec 2022 13:49:30 GMT

Thanks very much!

topic Filter on multiple columns in a measure in DAX Commands and Tips

Filter on multiple columns in a measure

Re: Filter on multiple columns in a measure

Re: Filter on multiple columns in a measure