Forum Discussion
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value
- 1 year ago
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"
toBaselineCompletedTask & " 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!
- 1 year ago
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)RETURNSWITCH(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)RETURNSWITCH(TRUE(),StartDaysLate > 0,StartTaskName & " is " & StartDaysLate & " days past the expected start date","All required tasks are on track." )
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.