Forum Discussion

PC2022's avatar
PC2022
Icon for Helper III rankHelper III
1 year ago
Solved

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...
  • Ilgar_Zarbali's avatar
    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"
    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's avatar
    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)

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