topic Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. in DAX Commands and Tips

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

proavinash — Sat, 10 May 2025 06:36:48 GMT

please suggest a solution.

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

DataNinja777 — Sat, 10 May 2025 14:04:14 GMT

The error occurs because UNION() only accepts two tables at a time, but you're passing in three ADDCOLUMNS() results directly. DAX cannot interpret more than two tables unless you nest the UNION() calls. To fix it, you need to combine the second and third ADDCOLUMNS() calls using UNION() first, then combine that result with the first one. Here's the corrected version of your DAX:

MTD/QTD/YTD = var TodayDate = TODAY() var startyear = CALCULATE(STARTOFYEAR(SaleDump[Date].[Date]), YEAR(SaleDump[Date]) = YEAR(TodayDate)) var startquarter = CALCULATE(STARTOFQUARTER(SaleDump[Date].[Date]), YEAR(SaleDump[Date]) = YEAR(TodayDate), QUARTER(SaleDump[Date]) = QUARTER(TodayDate)) var startmonth = CALCULATE(STARTOFMONTH(SaleDump[Date].[Date]), YEAR(SaleDump[Date]) = YEAR(TodayDate), MONTH(SaleDump[Date]) = MONTH(TodayDate)) var result = UNION( ADDCOLUMNS(CALENDAR(startyear, TodayDate), "Selection", "YTD"), UNION( ADDCOLUMNS(CALENDAR(startquarter, TodayDate), "Selection", "QTD"), ADDCOLUMNS(CALENDAR(startmonth, TodayDate), "Selection", "MTD") ) ) RETURN result

This corrects the structure so each UNION() has exactly two inputs and avoids the scalar value conversion error.

Best regards,

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

proavinash — Sun, 11 May 2025 09:05:50 GMT

SAME ERROR AGAIN.

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

v-saisrao-msft — Mon, 12 May 2025 08:54:50 GMT

Hi proavinash,

Thank you for reaching out to the Microsoft Fabric Forum Community.

The UNION function returns a table as a result, so rather than creating a measure, try creating a calculated table.

Please refer the below Document:
UNION function (DAX) - DAX | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

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

v-saisrao-msft — Thu, 15 May 2025 03:29:50 GMT

Hi proavinash,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

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

v-saisrao-msft — Mon, 19 May 2025 04:37:18 GMT

Hi proavinash,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

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

v-saisrao-msft — Thu, 22 May 2025 06:41:02 GMT

Hi proavinash,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

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

proavinash — Mon, 26 May 2025 15:04:20 GMT

not working same error