topic Re: MAXX function throwing another column in DAX Commands and Tips

MAXX function throwing another column

Unity — Thu, 02 May 2019 15:14:11 GMT

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())))

Re: MAXX function throwing another column

Greg_Deckler — Thu, 02 May 2019 15:26:44 GMT

Weird, I'd try CALENDARAUTO()

Re: MAXX function throwing another column

Michiel — Thu, 02 May 2019 15:27:48 GMT

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)

Re: MAXX function throwing another column

Unity — Thu, 02 May 2019 15:44:52 GMT

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.

Re: MAXX function throwing another column

Greg_Deckler — Thu, 02 May 2019 15:53:03 GMT

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.

Re: MAXX function throwing another column

Michiel — Thu, 02 May 2019 16:14:48 GMT

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.