Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
eloomis
Advocate II
Advocate II

Filter on multiple columns in a measure

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]
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @eloomis 

try like:

OverdueProjects =
COUNTROWS(

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

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @eloomis 

try like:

OverdueProjects =
COUNTROWS(

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

Thanks very much!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors