MAXX function throwing another column

[Unity]

Hello PowerBi,

I am trying to return a minimum and maximum date for each user following the sample code listed below.

=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))

(https://docs.microsoft.com/en-us/dax/calendar-function-dax)

However, I keep recieving the following error. Why is my MAXX function throwing another column?

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

Code: Test_Column = CALENDAR(MINX('User_Xref', filter('User_Xref', [LastDt] <> blank())), MAXX('User_Xref', filter('User_Xref', [LastDt] <= today())))

[Michiel · Accepted solution]

Your formula doesn't look too bad, but only as a calculated table which is what CALENDAR is meant for. 

I assume both your Sales table and your Forecast table have a [Date] column? First, the best practice is to put the table name before the column name in a formula, so

CALENDAR( MINX(Sales, Sales[Date]), MAXX(Forecast, Forecast[Date]))

In fact, this can be written in a simpler way by not using the table functions MINX and MAXX, but the basic aggregations:

CALENDAR(MIN(Sales[Date]), MAX(Forecast[Date]))

If the last line is the code you're trying to use, the problem there is that MINX expects a scalar value for each row in the table (User_Xref in your case) which is calculated through the expression you provide. This expression is:

FILTER(User_Xref, [LastDT] <> BLANK())

but FILTER returns a subset of the User_Xref table, which probably contains multiple columns. Poor MINX wouldn't know what to compare to what here (even if the table has one column, you would provide multiple rows which wouldn't work either)

[Unity]

Using this code

CALENDAR(MIN(Sales[Date]), MAX(Forecast[Date]))

is closer but now results in this error:

A table of multiple values was supplied where a single value was expected.

[Michiel]

Your code starting with 'Test_Column =' suggests that you're trying to create a calculated column, for which the formula should always return a scalar value. CALENDAR returns a table.

[Greg_Deckler]

Tough to say, something wonky with your data? Not actual date fields? I recreated this and had no issues. See attached, Sales, Forecast and Table.

[Greg_Deckler]

Weird, I'd try CALENDARAUTO()