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

[PC2022]

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

[Ilgar_Zarbali · Accepted solution]

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!

[PC2022 · Accepted solution]

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]

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.

 

[PC2022]

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

[Ilgar_Zarbali · Accepted solution]

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!

[PC2022]

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

[techies]

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