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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
PC2022
Helper III
Helper III

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

I am trying to identify

1. for < than 100% complete milestones, it needs to return the first late milestone task by finish date and a status string indicating which task is late and by how many days.

2. for =100% complete, it needs to return the last milestone and say "finished on time"

 

DAX below gives error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".

 

FirstLateMilestoneTask =
VAR TodayDate = TODAY()
VAR LateTasks =    FILTER( msdyn_projecttask, msdyn_projecttask[Schedule Status] = "Off Schedule" &&  msdyn_projecttask[Required Tasks.Flag] = "y" &&  msdyn_projecttask[% Complete] < 1 &&  NOT(ISBLANK(msdyn_projecttask[Baseline Finish])))
VAR FirstLateTask =    TOPN(1, LateTasks, msdyn_projecttask[Baseline Finish],   ASC)
VAR TaskName =    MAXX(FirstLateTask, msdyn_projecttask[Project Task Name])
VAR FinishDate =    MAXX(FirstLateTask, msdyn_projecttask[Finish])
VAR FinishDaysLate =     DATEDIFF(FinishDate,TODAY(),DAY)

VAR CompletedTasks =    FILTER( msdyn_projecttask,  msdyn_projecttask[Schedule Status] <> "Off Schedule" &&  msdyn_projecttask[Required Tasks.Flag] = "y" && msdyn_projecttask[% Complete] = 1 &&   NOT(ISBLANK(msdyn_projecttask[Baseline Finish])))
VAR LastCompletedTask =    TOPN(1, CompletedTasks,  msdyn_projecttask[Finish],  DESC)
VAR BaselineCompletedTask = MAXX(LastCompletedTask, msdyn_projecttask[Project Task Name])
VAR BaselineFinishDate = MAXX(LastCompletedTask, msdyn_projecttask[Baseline Finish])
VAR BaselineFinishDaysLate = DATEDIFF(BaselineFinishDate, MAX(msdyn_projecttask[Finish]),DAY)

RETURN
    SWITCH(TRUE(),
        NOT ISBLANK(TaskName),
        TaskName & " is " & FinishDaysLate & " days past the expected completion date", BLANK(),
        BaselineFinishDaysLate = 0, LastCompletedTask & " finished on time"  )
2 ACCEPTED SOLUTIONS
Ilgar_Zarbali
Most Valuable Professional
Most Valuable Professional

This error shows up because you're trying to use a table (like what TOPN returns) where Power BI is expecting just one value—like when you're combining text or checking a condition.

You've already used MAXX(...) to get the task name and finish date from the filtered table. So instead of using LastCompletedTask (which is still a table), you should use the variable that holds the actual task name.
Just update this line:
LastCompletedTask & " finished on time"
to

BaselineCompletedTask & " finished on time"

The corrected DAX formula:

FirstLateMilestoneTask =
VAR TodayDate = TODAY()

-- Tasks that are late
VAR LateTasks =
    FILTER (
        msdyn_projecttask,
        msdyn_projecttask[Schedule Status] = "Off Schedule"
            && msdyn_projecttask[Required Tasks.Flag] = "y"
            && msdyn_projecttask[% Complete] < 1
            && NOT ISBLANK ( msdyn_projecttask[Baseline Finish] )
    )
VAR FirstLateTask =
    TOPN ( 1, LateTasks, msdyn_projecttask[Baseline Finish], ASC )
VAR TaskName =
    MAXX ( FirstLateTask, msdyn_projecttask[Project Task Name] )
VAR FinishDate =
    MAXX ( FirstLateTask, msdyn_projecttask[Finish] )
VAR FinishDaysLate =
    DATEDIFF ( FinishDate, TodayDate, DAY )

-- Tasks that are done
VAR CompletedTasks =
    FILTER (
        msdyn_projecttask,
        msdyn_projecttask[Schedule Status] <> "Off Schedule"
            && msdyn_projecttask[Required Tasks.Flag] = "y"
            && msdyn_projecttask[% Complete] = 1
            && NOT ISBLANK ( msdyn_projecttask[Baseline Finish] )
    )
VAR LastCompletedTask =
    TOPN ( 1, CompletedTasks, msdyn_projecttask[Finish], DESC )
VAR BaselineCompletedTask =
    MAXX ( LastCompletedTask, msdyn_projecttask[Project Task Name] )
VAR BaselineFinishDate =
    MAXX ( LastCompletedTask, msdyn_projecttask[Baseline Finish] )
VAR ActualFinishDate =
    MAXX ( LastCompletedTask, msdyn_projecttask[Finish] )
VAR BaselineFinishDaysLate =
    DATEDIFF ( BaselineFinishDate, ActualFinishDate, DAY )

RETURN
    SWITCH (
        TRUE (),
        NOT ISBLANK ( TaskName ),
            TaskName & " is " & FinishDaysLate & " days past the expected completion date",
        BaselineFinishDaysLate = 0,
            BaselineCompletedTask & " finished on time",
        BLANK()
    )

Let me know if you're pulling data from other related tables, or if you're using slicers in your report—I'm happy to help fine-tune it!

Feel free to share if it worked for you!

If it solves your issue, feel free to mark it as the accepted solution so others can benefit as well. And if you found it helpful, a thumbs-up is always appreciated!

View solution in original post

PC2022
Helper III
Helper III

I had to separate the above DAX into 2 measures. One for Late Tasks, the other for completed ones. They both worked.

 

FirstLateMilestoneTask =
VAR TodayDate = TODAY()
VAR LateTasks =    FILTER( msdyn_projecttask, msdyn_projecttask[Schedule Status] = "Off Schedule" &&  msdyn_projecttask[Required Tasks.Flag] = "y" &&  msdyn_projecttask[% Complete] < 1 &&  NOT(ISBLANK(msdyn_projecttask[Baseline Finish])))
VAR FirstLateTask =    TOPN(1, LateTasks, msdyn_projecttask[Finish],   ASC)
VAR TaskName =    MAXX(FirstLateTask, msdyn_projecttask[Project Task Name])
VAR FinishDate =    MAXX(FirstLateTask, msdyn_projecttask[Baseline Finish])
VAR FinishDaysLate =     DATEDIFF(FinishDate,TODAY(),DAY)

RETURN
    SWITCH(TRUE(),
        NOT ISBLANK(TaskName),
        TaskName & " is " & FinishDaysLate & " days past the expected completion date", BLANK()        
          )
 
 
CompletedTasks1 =
VAR TodayDate = TODAY()
VAR LateTasks =
    FILTER(       msdyn_projecttask,
        msdyn_projecttask[Schedule Status] = "Off Schedule" &&
        msdyn_projecttask[Required Tasks.Flag] = "y" &&
        msdyn_projecttask[% Complete] < 1 &&
        NOT ISBLANK(msdyn_projecttask[Baseline Finish])    )
VAR FirstLateStartTask =
    TOPN(
        1,
        LateTasks,
        msdyn_projecttask[Baseline Start],
        ASC    )

VAR StartTaskName =
    MAXX(FirstLateStartTask, msdyn_projecttask[Project Task Name])
VAR StartDate =
    MAXX(FirstLateStartTask, msdyn_projecttask[Baseline Start])
VAR StartDaysLate =
    DATEDIFF(StartDate, TodayDate, DAY)
RETURN
    SWITCH(
        TRUE(),
        StartDaysLate > 0,
        StartTaskName & " is " & StartDaysLate & " days past the expected start date",
               "All required tasks are on track."    )

View solution in original post

6 REPLIES 6
PC2022
Helper III
Helper III

I had to separate the above DAX into 2 measures. One for Late Tasks, the other for completed ones. They both worked.

 

FirstLateMilestoneTask =
VAR TodayDate = TODAY()
VAR LateTasks =    FILTER( msdyn_projecttask, msdyn_projecttask[Schedule Status] = "Off Schedule" &&  msdyn_projecttask[Required Tasks.Flag] = "y" &&  msdyn_projecttask[% Complete] < 1 &&  NOT(ISBLANK(msdyn_projecttask[Baseline Finish])))
VAR FirstLateTask =    TOPN(1, LateTasks, msdyn_projecttask[Finish],   ASC)
VAR TaskName =    MAXX(FirstLateTask, msdyn_projecttask[Project Task Name])
VAR FinishDate =    MAXX(FirstLateTask, msdyn_projecttask[Baseline Finish])
VAR FinishDaysLate =     DATEDIFF(FinishDate,TODAY(),DAY)

RETURN
    SWITCH(TRUE(),
        NOT ISBLANK(TaskName),
        TaskName & " is " & FinishDaysLate & " days past the expected completion date", BLANK()        
          )
 
 
CompletedTasks1 =
VAR TodayDate = TODAY()
VAR LateTasks =
    FILTER(       msdyn_projecttask,
        msdyn_projecttask[Schedule Status] = "Off Schedule" &&
        msdyn_projecttask[Required Tasks.Flag] = "y" &&
        msdyn_projecttask[% Complete] < 1 &&
        NOT ISBLANK(msdyn_projecttask[Baseline Finish])    )
VAR FirstLateStartTask =
    TOPN(
        1,
        LateTasks,
        msdyn_projecttask[Baseline Start],
        ASC    )

VAR StartTaskName =
    MAXX(FirstLateStartTask, msdyn_projecttask[Project Task Name])
VAR StartDate =
    MAXX(FirstLateStartTask, msdyn_projecttask[Baseline Start])
VAR StartDaysLate =
    DATEDIFF(StartDate, TodayDate, DAY)
RETURN
    SWITCH(
        TRUE(),
        StartDaysLate > 0,
        StartTaskName & " is " & StartDaysLate & " days past the expected start date",
               "All required tasks are on track."    )
v-venuppu
Community Support
Community Support

Hi @PC2022 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @techies for the prompt response.

Yes - the error you're getting ("The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".) comes from this part:

LastCompletedTask & " finished on time"
LastCompletedTask is a table (result of TOPN), and you're trying to concatenate it with text, which requires a scalar value.

I have created a measure called "FirstLateMilestoneTask" in the attached pbix file which can solve the issue, please go through it.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

Not working. I am trying to figure it out and will post once i do.

Ilgar_Zarbali
Most Valuable Professional
Most Valuable Professional

This error shows up because you're trying to use a table (like what TOPN returns) where Power BI is expecting just one value—like when you're combining text or checking a condition.

You've already used MAXX(...) to get the task name and finish date from the filtered table. So instead of using LastCompletedTask (which is still a table), you should use the variable that holds the actual task name.
Just update this line:
LastCompletedTask & " finished on time"
to

BaselineCompletedTask & " finished on time"

The corrected DAX formula:

FirstLateMilestoneTask =
VAR TodayDate = TODAY()

-- Tasks that are late
VAR LateTasks =
    FILTER (
        msdyn_projecttask,
        msdyn_projecttask[Schedule Status] = "Off Schedule"
            && msdyn_projecttask[Required Tasks.Flag] = "y"
            && msdyn_projecttask[% Complete] < 1
            && NOT ISBLANK ( msdyn_projecttask[Baseline Finish] )
    )
VAR FirstLateTask =
    TOPN ( 1, LateTasks, msdyn_projecttask[Baseline Finish], ASC )
VAR TaskName =
    MAXX ( FirstLateTask, msdyn_projecttask[Project Task Name] )
VAR FinishDate =
    MAXX ( FirstLateTask, msdyn_projecttask[Finish] )
VAR FinishDaysLate =
    DATEDIFF ( FinishDate, TodayDate, DAY )

-- Tasks that are done
VAR CompletedTasks =
    FILTER (
        msdyn_projecttask,
        msdyn_projecttask[Schedule Status] <> "Off Schedule"
            && msdyn_projecttask[Required Tasks.Flag] = "y"
            && msdyn_projecttask[% Complete] = 1
            && NOT ISBLANK ( msdyn_projecttask[Baseline Finish] )
    )
VAR LastCompletedTask =
    TOPN ( 1, CompletedTasks, msdyn_projecttask[Finish], DESC )
VAR BaselineCompletedTask =
    MAXX ( LastCompletedTask, msdyn_projecttask[Project Task Name] )
VAR BaselineFinishDate =
    MAXX ( LastCompletedTask, msdyn_projecttask[Baseline Finish] )
VAR ActualFinishDate =
    MAXX ( LastCompletedTask, msdyn_projecttask[Finish] )
VAR BaselineFinishDaysLate =
    DATEDIFF ( BaselineFinishDate, ActualFinishDate, DAY )

RETURN
    SWITCH (
        TRUE (),
        NOT ISBLANK ( TaskName ),
            TaskName & " is " & FinishDaysLate & " days past the expected completion date",
        BaselineFinishDaysLate = 0,
            BaselineCompletedTask & " finished on time",
        BLANK()
    )

Let me know if you're pulling data from other related tables, or if you're using slicers in your report—I'm happy to help fine-tune it!

Feel free to share if it worked for you!

If it solves your issue, feel free to mark it as the accepted solution so others can benefit as well. And if you found it helpful, a thumbs-up is always appreciated!

Not working. I am trying to figure it out and will post once i do.

techies
Solution Sage
Solution Sage

Hi @PC2022 please share the sample of your data model or pbix file?

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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