Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

Hello,

 

I am trying to create a date table and I am receiving the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

Below is my dax:

DateTable =
ADDCOLUMNS (
CALENDAR(MINX('PST',PST[Date Worked]),MAXX('PST',PST[Date Worked])),
"DateAsInteger", FORMAT ( [date], "YYYYMMDD" ),
 "Year", YEAR ( [date] ), "MonthNo", FORMAT ( [date], "MM" ),
"YearMonthNo", FORMAT ( [date], "YYYY/MM" ),
"YearMonth", FORMAT ( [date], "YYYY/mmm" ),
"MonthShort", FORMAT ( [date], "mmm" ),
"MonthLong", FORMAT ( [date], "mmmm" ),
"WeekNo", WEEKDAY ( [date] ),
"WeekDay", FORMAT ( [date], "dddd" ),
"WeekDayShort", FORMAT ( [date], "ddd" ),
"Quarter", "Q" & FORMAT ( [date], "Q" ),
"YearQuarter", FORMAT ( [date], "YYYY" ) & "/Q" & FORMAT ( [date], "Q" ))

 

I appreciate any support you can provide. 

  • Anonymous's avatar
    Anonymous
    1 year ago

    I figured it out...

     

    It looks like Iwas trying to save a table expression as a measure. A measure requires a scalar output, but if the measure expression results in a columnar or table-like output, you will usually get this type of error. I had to try the expression in the new table dialog, instead of a new measure dialog. Thanks for your help!

9 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Have you disabled Auto Date/Time in settings ? If not, Power BI will have automatically created a date table which is referenced by PST[Date Worked]. Either disable auto date/time ( preferably ), or else you can replace the PST[Date Worked] with PST[Date Worked].Date.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I tried disabling the auto  date/time in settings with no luck. I'ts weird because i've used this dax formula in all my pbix files in the past with no issue. 

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      can you post a screenshot showing the date worked column in the PST table ? It doesn't look like there's anything wrong with the DAX.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

         

Related content