topic Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value in DAX Commands and Tips

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

PC2022 — Fri, 23 May 2025 17:48:21 GMT

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

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

techies — Fri, 23 May 2025 19:59:34 GMT

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

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

Ilgar_Zarbali — Mon, 26 May 2025 06:04:03 GMT

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!

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

v-venuppu — Mon, 26 May 2025 06:43:45 GMT

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.

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

PC2022 — Tue, 27 May 2025 19:54:58 GMT

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

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

PC2022 — Tue, 27 May 2025 19:55:14 GMT

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

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

PC2022 — Wed, 28 May 2025 20:47:28 GMT

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 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(

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