Forum Discussion

omarmendoza1216's avatar
omarmendoza1216
New Member
2 years ago
Solved

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

Hello Guys, could you please help me to solve this issue of the "multiple columns cannot be converted to scalar value" iam using the below DAx formula: 

 

MTD/QTD/YTD =
VAR todayDate = TODAY()
VAR YearStart = CALCULATE(STARTOFYEAR('ACT - BGT'[Postg Date]), YEAR('ACT - BGT'[Postg Date])=YEAR(todayDate) )
VAR QuarterStar = CALCULATE(STARTOFQUARTER('ACT - BGT'[Postg Date]), YEAR('ACT - BGT'[Postg Date]) = YEAR(todayDate), QUARTER('ACT - BGT'[Postg Date]) = QUARTER(todayDate) )
VAR MonthStart = CALCULATE(STARTOFMONTH('ACT - BGT'[Postg Date]), YEAR('Calendar'[Date])= YEAR(todayDate), MONTH('ACT - BGT'[Postg Date]) = MONTH(todayDate) )
VAR Result =
   
UNION(
    ADDCOLUMNS (
        CALENDAR(YearStart,todayDate),
        "selection", "YTD"
    ),
    ADDCOLUMNS (
        CALENDAR(QuarterStar,todayDate),
        "selection","QTD"
    ),
    ADDCOLUMNS(
        CALENDAR(MonthStart,todayDate),
        "selection", "MTD"
)
)
RETURN
Result

 

Reports
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi omarmendoza1216 ,

     

    As far as I know, UNION() function will return a table instead of a single value.

    For reference: UNION function (DAX) - DAX | Microsoft Learn

    I think you are trying to create a calculated column or a measure, so Power BI will return this error.

    I can reproduce your issue on my side. Screenshot is as below.

    We can see that measure or calculated column both will return error, however, calculated table won't.

    Please try to create a calculated table by this code. Or you need to add some steps by SUMX()/MINX()/MAXX()/... to get single value.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi omarmendoza1216 ,

     

    As far as I know, UNION() function will return a table instead of a single value.

    For reference: UNION function (DAX) - DAX | Microsoft Learn

    I think you are trying to create a calculated column or a measure, so Power BI will return this error.

    I can reproduce your issue on my side. Screenshot is as below.

    We can see that measure or calculated column both will return error, however, calculated table won't.

    Please try to create a calculated table by this code. Or you need to add some steps by SUMX()/MINX()/MAXX()/... to get single value.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

Related content