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

[Brianvt25]

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. 

[Brianvt25]

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]

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.

[Brianvt25]

[johnt75]

Thanks. That's the Power Query view, can you show it in the desktop view, ideally the view in the data pane rather than the underlying data.

[Brianvt25]

Like this?

[Brianvt25]

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.

[johnt75]

The only thing I can think to try is to go into DAX Query View and run

EVALUATE
ROW (
    "@min", MINX ( 'PST', 'PST'[Date Worked] ),
    "@max", MAXX ( 'PST', 'PST'[Date Worked] )
)

See what result that gives

[Brianvt25 · Accepted solution]

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!

[johnt75]

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.